数据表T
id pid col
1 1 col1
2 1 col2
3 1 col3
数据表D
tid content orders
1 testA1 1
1 testb1 2
1 testc1 3
1 testd1 4
2 testA2 1
2 testb2 2
2 testc2 3
2 testd2 4
3 testA3 1
3 testB3 2
3 testc3 3
3 testd4 4
T表的外键T.id对应D表的D.tid
要求查询结果当T.pid=1时:
orders col1 col2 col3
1 testA1 testA2 testA3
2 testb1 testb2 testb3
3 testc1 testc2 testc3
4 testd1 testd2 testd3
id pid col
1 1 col1
2 1 col2
3 1 col3
数据表D
tid content orders
1 testA1 1
1 testb1 2
1 testc1 3
1 testd1 4
2 testA2 1
2 testb2 2
2 testc2 3
2 testd2 4
3 testA3 1
3 testB3 2
3 testc3 3
3 testd4 4
T表的外键T.id对应D表的D.tid
要求查询结果当T.pid=1时:
orders col1 col2 col3
1 testA1 testA2 testA3
2 testb1 testb2 testb3
3 testc1 testc2 testc3
4 testd1 testd2 testd3
set @sql=''
select @sql=@sql+',max(case tid when '+cast(id as varchar)+' then content end) as ['+col+']'
from t
where pid=1exec('select orders'+@sql+' from D group by orders')
create table T(
id int,
pid int,
col varchar(6)
)
insert T select
1, 1, 'col1'
union all select
2, 1, 'col2'
union all select
3, 1, 'col3'create table D (
tid int,
content varchar(10),
orders int
)
insert D select
1, 'testA1', 1
union all select
1, 'testb1', 2
union all select
1, 'testc1', 3
union all select
1, 'testd1', 4
union all select
2, 'testA2', 1
union all select
2, 'testb2', 2
union all select
2, 'testc2', 3
union all select
2, 'testd2', 4
union all select
3, 'testA3', 1
union all select
3, 'testB3', 2
union all select
3, 'testc3', 3
union all select
3, 'testd4', 4go--查询
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case tid when '+cast(id as varchar)+' then content end) as ['+col+']'
from t
where pid=1exec('select orders'+@sql+' from D group by orders')--结果
orders col1 col2 col3
----------- ---------- ---------- ----------
1 testA1 testA2 testA3
2 testb1 testb2 testB3
3 testc1 testc2 testc3
4 testd1 testd2 testd4--删除环境
drop table T,D
不知道这个关系大不大
insert into @t
select 1, 1, 'col1' union all
select 2, 1, 'col2' union all
select 3, 1, 'col3'
declare @d table(tid int,[content] varchar(10),orders int)
insert into @d
select 1, 'testA1', 1 union all
select 1, 'testb1', 2 union all
select 1, 'testc1', 3 union all
select 1, 'testd1', 4 union all
select 2, 'testA2', 1 union all
select 2, 'testb2', 2 union all
select 2, 'testc2', 3 union all
select 2, 'testd2', 4 union all
select 3, 'testA3', 1 union all
select 3, 'testB3', 2 union all
select 3, 'testc3', 3 union all
select 3, 'testd4', 4--query
select orders,
max(case tid when 1 then [content] end) col1,
max(case tid when 2 then [content] end) col2,
max(case tid when 3 then [content] end) col3
from @d,@t
where id=tid and pid=1
group by pid,orders