根据状态，使用以下语句可以为我提供4列（位置，描述，父项，计数）：
SELECT
KINCIDENT.location, LOCATIONS.description,
LOCHIERARCHY.parent, count(KINCIDENT.incnum)
FROM
KINDICENT, LOCATIONS, LOCHIERARCHY
WHERE
KINCIDENT.location = LOCATIONS.location
and LOCATIONS.location = LOCHIERARCHY.location
AND KINCIDENT.status = 'ECCAPR'
GROUP BY
KINCIDENT.location, LOCATIONS.description, LOCHIERARCHY.parent
ORDER BY
parent;
但是，我想在第5列中给我计数，但是当KINCIDENT.status ='FSAPR'时。如何指定每个计数列采用的状态？
您需要条件聚合。您还应该学习使用显式joins：
SELECT k.location, l.description, h.parent,
SUM(CASE WHEN k.status = 'ECCAPR' THEN 1 ELSE 0 END) as cnt_ECCAPR,
SUM(CASE WHEN k.status = 'FSAPR' THEN 1 ELSE 0 END) as cnt_FSAPR
FROM KINDICENT k JOIN
LOCATIONS l
ON k.location = l.location JOIN
LOCHIERARCHY h
ON l.location = h.location
GROUP BY k.location, l.description, h.parent
ORDER BY parent;
我还介绍了表别名，这些别名通常使查询更易于编写和阅读。
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句