SQL列/行分组

Question

我刚开始学SQL。我正在寻找一个简单的SQL解决方案，以合并包含相同数据（在这种情况下是邮政编码）的列/行。例如，数据看起来像这样：

(数据表)

我希望最终的数据看起来像这样：

(数据表)

您可以看到，两行数据已经被合并或总结了。对于包含完全相同邮政编码的行，城市名称（两个都）出现在城市列中，并且计数是每行计数的总和。

有没有办法使用SQL来实现这一点？即使需要两个不同的SQL语句也没问题。

- user2019517

Answer 1

假设使用 SQL Server，您可以使用 FOR XML 来获取所需的结果。

select distinct t.state,t.county,t.zip,t2.sumcount,
STUFF(
        (
            SELECT '/' + city AS [text()]
            FROM mytable t3
            WHERE t.zip = t3.zip
            FOR XML PATH('')
        ), 1, 1, '') AS ColList
from mytable t 
  join (select zip, sum(count) as sumcount
       from mytable
       group by zip) t2 on t.zip=t2.zip

还有一些SQL Fiddle。

如果你正在使用MySQL，可以考虑使用GROUP_CONCAT：

select distinct t.state,t.county,t.zip,t2.sumcount,
GROUP_CONCAT(t.city) as cities
from mytable t 
  join (select zip, sum(count) as sumcount
       from mytable
       group by zip) t2 on t.zip=t2.zip
GROUP BY t.state,t.county,t.zip,t2.sumcount

还有更多Fiddle。

祝你好运。

- sgeddes

Answer 2

我修改了sgeddes的出色答案，以实际获得计数并避免重复条目，并添加了一个支持脚本，使您可以测试它。 这确实假定了SQL Server。

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'MYTABLE') BEGIN
  drop table MYTABLE;
END;
go

create table MYTABLE
(
  state nvarchar(2)
 ,county nvarchar(100)
 ,city nvarchar(100)
 ,zip nvarchar(10)
)
go

insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','HAYWARD','94541');
insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','HAYWARD','94541');
insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','HAYWARD','94544');
insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','HAYWARD','94545');
insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','CASTRO VALLEY','94546');
insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','CASTRO VALLEY','94546');
insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','CASTRO VALLEY','94546');
insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','CASTRO VALLEY','94546');
insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','CASTRO VALLEY','94546');
insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','HAYWARD','94546');
insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','HAYWARD','94546');
insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','HAYWARD','94546');
insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','HAYWARD','94546');
insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','LIVERMORE','94550');
insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','LIVERMORE','94551');
insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','CASTRO VALLEY','94552');
insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','HAYWARD','94552');
insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','FREMONT','94555');

select distinct
  t.state
 ,t.county
 ,t.zip
 ,t2.sumcount
 ,STUFF((
       SELECT distinct '/' + city AS [text()]
       FROM mytable t3
       WHERE t.zip = t3.zip
       FOR XML PATH('')
       ), 1, 1, '') AS ColList
from
  mytable t 
  inner join
  (
  select zip, sum(count) as sumcount
  from
    (
    select zip,count(*) as count
    from mytable
    group by zip
    ) x
  group by zip
  ) t2
  on t.zip=t2.zip

输出结果如下：

state   county  zip sumcount    ColList
CA  ALAMEDA 94541   2   HAYWARD
CA  ALAMEDA 94544   1   HAYWARD
CA  ALAMEDA 94545   1   HAYWARD
CA  ALAMEDA 94546   9   CASTRO VALLEY/HAYWARD
CA  ALAMEDA 94550   1   LIVERMORE
CA  ALAMEDA 94551   1   LIVERMORE
CA  ALAMEDA 94552   2   CASTRO VALLEY/HAYWARD
CA  ALAMEDA 94555   1   FREMONT

- JosephStyons