在SQL中顯示所有可能與計數匹配的項

我有一個表如下所示

我想顯示以下輸出

在這里，我想顯示所有可能的組合以及匹配計數。 注意：不能使用公用表運算式和 PIVOT，因為該工具不支持少數 SQL 關鍵字。我已經嘗試了以下但它給出了錯誤的計數

SELECT Gender,StatusValue,Count(*) as Count FROM Persons 
WHERE Gender <> '' AND StatusValue <> '' 
GROUP BY Gender,StatusValue 
UNION
SELECT 
CASE WHEN G.Gender <> '' THEN G.Gender ELSE 'Empty' END,
CASE WHEN T.StatusValue <> '' THEN T.StatusValue ELSE 'Empty' END, 0 as COUNT from
(SELECT DISTINCT Gender FROM Persons) as G, (SELECT DISTINCT StatusValue FROM Persons) as T

uj5u.com熱心網友回復：

你可以做：

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 中left join缺少運算子，謂詞過長。IS NOT DISTINCT FROM不過，顯然它正在 SQL Server 2022 中實作。

請參閱db<>fiddle中的運行示例。

注意：不使用 CTE 或資料透視表的限制有點人為，因為 SQL Server 確實支持它們。

uj5u.com熱心網友回復：

您可以執行一個子查詢來獲取所有不同的組合，然后執行另一個子查詢來進行計數。最后只需 LEFT JOIN 兩個以獲得所需的結果：

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

uj5u.com熱心網友回復：

在這里作業小提琴，

假設，

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;

或者，沒有 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];

兩者都產生

標籤：数据库sql服务器

上一篇：詹金斯運行很少的作業并得到他們的結果

下一篇：表上的SQLServer磁區可以更新舊磁區中的資料