mysql - 优化联合和公共(public)条件的查询

Question

我有近 18 个选择联合，每个选择包含近 10 个相同的条件，只有一个条件不同。请查看下面的 sql 结构。

SELECT count(*) AS count, 'blue' as title 
FROM Users
WHERE [a long list of conditions,which are identical] AND eyes='blue'

UNION

SELECT count(*) AS count, 'hazel' as title 
FROM Users
WHERE [a long list of conditions,which are identical] AND eyes='hazel'

UNION

SELECT count(*) AS count, 'Black' as title 
FROM Users
WHERE [a long list of conditions,which are identical] AND eyes='black'

等等。

检索此类数据的更好方法是什么。有更好的主意吗？

编辑:

很抱歉没有早点提到这一点，这些条件不是基于单一领域的“眼睛”，它可能是不同的，例如头发、高度等。因此不能按照建议使用分组依据。

Answer 1

你想要条件总和:

select count(*),
       sum(case when eyes = 'blue' then 1 else 0 end) as blue,
       sum(case when eyes = 'hazel' then 1 else - end) as hazel,
       . . . 
from users
where <long list of conditions>

这会将所有内容放在一行中。要将所有内容放在不同的行中，您可能需要:

select eyes, count(*)
from users
where <long list of conditions>
group by eyes

这将为您提供每种眼睛颜色的单独一行。

根据您的评论，最好的方法可能是在单行上进行汇总，然后对值进行逆透视。不幸的是，MySQL 没有 unpivot，所以下面的代码虽然丑陋但应该是有效的:

select titles.title,
       max(case when titles.title= 'blue' then blue
                when titles.title = 'hazel' then hazel
                . . .
           end) as cnt
from (select count(*) as cnt,
             sum(case when eyes = 'blue' then 1 else 0 end) as blue,
             sum(case when eyes = 'hazel' then 1 else - end) as hazel,
             . . . 
      from users
      where <long list of conditionss
     ) cross join
     (select 'blue' as title union all
      select 'hazel' union all
      . . .
     ) titles
group by titles.title