SQL MS访问计数超过了三列

Question

我对SQL语句的类型有问题，我拥有的不仅仅是列、行，我需要使用where和count，我会得到这样的结果，但我不知道如何写SQL语句。

name|total| ER | OPD | IPD
WBC | 5   |  2 |  3  |  0
PLT | 3   |  1 |  0  |  2
MCV | 7   |  4 |  2  |  1



    SELECT count(WBC) as total,
    (select COUNT(WBC) FROM Table2 WHERE Section='ER')  as ER,
    (SELECT COUNT(WBC) FROM Table2 WHERE Section='OPD') as OPD,
   (SELECT COUNT(WBC) FROM Table2 WHERE Section = 'IPD') as IPD 
   FROM Table2 ;

第二排

    SELECT count(PLT) as total,
    (select COUNT(PLT) FROM Table2 WHERE Section='ER')  as ER,
    (SELECT COUNT(PLT) FROM Table2 WHERE Section='OPD') as OPD,
   (SELECT COUNT(PLT) FROM Table2 WHERE Section = 'IPD') as IPD 
   FROM Table2 ;

第三行

    SELECT count(MCV) as total,
    (select COUNT(MCV) FROM Table2 WHERE Section='ER')  as ER,
    (SELECT COUNT(MCV) FROM Table2 WHERE Section='OPD') as OPD,
   (SELECT COUNT(MCV) FROM Table2 WHERE Section = 'IPD') as IPD 
   FROM Table2 ; 

Answer 1

试试这个：

SELECT name, total, ER, OPD, IPD 
FROM(
    SELECT 1 as order, 'WBC' as name, count(WBC) as total,
    (select COUNT(WBC) FROM Table2 WHERE Section='ER')  as ER,
    (SELECT COUNT(WBC) FROM Table2 WHERE Section='OPD') as OPD,
    (SELECT COUNT(WBC) FROM Table2 WHERE Section = 'IPD') as IPD 
    FROM Table2 

    UNION ALL

    SELECT 2, 'PLT', count(PLT) as total,
    (select COUNT(PLT) FROM Table2 WHERE Section='ER')  as ER,
    (SELECT COUNT(PLT) FROM Table2 WHERE Section='OPD') as OPD,
    (SELECT COUNT(PLT) FROM Table2 WHERE Section = 'IPD') as IPD 
    FROM Table2 

    UNION ALL

    SELECT 3, 'MCV', count(MCV) as total,
    (select COUNT(MCV) FROM Table2 WHERE Section='ER')  as ER,
    (SELECT COUNT(MCV) FROM Table2 WHERE Section='OPD') as OPD,
    (SELECT COUNT(MCV) FROM Table2 WHERE Section = 'IPD') as IPD 
    FROM Table2
) u
ORDER BY u.order