mysql - 跨越多个UNION ALL语句的位置

我有一个带有多个UNION ALL关键字的SQL select语句，这些关键字连接了否则为独立的SQL语句。对于我来说，查询太复杂了，无法使用UNION ALL关键字。

但是，我想将所有子查询限制在一个特定的日期范围内，并且我不想在每个子查询的末尾编写完全相同的where子句。有没有一种方法可以为我所有人编写一个WHERE子句？

我尝试了以下代码，但由于COUNT(*)短语阻止为每个记录过滤inputDate列，因此它不起作用。从性能的角度来看，如果有办法的话，最好先按日期列进行过滤。

SELECT * FROM (
    SELECT count(*), inputDate 'sel1' as name FROM tblName WHERE name IN ('n1','n2')
    UNION ALL
    SELECT count(*), inputDate, 'sel2' as name FROM tblName WHERE name IN ('n3','n4')
    UNION ALL
    SELECT count(*), inputDate, 'sel3' as name FROM tblName WHERE name IN ('n5','n6')
) names WHERE inputDate >= DATE_SUB(NOW(), INTERVAL 1 MONTH)

最佳答案

您将需要在外部查询中执行count(*)：

SELECT name, count(*), inputdate
FROM (SELECT inputDate, 'sel1' as name FROM tblName WHERE name IN ('n1','n2')
      UNION ALL
      SELECT inputDate, 'sel2' as name FROM tblName WHERE name IN ('n3','n4')
      UNION ALL
      SELECT inputDate, 'sel3' as name FROM tblName WHERE name IN ('n5','n6')
     ) names
WHERE inputDate >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
GROUP BY name;

编辑：

实际上，如果这些都来自同一表，则不需要union all：

select (case when name in ('n1', 'n2') then 'sel1'
             when name in ('n3', 'n4') then 'sel2'
             when name in ('n5', 'n6') then 'sel3'
        end) as grp, count(*)
from tblName
where inputDate >= DATE_SUB(NOW(), INTERVAL 1 MONTH) and
      name in ('n1', 'n2', 'n3', 'n4',' n5', 'n6')
group by grp;

关于mysql - 跨越多个UNION ALL语句的位置，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/31674716/