使用联合的子查询的问题

Question

我使用联合，这样我可以"合并";来自3个独立查询的数据。我的表有许多列，但我只能使用标题和ID。

我的数据:

ID Title 
1 'trainings'  
2 'trainings'
3 'workshops'
4 'workshops'
5 'workshops'
6 'qwerty'
7 'qwerty2'
8 'qwerty3'

我想做的是只显示training, workshop的标题，并将其他三个组合为"其他"。然后逐一数一数。例子:

Count Title
2     trainings
3     workshops
3     others

我已经试过了:

select  Title, count(*) as Count From Events where Title like '%Training%' 
group by Title 
union 
select Title, count(*) as Count From Events where Title like '%Workshop%' 
group by Title
union 
select 'Others' as Title,count(*) as Count from Events e where e.Title not like '%workshop%' and e.Title not like '%training%'
Group by Title

但我没有得到想要的结果，事实上，我把1作为每个"Other "列:

Count Title
1     Others
2     Trainings
3     Workshops

但是如果我只运行这段代码:

select 'Others' as Title,count(*) as Count from Events e where e.Title not like '%workshop%' and e.Title not like '%training%'
Group by Title

它将工作一切正常，"错误"只有当我把其他两个结合起来的时候。

你能帮我一下吗?

Answer 1

为什么不使用聚合的单个查询呢?

SELECT COUNT(*) AS [Count],
CASE WHEN Title LIKE '%Training%' THEN 'Trainings' 
WHEN Title LIKE '%Workshop%' THEN 'Workshops'
ELSE 'Other'
END AS Title
FROM dbo.Events
GROUP BY CASE WHEN Title LIKE '%Training%' THEN 'Trainings' 
WHEN Title LIKE '%Workshop%' THEN 'Workshops'
ELSE 'Other'
END;

如果不想重复CASE表达式，可以使用CTE:

WITH Titles AS(
SELECT CASE WHEN Title LIKE '%Training%' THEN 'Trainings' 
WHEN Title LIKE '%Workshop%' THEN 'Workshops'
ELSE 'Other'
END AS Title
FROM dbo.Events)
SELECT COUNT(*) AS [Count],
Title
FROM Titles
GROUP BY Title;

Answer 2

可以使用case表达式进行聚合:

select (case when title in ('Trainings', 'Workshops') then title
else 'Others'
end) as title,
count(*)
from events e
group by (case when title in ('Trainings', 'Workshops') then title
else 'Others'
end);

如果您不想重复case表达式，您可以使用与apply的横向连接:

select v.title, count(*)
from events e cross join
(values (case when title in ('Trainings', 'Workshops')
then title else 'Others'
end)
) v(title)
group by v.title;

编辑:

如果你需要LIKE，你只需要扩展这个(没有例子在问题中，这是必要的):

select v.title, count(*)
from events e cross join
(values (case when title like '%trainings%' then 'Training'
when title like '%workshops%' then 'Workshop'
else 'Others'
end)
) v(title)
group by v.title;

Answer 3

SELECT COUNT(ID) AS COUNT, Title从(SELECT ID, CASE WHEN Title IN ('training '， 'workshops')然后Title ELSE 'others'结束Title选自事件)AS TGROUP BY Title

Answer 4

另一种方式

select * from (
select  count(case when Title like '%trainings%' then 1 end) as trainings,
count(case when Title like '%workshops%' then 1 end) as workshops,
count(case when Title not like '%workshops%' and Title not like '%trainings%' then 1 end) as Other from Events
)tab
UNPIVOT 
( 
Counts FOR Title IN (trainings, workshops,Other)
) 
AS UnpivotTable order by Counts