我有一张满是姓名和地址的桌子。我希望合并(group_concat)对于城市状态和名称具有相同值的所有行,但前提是其中一个地址是空值。例如,我可能有这个表:
name | addr | city | state | phone
jon snow | 123 got st | la | CA | 1234567890
jon snow | NULL | la | CA | 1225556789
walker | 777 diff ln| la | CA | 4556678899
walker |456 crown rd| la | CA | 33344455566我希望结果是:
name | addr | city | state | phone
jon snow | 123 got st | la | CA | 1234567890,1225556789
walker | 777 diff ln| la | CA | 4556678899
walker |456 crown rd| la | CA | 33344455566目前,我正在使用以下命令:
SELECT name, group_concat(distinct(addr)) as addr, city, state, group_concat(distinct phone) as phone from got_table group by name, city, state having count(*) >1;这样即使有两个非空地址,也会以相同的名称、城市、州合并所有地址。上述声明的结果如下:
name | addr | city | state | phone
jon snow | 123 got st | la | CA | 1234567890,1225556789
walker | 777 diff ln, 456 crown rd| la | CA | 4556678899, 33344455566是否只有当一行的地址为NULL时才进行组合,只有在只有2行匹配时才执行此group_concat。
发布于 2018-09-06 20:16:58
您可以尝试使用子查询按CASE WHEN对其进行分组。
检查addr列是否NULL get。
addr为NULL、get、MAX值或您的concat值期望组。addr不是空get addr看上去像这样。
CREATE TABLE got_table(
name varchar(50),
addr varchar(50),
city varchar(50),
state varchar(50),
phone varchar(50)
);
insert into got_table values ('jon snow','123 got st' ,'la', 'CA', '1234567890');
insert into got_table values ('jon snow',NULL ,'la', 'CA', '1225556789');
insert into got_table values ('walker','777 diff ln' ,'la', 'CA' , '4556678899');
insert into got_table values ('walker','456 crown rd','la', 'CA' , '33344455566');查询1
SELECT name,
case when addr is null then
(
SELECT MAX(tt.addr)
FROM got_table tt
WHERE t1.name = tt.name
and t1.city = tt.city
and t1.state = tt.state
) else addr end as address,
city,
state,
group_concat(distinct phone) as phone
from got_table t1
group by name, city, state,address| name | address | city | state | phone |
|----------|--------------|------|-------|-----------------------|
| jon snow | 123 got st | la | CA | 1225556789,1234567890 |
| walker | 456 crown rd | la | CA | 33344455566 |
| walker | 777 diff ln | la | CA | 4556678899 |https://stackoverflow.com/questions/52211231
复制相似问题