sql - 如何查询: "for which do these values apply"?

Question

我正在尝试匹配和对齐数据，或者重新说，计算出现次数，然后列出出现这些次数的值。

或者，在一个问题中:“每个 ID 值出现了多少次，对应的名称是什么？”

例如，有了这个输入

Name    ID
-------------
jim     123
jim     234
jim     345
john    123
john    345
jane    234
jane    345
jan    45678

我希望输出为:

count    ID    name    name    name
------------------------------------
3       345    jim    john    jane
2       123    jim    john
2       234    jim    jane
1       45678    jan

或者类似地，输入可以是(注意 ID 值未对齐)，

jim    john    jane    jan
----------------------------
123    345    234    45678
234    123    345        
345                

但这似乎使事情变得复杂。

在 SQL 中尽可能接近我想要的结果，因为

for ID, count(ID)
from table
group by (ID)
order by count desc

哪些输出

ID    count
------------
345    3
123    2
234    2
45678    1

我将不胜感激。

Answer 1

你似乎想要一个支点。在 SQL 中，您必须提前指定列数(除非您将查询构造为字符串)。

但思路是:

select ID, count(*) as cnt,
       max(case when seqnum = 1 then name end) as name_1,
       max(case when seqnum = 2 then name end) as name_2,
       max(case when seqnum = 3 then name end) as name_3
from (select t.*,
             row_number() over (partition by id order by id) as seqnum  -- arbitrary ordering
      from table t
     ) t
group by ID
order by count desc;

如果您有未知数量的列，您可以将这些值聚合到一个数组中:

select ID, count(*) as cnt,
       array_agg(name order by name) as names
from table t
group by ID
order by count desc