在SQL中显示所有可能的count匹配

Question

我有一个如下所示的表

<表类> Id 帧 性别 StatusValue tbody><<tr>1山姆男SV0012艾玛女性SV0023艾娃未知4约翰男5奥利维亚女性6乔男7米娅女性

你可以这样做:

select x.gender, y.status_value, count(id)
from (select distinct gender from t) x
cross join (select distinct status_value from t) y
left join t on (t.gender = x.gender or coalesce(t.gender, x.gender) is null)
and (t.status_value = y.status_value or coalesce(t.status_value, y.status_value) is null)
group by x.gender, y.status_value
order by x.gender, y.status_value

结果:

gender   status_value  (No column name) 
-------- ------------- ---------------- 
Female   null          2                
Female   SV001         0                
Female   SV002         1                
Male     null          2                
Male     SV001         1                
Male     SV002         0                
Unknown  null          1                
Unknown  SV001         0                
Unknown  SV002         0                

由于SQL Server中缺少IS NOT DISTINCT FROM操作符，left join谓词不必要的长。显然，它将在SQL Server 2022中实现。

参见运行示例:db<>fiddle。

注意:不使用cte或pivot的限制是人为的，因为SQL Server支持它们。

您可以执行一个子查询来获得所有不同的组合，而执行另一个子查询来进行计数。最后，为了得到想要的结果，只需要LEFT JOIN 2:

SELECT Cmb.Gender, Cmb.StatusValue, COALESCE(Cnt.Count, 0) AS Count
FROM
(
SELECT 
CASE WHEN G.Gender <> '' THEN G.Gender ELSE 'Empty' END AS Gender
, CASE WHEN T.StatusValue <> '' THEN T.StatusValue ELSE 'Empty' END AS StatusValue
FROM
(SELECT DISTINCT Gender FROM Persons) as G 
CROSS JOIN (SELECT DISTINCT StatusValue FROM Persons) as T
) AS Cmb  -- cominations
LEFT JOIN 
(
SELECT 
CASE WHEN Gender <> '' THEN Gender ELSE 'Empty' END AS Gender
,CASE WHEN StatusValue <> '' THEN StatusValue ELSE 'Empty' END AS StatusValue, Count(*) as Count 
FROM Persons 
GROUP BY CASE WHEN Gender <> '' THEN Gender ELSE 'Empty' END, CASE WHEN StatusValue <> '' THEN StatusValue ELSE 'Empty' END 
) AS Cnt -- counts
ON Cmb.Gender = Cnt.Gender AND Cmb.StatusValue = Cnt.StatusValue

Working Fiddle Here，

假设,

CREATE TABLE [X]
(
[Id] INT NOT NULL PRIMARY KEY,
[FName] VARCHAR(10) NOT NULL,
[Gender] VARCHAR(10) NOT NULL,
[StatusValue] VARCHAR(10) NULL
);

这个工作

;WITH S AS
(
SELECT 'ZZZZZZZZ' [Sub]
),
G AS
(
SELECT DISTINCT [Gender] FROM [X]
),
SV AS
(
SELECT DISTINCT [StatusValue] FROM [X]
),
C AS
(
SELECT
G.[Gender],
ISNULL(SV.[StatusValue], S.[Sub]) [StatusValue]
FROM
G
CROSS JOIN
SV
CROSS JOIN
S
),
D AS
(
SELECT
X.[Gender],
ISNULL(X.[StatusValue], S.[Sub]) [StatusValue]
FROM
X
CROSS JOIN
S
),
A AS
(
SELECT
COUNT(*) [Count],
D.[Gender],
D.[StatusValue]
FROM
D
GROUP BY
D.[Gender],
D.[StatusValue]
)
SELECT
ROW_NUMBER() OVER(ORDER BY C.[Gender], C.[StatusValue]) [Id],
C.[Gender],
CASE
WHEN C.[StatusValue] = S.[Sub]
THEN 'Empty'
ELSE
C.[StatusValue]
END [StatusValue],
ISNULL(A.[Count], 0) [Count]
FROM
C
LEFT JOIN
A
ON A.[Gender] = C.[Gender] AND A.[StatusValue] = C.[StatusValue]
CROSS JOIN
S;

---

Or，无cte，

SELECT
ROW_NUMBER() OVER(ORDER BY C.[Gender], C.[StatusValue]) [Id],
C.[Gender],
CASE
WHEN C.[StatusValue] = 'ZZZZZZZZZZ'
THEN 'Empty'
ELSE
C.[StatusValue]
END [StatusValue],
COALESCE(A.[Count], 0) [Count]
FROM
(
SELECT
G.[Gender],
COALESCE(SV.[StatusValue], 'ZZZZZZZZZZ') [StatusValue]
FROM
(SELECT DISTINCT [Gender] FROM [X]) G
CROSS JOIN
(SELECT DISTINCT [StatusValue] FROM [X]) SV
) C
LEFT JOIN
(
SELECT
COUNT(*) [Count],
D.[Gender],
D.[StatusValue]
FROM
(
SELECT
X.[Gender],
COALESCE(X.[StatusValue], 'ZZZZZZZZZZ') [StatusValue]
FROM
X
) D
GROUP BY
D.[Gender],
D.[StatusValue]
) A
ON A.[Gender] = C.[Gender] AND A.[StatusValue] = C.[StatusValue];

都生产

<表类> Id 性别 StatusValue 数 tbody><<tr>1女性SV00102女性SV00213女性空24男SV00115男SV00206男空27未知SV00108未知SV00209未知空1