我有 2 個表Table1，Table2我想在其中獲取重復行的總數：
預期輸出：
查詢測驗：
SELECT
t1.name,
t1.duplicates,
ISNULL(t2.active, 0) AS active,
ISNULL(t3.inactive, 0) AS inactive
FROM
(SELECT
t1.name, COUNT(*) AS duplicates
FROM
(SELECT c.name
FROM table1 c
INNER JOIN table2 as cd on cd.id = c.id)) t1
GROUP BY
name
HAVING
COUNT(*) > 1) t1
LEFT JOIN
(SELECT c.name, COUNT(*) AS active
FROM table1 c
WHERE name IN (SELECT c.name FROM table1 c)
GROUP BY c.name AND status = 'Active'
GROUP BY name) t2 ON t1.name = t2.name
LEFT JOIN
(SELECT c.name, COUNT(*) AS inactive
FROM table1 c
WHERE name IN (SELECT c.name FROM table1 c GROUP BY c.name)
AND status = 'InActive'
GROUP BY name) t3 ON t1.name = t3.name
ORDER BY
name
它仍然回傳重復的行，我無法獲取 id 和 creator 列
uj5u.com熱心網友回復：
如果您愿意原諒subqueryand left join，我建議您使用以下查詢：
select b.*,
count(creator) as creator_count
from
(select a.mainid,
a.name,
sum(case when a.status = "active"
then 1 else 0 end) as active_count,
sum(case when a.status = "inactive"
then 1 else 0 end) as inactive_count,
count(a.name) as duplicate_count
from table1 as a
group by a.name
having count(a.name) > 1) as b
left join table2 as c
on b.mainid = c.mainid
group by c.mainid
having count(c.creator) > 1
而不是強迫我們直接加入兩個表。首先，匯出我們可以從中獲取的資訊，Table1然后將其與 連接起來Table2以獲取creator count.
SQL 小提琴：http ://sqlfiddle.com/#!9/4daa19e/28
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/448478.html
下一篇：不顯示空的選擇結果