代码之家 › 专栏 › 技术社区 › Charles

分组查询的简单SQL操作

aggregate-functions mysql sql

Charles · 技术社区 · 15 年前

我有这样的疑问:

SELECT foo.*, COUNT(archive.pkey) as archivedCount 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;

我想添加一个列 total 它将是archivedCount+activeCount并按此排序。(很简单,对吧?)但我做不到

SELECT foo.*, COUNT(archive.pkey) as archivedCount,
archivedCount + 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;

因为archivedCount使用聚合函数。

可能值得注意的是,active和archive都非常大,因此任何可以避免服务器额外工作的做法都是值得赞赏的。

3 回复 | 直到 15 年前

user359040user359040 15 年前

以下可以从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

OMG Ponies 15 年前

使用:

   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可以引用列别名,如本例所示。

Eric Petroelje 15 年前

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;

推荐文章

Bard.Mus · 迁移后的数据库字符集环境

10 月前

tinkerr · 如何在2025年在MacOS上安装MySQL 5.7

10 月前