mysql - mysql查询中如何从多条记录中获取记录总数

Question

我已经运行了下面的查询

SELECT 
    SUBSTRING_INDEX(prim_key ,":" , 1) prim_key, 
    SUBSTRING_INDEX(prim_key ,":" , -1) urid, 
    CASE WHEN basicname = 'Q914'  THEN 'SHC'
         WHEN basicname = 'QP913'  THEN 'PHC'
         WHEN basicname = 'QC1214'  THEN 'CHC'
         WHEN basicname = 'QD1014'  THEN 'DH/SDH'
         ELSE 'UNKOWN' 
    END AS Status 
FROM facility_data 
WHERE 1 = 1
    AND basicname in ( 'Q914'  , 'QP913' , 'QC1214' , 'QD1014' ) 
    and prim_key like  '3_10_%' 
ORDER BY  prim_key ASC

我需要稍微修改一下，我希望它显示 SHC 、 DH 、 PHC 和 CHC 的总数。

所以请告诉我应该怎么做才能显示 shc、dh、phc 和 chc 的总数。

Answer 1

SELECT status
     , Count(*)
FROM   (
        SELECT CASE basicname
                 WHEN 'Q914'   THEN 'SHC'
                 WHEN 'QP913'  THEN 'PHC'
                 WHEN 'QC1214' THEN 'CHC'
                 WHEN 'QD1014' THEN 'DH/SDH'
               END AS status
        FROM   facility_data 
        WHERE  basicname in ('Q914', 'QP913', 'QC1214', 'QD1014' )
        AND    prim_key like  '3_10_%'
       ) As x
GROUP
    BY status

如果您希望按 prim_key 和 urid 进行分组，则

SELECT prim_key
     , urid
     , status
     , Count(*)
FROM   (
        SELECT SUBSTRING_INDEX(prim_key, ':', +1) As prim_key
             , SUBSTRING_INDEX(prim_key, ':', -1) As urid
             , CASE basicname
                 WHEN 'Q914'   THEN 'SHC'
                 WHEN 'QP913'  THEN 'PHC'
                 WHEN 'QC1214' THEN 'CHC'
                 WHEN 'QD1014' THEN 'DH/SDH'
               END AS status
        FROM   facility_data 
        WHERE  basicname in ('Q914', 'QP913', 'QC1214', 'QD1014' )
        AND    prim_key like  '3_10_%'
       ) As x
GROUP
    BY prim_key
     , urid
     , status