表a
id name
1 淘宝
2 京东
3 亚马逊表b
name money1
淘宝 10
淘宝 20
京东 15
京东 25表c
name money2
淘宝 5
淘宝 15
亚马逊 20
亚马逊 40怎么写连接查询 出
id name money1 money2
1 淘宝 30 20
2 京东 40 0
3 亚马逊 0 60之前用的 select 嵌套 select 数据多的时候 反应很慢
id name
1 淘宝
2 京东
3 亚马逊表b
name money1
淘宝 10
淘宝 20
京东 15
京东 25表c
name money2
淘宝 5
淘宝 15
亚马逊 20
亚马逊 40怎么写连接查询 出
id name money1 money2
1 淘宝 30 20
2 京东 40 0
3 亚马逊 0 60之前用的 select 嵌套 select 数据多的时候 反应很慢
select (name,sum(money1),money1(money2) from(
select id,a.name,money1,money2 from a join b on a.name=b.name
join c on a.name=c.name) mm
group by cc.name
SELECT a.id,a.NAME SUM(b.money1) AS money1,SUM(c.money2) AS money2
FROM [表a] a LEFT JOIN [表b] b ON a.NAME=b.NAME --这里最好用id
LEFT JOIN [表c] c ON a.NAME=b.NAME --也最好用name
GROUP BY a.id,a.name
;WITH a (id,name)AS(
select 1,'淘宝' union all
select 2,'京东' union all
select 3,'亚马逊'
),b(name,money1)AS (
select '淘宝',10 union all
select '淘宝',20 union all
select '京东', 15 union all
select '京东', 25
),c(name,money2)AS (
select '淘宝',5 union all
select '淘宝',15 union all
select '亚马逊',20 union all
select '亚马逊',40
)select id,name,isnull(sum(money1),0),isnull(sum(money2),0) from (
select id,a.name,money1,money2 from a left join b on a.name=b.name
left join c on a.name = c.name ) t
group by t.name,t.id--结果
1 淘宝 60 40
2 京东 40 0
3 亚马逊 0 60
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[name] nvarchar(23))
Insert #T
select 1,N'淘宝' union all
select 2,N'京东' union all
select 3,N'亚马逊'
GO
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([name] nvarchar(22),[money1] int)
Insert #T1
select N'淘宝',10 union all
select N'淘宝',20 union all
select N'京东',15 union all
select N'京东',25
GO
if not object_id(N'Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([name] nvarchar(23),[money2] int)
Insert #T2
select N'淘宝',5 union all
select N'淘宝',15 union all
select N'亚马逊',20 union all
select N'亚马逊',40
Go
--测试数据结束
SELECT #T.* ,
ISNULL(SUM(#T1.money1),0) AS money1 ,
ISNULL(SUM(#T2.money2),0) AS money2
FROM #T
LEFT JOIN #T1 ON #T1.name = #T.name
LEFT JOIN #T2 ON #T2.name = #T.name
GROUP BY #T.id ,
#T.name
ORDER BY id
结果: