样本数据:
Serial | Name
-------------------------
1 | aaaa
1 | bbbb
1 | cccc
2 | aaaa
3 | bbbb
3 | aaaa
3 | cccc
4 | bbbb
4 | cccc
如果我把它和serial
分组,我有4个基团。
Serial | record count in group | name in group |
----------------------------------------
1 | 3 | aaaa,bbbb,cccc |
2 | 1 | aaaa |
3 | 3 | aaaa,bbbb,cccc |
4 | 2 | bbbb,cccc |
并按record count in group
repeat count | name in record count | repeat repeat count |
----------------------------------------------------------|
1 | aaaa | 1 |
2 | bbbb,cccc | 1 |
3 | aaaa,bbbb,cccc | 2 |
在所有分组数据之后,我想要这个:(我期望的最后结果)
我如何实现以下目标?
| Name | record count in group | Available Count |
|------------------------------------------------|
| aaaa | 3 | 2 | (there are 2 aaaa in 3 combinations)
|------------------------------------------------|
| aaaa | 1 | 1 | (there is 1 aaaa in 1 combination)
|------------------------------------------------|
| bbbb | 3 | 2 | (there is 2 bbb in 3 combinations)
|------------------------------------------------|
| bbbb | 2 | 1 | ...
|------------------------------------------------|
| cccc | 3 | 2 |
|------------------------------------------------|
| cccc | 2 | 1 |
|------------------------------------------------|
SQL FIDDLE DEMO
SELECT Serial, COUNT(Serial) GroupCount, Name =
STUFF((SELECT ', ' + Name
FROM your_table b
WHERE b.Serial = a.Serial
FOR XML PATH('')), 1, 2, '')
FROM your_table a
GROUP BY Serial
但是我应该写一个搜索查询来找到我期望的结果
我不确定您的实际需求,但这可能是您想要的:
SELECT Name, cnt, COUNT(*)
FROM
(
SELECT Serial, Name,
COUNT(*) OVER (PARTITION BY Serial) as cnt
FROM your_table
) a
GROUP BY Name, cnt
获取每个Serial的行数作为窗口计数,然后按它分组:Fiddle