下面是我創建的表，我還在其中插入了值：
CREATE TABLE user_posts
(username varchar(25),
num_of_posts int)
GO
INSERT INTO user_posts VALUES ('Mark' , 23),
('Donald' , 23),
('George' , 7),
('Edward' , 2),
('Richard' , 15),
('Michael' , 1),
('John' , 1),
('Paul' , 12),
('Daniel' , 9)
GO
現在我正在嘗試解決以下問題：
撰寫一個查詢，顯示發布最多和最少帖子數的團隊成員。
這就是我所做的：
SELECT A.[Team members with highest number of reports], B.[Team members with lowest number of reports]
FROM
(SELECT username AS 'Team members with highest number of reports'
FROM user_posts
WHERE num_of_posts = (SELECT MAX(num_of_posts) FROM user_posts)
) A,
(SELECT username AS 'Team members with lowest number of reports'
FROM user_posts
WHERE num_of_posts = (SELECT MIN(num_of_posts) FROM user_posts)
) B
我收到以下結果：
我幾乎得到了我想要的結果，但唯一的問題是它顯示了重復的值。我什至嘗試為用戶名和 num_of_posts 添加 DISTINCT，但它仍然顯示重復值。我仍然無法弄清楚為什么我的查詢邏輯回傳重復值。
如何解決此問題？
uj5u.com熱心網友回復：
您正在兩個串列之間進行笛卡爾連接，但它們之間沒有相關性，因此您無法真正加入它們 - 如果您有 3 個用戶共享最大帖子和 1 個共享最小帖子怎么辦 - 3 行中的哪一個匹配1行？
您可能需要類似以下內容的內容，它可以有效地將兩個單獨的串列放在一個“聯合”中：
select username,
case when num_of_posts=minp then
Concat('Min number ', minp) else
Concat('Max number ',maxp)
end Postcount
from (
select *,
Max(num_of_posts) over() maxp,
Min(num_of_posts) over() minp
from user_posts
)p
where num_of_posts in (minp,maxp);
您還可以稍微調整它以僅回傳最小和最大計數以及每個用戶的一行：
select String_Agg(username, ',') UserName,
Concat(Iif(num_of_posts=Max(maxp),'Max posts - ','Min posts - '),num_of_posts) PostCount
from (
select *,
Max(num_of_posts) over() maxp,
Min(num_of_posts) over() minp
from user_posts
)p
where num_of_posts in (minp,maxp)
group by num_of_posts;
這使：
另一種選擇是使用row_number在每個單獨的查詢結果之間進行完全連接，以人為地創建連接條件：
with maxp as (
SELECT username AS [Team members with highest number of reports],
Row_Number() over(order by (select null)) rn
FROM user_posts
WHERE num_of_posts = (SELECT MAX(num_of_posts) FROM user_posts)
), minp as (
SELECT username AS [Team members with lowest number of reports],
Row_Number() over(order by (select null)) rn
FROM user_posts
WHERE num_of_posts = (SELECT MIN(num_of_posts) FROM user_posts)
)
select [Team members with highest number of reports],
[Team members with lowest number of reports]
from maxp full join minp on maxp.rn = minp.rn;
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/448980.html
上一篇：使用組和名稱排序查詢