sql - 在SQL UNION查询中添加列内容

到目前为止我有这个问题

SELECT
    COUNT(f.code_id) as item_count,
    f.code_desc
FROM
    foo f
    INNER JOIN foohistory fh ON f.history_id = fh.history_id
WHERE
    MONTH(fh.create_dt) = 6
    AND YEAR(fh.create_dr) = 2010
GROUP BY
    f.code_desc

    UNION ALL

SELECT
    COUNT(b.code_id) as item_count,
    b.code_desc
FROM
    bar b
    INNER JOIN barhistory bh ON b.history_id = bh.history_id
WHERE
    MONTH(bh.create_dt) = 6
    AND YEAR(bh.create_dr) = 2010
GROUP BY
    b.code_desc

我的目标是将这两个查询合并为每个代码描述的“项目计数”列的总和。这可能吗？

最佳答案

如果没有关于代码的更多信息，例如如果两个表之间的代码可能是互斥的，请使用：

SELECT x.code_desc,
       SUM(x.item_count)
 FROM (SELECT f.code_desc,
              COUNT(f.code_id) as item_count
         FROM foo f
         JOIN foohistory fh ON f.history_id = fh.history_id
        WHERE MONTH(fh.create_dt) = 6
          AND YEAR(fh.create_dr) = 2010
     GROUP BY f.code_desc
       UNION ALL
       SELECT b.code_desc,
              COUNT(b.code_id) as item_count
         FROM bar b
         JOIN barhistory bh ON b.history_id = bh.history_id
        WHERE MONTH(bh.create_dt) = 6
          AND YEAR(bh.create_dr) = 2010
     GROUP BY b.code_desc) x
GROUP BY x.code_desc

关于sql - 在SQL UNION查询中添加列内容，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/4046278/