關於只查詢出前幾列數據的問題?

[peter_dmh]

我有兩個表(A和B)
A
no count
n1 3
n2 2
n3 1

B
no time number
n1 2006-10-1 4
n1 2006-10-2 2
n1 2006-10-3 8
n1 2006-10-4 1
n1 2006-10-5 3
n1 2006-10-6 5
n2 2006-10-1 4
n2 2006-10-2 4
n2 2006-10-3 4
n2 2006-10-4 4
n3 2006-10-1 4
n3 2006-10-2 4

需要查詢的結果如下(#TEMP)
no time number
n1 2006-10-1 4
n1 2006-10-2 2
n1 2006-10-3 8
n2 2006-10-1 4
n2 2006-10-2 4
n3 2006-10-1 4

我用top n實現不了..B表中列了很多詳細數據,A表只是說明各項需要讀出的數據筆數(動態的).通過一個SQL實現
讀出B表中的數據根據關連A表中的字段中所需要的行數.(如n1需3行,則讀出3行,n2需要2行就讀出2行)

謝謝!

[splory]

动态查询

[splory]

declare @sql varchar(8000)
set @sql=''
select @sql=@sql+'select top '+rtrim([count])+ ' * from B where no='+rtrim([no])+' union all ' from A
select @sql=left(@sql,len(@sql)-10)
exec(@sql)

[splory]

你测试一下
随手写的

[peter_dmh]

exec()?

[拓狼]

--生成测试数据
declare @A table(no varchar(10),[count] int)
insert into @a select 'n1', 3
insert into @a select 'n2', 2
insert into @a select 'n3', 1

declare @B table(no varchar(10),time datetime, number int)
insert into @b select 'n1' ,'2006-10-1', 4
insert into @b select 'n1' ,'2006-10-2', 2
insert into @b select 'n1' ,'2006-10-3', 8
insert into @b select 'n1' ,'2006-10-4', 1
insert into @b select 'n1' ,'2006-10-5', 3
insert into @b select 'n1' ,'2006-10-6', 5
insert into @b select 'n2' ,'2006-10-1', 4
insert into @b select 'n2' ,'2006-10-2', 4
insert into @b select 'n2' ,'2006-10-3', 4
insert into @b select 'n2' ,'2006-10-4', 4
insert into @b select 'n3' ,'2006-10-1', 4
insert into @b select 'n3' ,'2006-10-2', 4

--解决方法
select b.no,b.time,b.number from @a a inner join
(
select *,[count]=(select count(*) from @b x where x.no=y.no and x.time<=y.time) from @b y
)b on a.no=b.no and a.count>=b.count

[marco08]

create table A(no char(2), [count] int)
insert A select 'n1', 3
union all select 'n2', 2
union all select 'n3', 1

create table B(no char(2), [time] datetime, number int)
insert B select 'n1', '2006-10-1', 4
union all select 'n1', '2006-10-2', 2
union all select 'n1', '2006-10-3', 8
union all select 'n1', '2006-10-4', 1
union all select 'n1', '2006-10-5', 3
union all select 'n1', '2006-10-6', 5
union all select 'n2', '2006-10-1', 4
union all select 'n2', '2006-10-2', 4
union all select 'n2', '2006-10-3', 4
union all select 'n2', '2006-10-4', 4
union all select 'n3', '2006-10-1', 4
union all select 'n3', '2006-10-2', 4

declare @sql varchar(8000)
set @sql=''
select @sql=@sql+' select top '+cast([count] as varchar)+' * from B where no='''+no+''' union all ' from A
select @sql=left(@sql, len(@sql)-9)
exec(@sql)
--result
no time number
---- ------------------------------------------------------ -----------
n1 2006-10-01 00:00:00.000 4
n1 2006-10-02 00:00:00.000 2
n1 2006-10-03 00:00:00.000 8
n2 2006-10-01 00:00:00.000 4
n2 2006-10-02 00:00:00.000 4
n3 2006-10-01 00:00:00.000 4

[DragonBill]

create table A(Num varchar(3), Cnt int)
insert into A values('n1',3)
insert into A values('n2',2)
insert into A values('n3',1)



create table B(Num varchar(3), D_Time varchar(10), Number int)
alter table B alter column D_Time DateTime
insert into B values('n1', '2006-10-1', 4)
insert into B values('n1', '2006-10-2', 2)
insert into B values('n1', '2006-10-3', 8)
insert into B values('n1', '2006-10-4', 1)
insert into B values('n1', '2006-10-5', 3)
insert into B values('n1', '2006-10-6', 5)
insert into B values('n2', '2006-10-1', 4)
insert into B values('n2', '2006-10-2', 4)
insert into B values('n2', '2006-10-3', 4)
insert into B values('n2', '2006-10-4', 4)
insert into B values('n3', '2006-10-1', 4)
insert into B values('n3', '2006-10-2', 4)


select a.* from B a
left join A a1 on a.Num = a1.Num
where a1.Cnt >= (select count(Num) from B where Num = a.Num and D_Time <= a.D_Time)
--order by a.Num, a.D_Time

drop table A
drop table B