mysql - 左连接计数等于 3 的 SQL 更新

Question

你好我正在尝试构建一个 sql 查询，我在其中更新一个表的值，其中另一个表的左连接等于 3。

车辆有 3 张照片的示例。

到目前为止，我编写的查询似乎在使用 group by 时失败了。

UPDATE domain.vehicle_listing AS t0 LEFT OUTER JOIN photo AS t1 ON t0.id = t1.vehicle_listing_id
SET t0.active = 0
WHERE `create_date` >= '2015-5-2' 
AND user_profile_id is not null 
AND t0.active = 1
GROUP BY t1.vehicle_listing_id 
HAVING COUNT(DISTINCT t1.id) = 3
ORDER BY create_date desc;

Vehicle_Listing
id

Photo
id, vehicle_listing_id, photo_url

OneToMany relationship with photo. 

Answer 1

你也可以使用exists

UPDATE vehicle_listing AS t0 
SET t0.active = 0
WHERE t0.`create_date` >= '2015-05-02' 
AND t0.user_profile_id is not null 
AND t0.active = 1
AND EXISTS (
  SELECT 1 
  FROM photo
  WHERE vehicle_listing_id=t0.id
  GROUP BY vehicle_listing_id 
  HAVING COUNT(DISTINCT id) = 3
)

vehicle_listing 的示例数据

INSERT INTO vehicle_listing
    (`id`, `title`, `create_date`, `active`,user_profile_id)
VALUES
    (1, 'test', '2015-05-02 00:00:00', 1,1),
    (2, 'test1', '2015-05-02 00:00:00', 1,1)
;

照片的示例数据

INSERT INTO photo
    (`id`, `vehicle_listing_id`, `photo_url`)
VALUES
    (1, 1, 'image.jpg'),
    (2, 1, 'image.jpg'),
    (3, 1, 'image.jpg'),
    (4, 2, 'image.jpg'),
    (5, 2, 'image.jpg')
;

示例输出

id  title   create_date             active     user_profile_id
1   test    May, 02 2015 00:00:00   0          1
2   test1   May, 02 2015 00:00:00   1          1

DEMO