优化联合和公共条件的查询

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

Answer 2

虽然这与上面的输出不完全相同，但是

select eyes, count(*)
from Users
where [a long list of conditions,which are identical]
group by eyes

应该会给你你想要的信息。

Answer 3

如果你想获取每种眼睛颜色的用户数，你应该试试：

SELECT count( * ) AS c, 'eye'
FROM Users
WHERE .... all your conditions here ...
GROUP BY 'eye'