嵌入式SQL/计数/分组依据? [英] Embedded SQL / Count / GroupBy?

问题描述

大家好，

寻求帮助以组合一些子选择的SQL代码.

样本表数据:

Hi All,

looking for help to put together some sub-select SQL code.

Sample table data:

-------------------------
RecID | GroupID | User  |
-------------------------
  1   |    1    | John  |
  2   |    1    | Mary  |
  3   |    1    | Fred  |
  4   |    2    | Joe   |
  5   |    2    | Mary  |
  6   |    3    | Fred  |
  7   |    3    | John  |
  8   |    3    | Mary  |
  9   |    4    | Tony  |
  10  |    5    | Fred  |
-------------------------

我想在不知道GroupID的情况下查询一组特定用户的数量.
例如，(约翰+玛丽+弗雷德)出现在分组中多少次了?
(在上面的示例中，它两次，但是顺序不同).

我很生疏，但认为它应该是来自[选择分组]的选择计数(*)
[设置?]中的用户在哪里?

对有效的东西有什么想法吗?

非常感谢，

艾伦.

I want to query the count of a particular set of users without knowing the GroupID.
For example, how many times do (John + mary + Fred) appear in a grouping?
(in the sample above, its twice, but in a different order).

I am very rusty but think it should be a "Select count (*) from [select Grouping]
where User in [set?]

Any thoughts on something efficent?

many thanks,

Allen.

推荐答案

由Kent在MSDN上解决

！declare @测试表
(recId整数，
GroupId整数，
[用户] varchar(9))
插入@test
选择1，，1，``John''union all
选择2，1，``Mary''union all
选择3，1，``Fred''所有联合
选择4、2，``Joe''union all
选择5、2，"Mary"联合全部
选择6，3，"Fred"联合所有
选择7，3，``John''union all
选择8，3，"Mary"并集所有
选择9，4，``Tony''union all
选择10、5，``弗雷德''

选择
GroupId
来自@test
其中[user]在(``John''，``Mary''，``Fred'')
按groupId分组
拥有count(distinct [user])= 3

/* --------输出:--------
GroupId
-----------
1
3

(受影响的1行)
*/

选择count(*)作为Group_Count
来自
(选择
GroupId
来自@test
其中[user]在(``John''，``Mary''，``Fred'')
按groupId分组
拥有count(distinct [user])= 3
)x

/* --------输出:--------
Group_Count
-----------
2
*/

solved by Kent on MSDN

!declare @test table
( recId integer,
GroupId integer,
[User] varchar(9))
insert into @test
select 1 , 1, ''John'' union all
select 2 , 1, ''Mary'' union all
select 3 , 1, ''Fred'' union all
select 4 , 2, ''Joe '' union all
select 5 , 2, ''Mary'' union all
select 6 , 3, ''Fred'' union all
select 7 , 3, ''John '' union all
select 8 , 3, ''Mary'' union all
select 9 , 4, ''Tony'' union all
select 10, 5, ''Fred''

select
GroupId
from @test
where [user] in (''John'',''Mary'',''Fred'')
group by groupId
having count(distinct [user]) = 3

/* -------- Output: --------
GroupId
-----------
1
3

(1 row(s) affected)
*/

select count(*) as Group_Count
from
( select
GroupId
from @test
where [user] in (''John'',''Mary'',''Fred'')
group by groupId
having count(distinct [user]) = 3
) x

/* -------- Output: --------
Group_Count
-----------
2
*/

这篇关于嵌入式SQL/计数/分组依据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！