java - 如何根据在 mysql 中该行上使用 sum(1) 接收到的数据创建一个新列

Question

我有一个查询，我正在获取该行的计数，但我试图根据两个条件接收计数，但我得到的输出在两个条件下都在一行中。我想获得另一个新列对于第二个条件类似于我的第一个条件。我的查询如下

SELECT 
    SUM(1) AS Usercount, feild
FROM
    (SELECT 
        *
    FROM
        (SELECT 
        id,
            GROUP_CONCAT(DISTINCT code) AS feild
    FROM
        table
    WHERE
        type = 'Usercount' and code = 001
            AND locId IN (SELECT 
                id
            FROM
                location
            WHERE
                id IN (1) AND status = 1)
    GROUP BY id) AS deptdata
    WHERE
        feild NOT LIKE '%,%') AS Data
GROUP BY feild 
UNION SELECT 
    SUM(1) AS EmployeeCount, feild
FROM
    (SELECT 
        *
    FROM
        (SELECT 
        id,
            GROUP_CONCAT(DISTINCT code) AS feild
    FROM
        table
    WHERE
        type = 'EmployeeCount' and code = 001
            AND locId IN (SELECT 
                id
            FROM
                location
            WHERE
                id IN (1) AND status = 1)
    GROUP BY id) AS deptdata
    WHERE
        feild NOT LIKE '%,%') AS Data
GROUP BY feild 

执行此操作后，我得到两列，第一列用于 UserCount，第二列用于代码。即

Usercount Code
3         001

但我希望输出为

UserCount EmployeeCount  Code
3              2          001

Answer 1

您可以尝试以下方式 - 使用条件聚合

select feild,
       count(case when type='Usecount' then 1 end) as UserCount,
       count(case when type='EmployeeCount' then 1 end) as EmployeeCount
from
(
SELECT id,type,GROUP_CONCAT(DISTINCT code) AS feild
FROM table
    WHERE code = 001
            AND locId IN (SELECT 
                id
            FROM
                location
            WHERE
                id IN (1) AND status = 1)
GROUP BY id,type
)A where feild NOT LIKE '%,%' group by feild