22,207
社区成员
发帖
与我相关
我的任务
分享
create table [A]([id] int,[名称] varchar(8))
insert [A]
select 1,'可口可乐'
create table [B]([id] int,[A_id] int,[A_Name] varchar(4))
insert [B]
select 1,1,'容量' union all
select 2,1,'口味'
create table [C]([id] int,[B_id] int,[value] varchar(6))
insert [C]
select 1,1,'500ml' union all
select 2,1,'1000ml' union all
select 3,2,'原味' union all
select 4,2,'无糖'
go
select 名称 ,
max(case px when 1 then value else '' end) value1,
max(case px when 2 then value else '' end) value2,
max(case px when 3 then value else '' end) value3,
max(case px when 4 then value else '' end) value4
from
(
select t1.* , px = (select count(1) from
(select a.名称 , c.* from a, b, c where a.id = b.a_id and b.id = c.b_id) t2
where t2.名称 = t1.名称 and t2.id < t1.id) + 1 from
(select a.名称 , c.* from a, b, c where a.id = b.a_id and b.id = c.b_id) t1
) m
group by 名称
drop table a , b , c
/*
名称 value1 value2 value3 value4
-------- ------ ------ ------ ------
可口可乐 500ml 1000ml 原味 无糖
(所影响的行数为 1 行)
*/