SQL查询以特定条件分组 [英] SQL query to count in group by with specific condition

问题描述

我的表格数据:

id      fieldId  Name   Text
----    ------  -----   ----
1        101    name1   a1
2        102    name2   a2
3        101    name1   a1
4        103    name3   a2
5        102    name2   a3
6        101    name1   c1
7        101    name1   a3
8        102    name2   
9        101    name1   b2
10       103    name3   c1

我需要类似

Name    Count(fieldId)  Count_id_text_is_either_a1_or_a2_a3
------      ------        -------
name1   4                   2
name2   3                   2
name3   2                   1

• Name =我需要按fieldId分组.
• Count(fieldId) =计算每个组记录.
• Count_id_text_is_either_a1_or_a2_a3 =仅当Text like 'a1-%' or 'a2-%' or 'a3-%'时，这是我想要组记录记录的列.
• Name = I need to group by fieldId.
• Count(fieldId) = Count each group records.
• Count_id_text_is_either_a1_or_a2_a3 = This is the column I want the count of records of group only if Text like 'a1-%' or 'a2-%' or 'a3-%'.

可以通过单个查询完成吗?如果是，请说明如何?如果否，那么最有效的方法是什么?

Can this be done by single query? If yes the please explain how? If no then what is the most efficient way to do this?

推荐答案

select Name, 
    count(*) as CountAll, 
    count(case when Text like 'a1-%' or Text like 'a2-%' or Text like 'a3-%' then 1 end) as CountA1A2A3
from MyTable
group by Name

如果愿意，也可以使用RegEx.

You could also use a RegEx if you wish.

这篇关于SQL查询以特定条件分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！