我有如下查詢
SELECT dates,
COUNT(link_data_id) as TotalClicks,
sum(case when link_redirect_status = 1 then 1 else 0 end) AS GoodClicks,
sum(case when link_redirect_status != 1 then 1 else 0 end) AS BadClicks
FROM tbl_calendar
LEFT JOIN tbl_links_data ON dates = CAST(link_data_time AS DATE)
WHERE (`dates` BETWEEN '2021-11-28' AND DATE_ADD('2021-11-28', INTERVAL 7 DAY))
GROUP BY dates
它給了我如下預期的輸出
但我想再添加一個名為 link_order_id ='abcde' 的條件，所以我正在嘗試如下
SELECT dates,
COUNT(link_data_id) as TotalClicks,
sum(case when link_redirect_status = 1 then 1 else 0 end) AS GoodClicks,
sum(case when link_redirect_status != 1 then 1 else 0 end) AS BadClicks
FROM tbl_calendar
LEFT JOIN tbl_links_data ON dates = CAST(link_data_time AS DATE)
WHERE link_order_id = 'abcde'
AND (`dates` BETWEEN '2021-11-28' AND DATE_ADD('2021-11-28', INTERVAL 7 DAY))
GROUP BY dates
但它只給了我如下兩行
為什么我只得到兩行而不是像第一張圖片那樣的 8 行？
uj5u.com熱心網友回復：
將WHERE子句中的條件移動到連接的ON子句中：
SELECT
c.dates,
COUNT(d.link_data_id) AS TotalClicks,
SUM(CASE WHEN d.link_redirect_status = 1 THEN 1 ELSE 0 END) AS GoodClicks,
SUM(CASE WHEN d.link_redirect_status != 1 THEN 1 ELSE 0 END) AS BadClicks
FROM tbl_calendar c
LEFT JOIN tbl_links_data d
ON c.dates = CAST(d.link_data_time AS DATE) AND
d.link_order_id = 'abcde'
WHERE (c.dates BETWEEN '2021-11-28' AND DATE_ADD('2021-11-28', INTERVAL 7 DAY))
GROUP BY c.dates;
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/377399.html
標籤：mysql