从已知的两张表中，如何查询出我希望得到的数据（棘手）？

[gsk99]

数据表Zl004结构如下：（zl0401，Zl0402为联合主键）

zl0401 Zl0402 Zl0403 Zl0404
GP06040101 222005 87.0 >=
GP06040101 222006 1.5 <=


数据表zl013结构如下：（Zl1301，Zl1302，Zl1303为联合主键）
（Zl1301，Zl1302与zl004中的zl0401，Zl0402对应）

Zl1301 Zl1302 Zl1303 Zl1304
GP06040101 222005 1 88.6
GP06040101 222005 2 88.5
GP06040101 222005 3 89.0
GP06040101 222005 4 89.1
GP06040101 222005 5 87.0
GP06040101 222005 6 89.8
GP06040101 222006 3 0.8

我想要的结果:
zl0401 Zl0402 val1 val2 val3 val4 val5 val6
GP06040101 222005 88.6 88.5 89.0 89.1 87.0 89.8
GP06040101 222006 0.8 null null null null null

多谢指导

[xiao_deng]

参考：


Create Table TEST(XVarchar(10),Y Numeric(10,2))
Insert TEST Select 'a', 1001
Union All Select 'a', 1001.01
Union All Select 'a', 1001.02
Union All Select 'a', 1001.03
Union All Select 'b', 1001
GO
Select (Select Count(*) From TEST WHere X=A.X And Y<=A.Y) As ID,* Into #T From TEST A
Declare @S Varchar(8000)
Select @S=''
Select @S=@S+',Max(Case ID When '+Rtrim(ID) +' Then Y Else 0 End) As '+Char(65+ID) From #T Group By ID Order By ID
Select @S='Select X As A'+@S+' From #T Group By X'
EXEC(@S)
GO
Drop Table TEST,#T
GO
/*
ABCDE
a1001.001001.011001.021001.03
b1001.00.00.00.00
*/

[子陌红尘]

create table zl013(Zl1301 varchar(10),Zl1302 int,Zl1303 int,Zl1304 numeric(5,1))
set nocount on
insert into zl013 select 'GP06040101',222005,1,88.6
insert into zl013 select 'GP06040101',222005,2,88.5
insert into zl013 select 'GP06040101',222005,3,89.0
insert into zl013 select 'GP06040101',222005,4,89.1
insert into zl013 select 'GP06040101',222005,5,87.0
insert into zl013 select 'GP06040101',222005,6,89.8
insert into zl013 select 'GP06040101',222006,3,0.8
set nocount off
go

declare @sql varchar(8000),@i int
select @i=max(num),@sql='' from (select count(1) as num from zl013 group by Zl1301,Zl1302) t

while @i>0
begin
set @sql=',max(case num when '+rtrim(@i)+' then Zl1304 end) as val'+rtrim(@i)+@sql
set @i=@i-1
end
set @sql='select Zl1301,Zl1302'+@sql+' from (select t.*,(select count(1) from zl013 where Zl1301=t.Zl1301 and Zl1302=t.Zl1302 and Zl1303<=t.Zl1303) as num from zl013 t) a group by Zl1301,Zl1302'

exec(@sql)
go

/*
Zl1301 Zl1302 val1 val2 val3 val4 val5 val6
---------- ----------- ------- ------- ------- ------- ------- -------
GP06040101 222005 88.6 88.5 89.0 89.1 87.0 89.8
GP06040101 222006 .8 NULL NULL NULL NULL NULL
*/

drop table zl013
go

[碧海情天-赵亮]

有点意思
mark