order我需要從兩個輸入表table userstable中得到如下所需的輸出
id order_date id username
1 2019-01-01 1 A
2 2019-01-01 2 B
3 2019-01-02 3 B
4 2019-01-03 4 A
5 2019-01-03 5 B
期望的輸出
order_date username orders
2019-01-01 A 1
2019-01-02 A 0
2019-01-03 A 1
我試過這個查詢，
SELECT o. order_date as order_date, u.username as username,
ISNULL (COUNT (username),0) AS orders
FROM Order O LEFT JOIN users U ON o.id=u.id
WHERE u.username = ‘A’
GROUP BY o. order_date, u.username
ORDER BY o. order_date, u.username
這給了我這個結果
order_date username orders
2019-01-01 A 1
2019-01-03 A 1
我不知道如何將這部分帶入結果“2019-01-02 A 0”
誰能幫我查詢一下，在此先感謝
uj5u.com熱心網友回復：
你可以做：
select d.order_date, 'A' as username, coalesce(cnt, 0) as orders
from (select distinct order_date as order_date from orders) d
left join (
select o.order_date, count(*) as cnt
from orders o
join users u on u.id = o.id
where u.username = 'A'
group by o.order_date
) t on t.order_date = d.order_date
order by d.order_date
結果：
order_date username orders
----------- --------- ------
2019-01-01 A 1
2019-01-02 A 0
2019-01-03 A 1
請參閱db<>fiddle的運行示例。
uj5u.com熱心網友回復：
您可以使用下面的查詢，其中includeAllUsers（使用 CROSS JOIN）允許您包含“A” ——無需將其放在 SELECT 子句中——并且StrictMatching使用兩個表 Order & Users 之間的匹配 ID 為您提供真實資料集（順便說一句，你真的必須在 Orders 或其他詞中更改訂單表的名稱，因為 ORDER 是保留字）。
select includeAllUsers.Order_Date,
coalesce(StrictMatching.username,includeAllUsers.username) User_Name,
count(distinct StrictMatching.username) Total_Orders
from (select o.order_date, u.username, u.id from orders o cross join users u) includeAllUsers
left join (select o.order_date, u.username,o.id from orders o join users u on o.id=u.id) StrictMatching
on includeAllUsers.order_date = StrictMatching.order_date and StrictMatching.username='A'
where includeAllUsers.username='A'
group by
includeAllUsers.order_date, StrictMatching.username, includeAllUsers.username;
通過結合includeAllUsers和StrictMatching并通過 StrictMatching.username='A' （在 JOIN 子句的標準中）再次通過 includeAllUsers.username='A' （在 WHERE 子句中）進行過濾，您可以獲得正確的結果。
這是一個驗證鏈接
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/449525.html