多表查询语句这个怎么写 在线等
S表中id name
1 1 张三
2 2 李四
3 3 王五
4 4 赵六
c表中 id name
1 1 宝马
2 2 奥迪
3 3 雪铁龙
4 4 奔驰
5 5 兰博基尼
6 6 大众
sc表中 sid cid SMoney 要得到查询显示如下所示的结果
1 1 1 50.00
2 1 2 30.00 姓名 宝马 奥迪 雪铁龙 奔驰 兰博基尼 大众
3 1 3 50.00 张三 50.00 30.00 50.00 80.00 NULL NULL
4 1 4 80.00 李四 70.00 35.00 40.00 50.00 NULL NULL
5 2 1 25.00 王五 NULL NULL NULL 50.00 NULL 60.OO
6 2 2 35.00 赵六 90.00 NULL NULL NULL 80.OO NULL
7 2 3 40.oo
8 2 4 50.00
9 2 1 70.00
10 3 4 50.00
11 3 6 60.00
12 4 5 80.00
13 4 1 90.00
14 1 1 30.00
[解决办法]
三表联查 行列转换。
[解决办法]
三表联查 行列转换。
[解决办法]
select b.姓名,sum(b.宝马),sum(b.奥迪),sum(b.雪铁龙),sum(b.奔驰),sum(b.兰博基尼),sum(b.大众) from
( select s.name as 姓名,(case when c.name='宝马'then sc.smoney else null end) as 宝马 ,
(case when c.name='奥迪'then sc.smoney else null end) as 奥迪,
(case when c.name='雪铁龙'then sc.smoney else null end) as 雪铁龙,
(case when c.name='奔驰'then sc.smoney else null end) as 奔驰,
(case when c.name='兰博基尼'then sc.smoney else null end) as 兰博基尼,
(case when c.name='大众'then sc.smoney else null end) as 大众
from s,c,sc
where sc.sid=s.id and sc.cid=c.id ) b
group by b.姓名
[解决办法]
select b.姓名,sum(b.宝马),sum(b.奥迪),sum(b.雪铁龙),sum(b.奔驰),sum(b.兰博基尼),sum(b.大众) from
( select s.name as 姓名,(case when c.name='宝马'then sc.smoney else null end) as 宝马 ,
(case when c.name='奥迪'then sc.smoney else null end) as 奥迪,
(case when c.name='雪铁龙'then sc.smoney else null end) as 雪铁龙,
(case when c.name='奔驰'then sc.smoney else null end) as 奔驰,
(case when c.name='兰博基尼'then sc.smoney else null end) as 兰博基尼,
(case when c.name='大众'then sc.smoney else null end) as 大众
from s,c,sc
where sc.sid=s.id and sc.cid=c.id ) b
group by b.姓名
