mysql - 如何从另外两个表动态计算表中的行数？

Question

我想计算 CHAT 表中的行，它们的 REASON_ID 列存在于 REASON_GROUP 表中:

第一个表聊天:

ID    REASON_ID    DEPARTMENT_ID    
 1      46           1
 2      46           1
 3      50           1
 4      50           2
 5      100          1 
 6      100          2

第二张表原因:

ID    REASON_NAME     REASON_GROUP_ID
46    Reason1         1
50    Reason2         1
100   Reason3         2
101   Reason4         2
105   Reqson5         3

第三个表Reason_Group:

ID    NAME
1     Group1
2     Group2
3     Group3

我想像这样显示结果:

Reason1   Reason2   Reason3   Reason4   Reason5
2         2         2         0         0      

Answer 1

您可以为此使用 PIVOT 和动态 sql:

DECLARE @DynamicPivotQuery AS nvarchar(max)
DECLARE @ColumnName AS nvarchar(max)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName = ISNULL(@ColumnName + ',', '') + QUOTENAME(REASON_NAME)
FROM (SELECT DISTINCT REASON_NAME FROM Reason) AS Reasons

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery =
N'SELECT ' + @ColumnName + ' FROM 
(
    SELECT r.REASON_NAME, SUM(CASE WHEN c.ID is null OR rg.ID IS NULL THEN 0 ELSE 1 END) AS cnt 
    FROM Reason r
    LEFT JOIN Chat c ON (c.REASON_ID = r.ID)
    LEFT JOIN Reason_Group rg ON (r.REASON_GROUP_ID = rg.ID)
    GROUP BY REASON_NAME
) inn
    PIVOT(AVG(cnt) 
          FOR REASON_NAME IN (' + @ColumnName + ')) AS PVTTable'

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

结果:

Reason1     Reason2     Reason3     Reason4     Reqson5   
2           2           2           0           0

其实不用PIVOT也可以，但是你还是得用动态sql，我不知道没有动态sql怎么解决你的任务:(

所以这是一个没有 PIVOTING 的解决方案:

DECLARE @DynamicQuery AS nvarchar(max)
DECLARE @AgrColumns AS nvarchar(max)

--Get distinct values of the Column 
SELECT @AgrColumns = ISNULL(@AgrColumns + ',', '') + 'SUM(CASE WHEN REASON_NAME = ''' + REASON_NAME + ''' THEN cnt ELSE 0 END) AS ' + QUOTENAME(REASON_NAME)
FROM (SELECT DISTINCT REASON_NAME FROM Reason) AS Reasons

--Prepare the query using the dynamic 
SET @DynamicQuery =
N'SELECT ' + @AgrColumns + ' FROM 
(
    SELECT r.REASON_NAME, SUM(CASE WHEN c.ID is null OR rg.ID IS NULL THEN 0 ELSE 1 END) AS cnt 
    FROM Reason r
    LEFT JOIN Chat c ON (c.REASON_ID = r.ID)
    LEFT JOIN Reason_Group rg ON (r.REASON_GROUP_ID = rg.ID)
    GROUP BY REASON_NAME
) inn'

--Execute the Dynamic Query
EXEC sp_executesql @DynamicQuery