分组查询的简单SQL操作

以下 可以 从workertype到active和archive的连接要比外部连接好,因为后者显然非常大:

select type, sum(active_count) active_total, sum(archive_count) archive_total
from (select type, 0 active_count, 0 archive_count from workertype UNION ALL
      select type, 1 active_count, 0 archive_count from active UNION ALL
      select type, 0 active_count, 1 archive_count from archive) sq
group by type

使用:

   SELECT wt.type, 
          COUNT(a.pkey) AS activeCount,
          COUNT(ar.pkey) as archivedCount,
          COUNT(a.pkey) + COUNT(ar.pkey) AS total
     FROM WORKERTYPE wt
LEFT JOIN ACTIVE a ON a.type = wt.type
LEFT JOIN ARTCHIVE ar ON ar.type = wt.type
 GROUP BY wt.type
 ORDER BY total

不需要子查询-这可以在单个查询中完成。

标准SQL中的ORDER BY可以引用列别名,如本例所示。

SELECT foo.*, COUNT(archive.pkey) as archivedCount,
COUNT(archive.pkey) + foo.activeCount AS total FROM (
    SELECT type, count(active.pkey) AS activeCount
    FROM workertype
    LEFT JOIN active USING(type)
    GROUP BY type
) AS foo
LEFT JOIN archive USING(type)
GROUP BY type
ORDER BY total;