SQL字段合并和多表联合查询的有关问题

SQL字段合并和多表联合查询的问题
现在有两个表A，B，

A表：

ID NAME
1 amy
2 wang
3 chris

B表

ID PRICE LOCATION
1 11 aaa
1 22 bbb
2 10 ccc
2 15 eee

B表中最多有两条A.ID=B.ID的数据，我想得到的查询结果为：

ID NAME RESULT1 RESULT2
1 amy 11|aaa 22|bbb
2 wang 10|ccc 15|eee
请问查询语句如果写，多谢各位。

------解决方案--------------------

SQL code


;with cte  as    
(select ID,NAME,[RESULT1]=stuff((select '|'+[RESULT1] from B t where ID=tb.ID for xml path('')), 1, 1, '')) from tb  group by id )a,
union all
(select ID,NAME,[RESULT2]=stuff((select '|'+[RESULT2] from B t where ID=tb.ID for xml path('')), 1, 1, '')) from tb  group by id )b

select ID,NAME,RESULT1,RESULT2  from cte  left join A on cte.ID=A.id

------解决方案--------------------

SQL code

--> 测试数据：[ta]
IF OBJECT_ID('[ta]') IS NOT NULL DROP TABLE [ta]
GO
CREATE TABLE [ta]([ID] INT,[NAME] VARCHAR(5))
INSERT [ta]
SELECT 1,'amy' UNION ALL
SELECT 2,'wang' UNION ALL
SELECT 3,'chris'
GO

IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([ID] INT,[PRICE] INT,[LOCATION] VARCHAR(3))
INSERT [tb]
SELECT 1,11,'aaa' UNION ALL
SELECT 1,22,'bbb' UNION ALL
SELECT 2,10,'ccc' UNION ALL
SELECT 2,15,'eee'
GO

--> 测试语句：
SELECT a.[ID],a.[name],
max(case when b.row_id=1 then ltrim([PRICE])+'|'+[LOCATION] else '' end) as [result1],
max(case when b.row_id=2 then ltrim([PRICE])+'|'+[LOCATION] else '' end) as [result2]
FROM 
[ta] a,
(select *,row_id=row_number() over(partition by id order by id) from tb) as b 
where  a.[ID]=b.[ID]
group by a.[ID],a.[name]
/*
ID          name  result1          result2
----------- ----- ---------------- ----------------
1           amy   11|aaa           22|bbb
2           wang  10|ccc           15|eee
*/

SQL字段合并和多表联合查询的有关问题

相关推荐