sql 组合 2 个不同顺序的查询

Question

我有一个查询，我在其中计算数据库中最频繁的响应并按最高数量对它们进行排名，因此使用分组依据和排序依据。

下面展示了如何为一个人做这件事:

select health, count(health) as count 
from [Health].[Questionaire] 
group by Health 
order by count(Health) desc

输出如下:

Health       Count
-----------  -----
Very Good    6
Good         5
Poor         4

我想对同一个表上的另一列执行另一个类似于以下的查询，因此使用一个 sql 语句的两个查询如下所示:

Health       Count    Diet       Count
-----------  -----   -----       -----
Very Good    6        Very Good   6
Good         5        Good        4
Poor         4        Poor        3

更新!!

你好，这是目前表格的样子

ID           Diet              Health      
-----------  -----             -------       
101          Very Good         Very Good   
102          Poor              Good        
103          Poor              Poor        

我想对同一个表上的另一列执行另一个类似于以下的查询，因此使用一个 sql 语句的两个查询如下所示:

Health       Count    Diet       Count
-----------  -----   -----       -----
Very Good    2        Very Good   1
Poor         1        Good        1
Good         0        Poor        1

谁能帮我解决这个问题？如果需要，可以提供进一步的说明!

Answer 1

这里有两种不同的方法，注意我删除了多余的列:

测试数据:

DECLARE @t table(Health varchar(20), Diet varchar(20))
INSERT @t values
('Very good', 'Very good'),
('Poor', 'Good'),
('Poor', 'Poor')

查询 1:

;WITH CTE1 as
(
  SELECT Health, count(*) CountHealth
  FROM @t --[Health].[Questionaire] 
  GROUP BY health
), CTE2 as
(
  SELECT Diet, count(*) CountDiet
  FROM @t --[Health].[Questionaire] 
  GROUP BY Diet
)
SELECT 
  coalesce(Health, Diet) Grade, 
  coalesce(CountHealth, 0) CountHealth, 
  coalesce(CountDiet, 0) CountDiet
FROM CTE1
FULL JOIN
CTE2
ON CTE1.Health = CTE2.Diet
ORDER BY CountHealth DESC

结果 1:

Grade     CountHealth  CountDiet
Poor      2            1
Very good 1            1
Good      0            1

像这样混合结果确实不是一个好的做法，所以这里有一个不同的解决方案

查询 2:

SELECT Health, count(*) Count, 'Health' Grade
FROM @t --[Health].[Questionaire] 
GROUP BY health
UNION ALL
SELECT Diet, count(*) CountDiet, 'Diet'
FROM @t --[Health].[Questionaire] 
GROUP BY Diet
ORDER BY Grade, Count DESC

结果 2:

Health     Count Grade
Good       1     Diet
Poor       1     Diet
Very good  1     Diet
Poor       2     Health
Very good  1     Health