求一个精简的循环语句

[sunshinebbs]

create table a1(id int, name varchar(10))
insert into a1 values(1001 , 'a')
insert into a1 values(1002 , 'b')
insert into a1 values(1003 , 'c')
create table a2(id int, time varchar(10) , date varchar(10))
insert into a2 values(1001 , '08:12','2009-01-01')
insert into a2 values(1001 , '15:33','2009-01-01')
insert into a2 values(1002 , '08:11','2009-01-01')
insert into a2 values(1003 , '08:44','2009-01-01')
insert into a2 values(1002 , '18:22','2009-01-01')
insert into a2 values(1001 , '08:12','2009-01-02')
insert into a2 values(1001 , '15:33','2009-01-02')
insert into a2 values(1002 , '08:11','2009-01-02')
insert into a2 values(1003 , '08:44','2009-01-02')
insert into a2 values(1002 , '18:22','2009-01-02')
go


select a1.id,
a1.name,
time1 = (select top 1 time from a2 where time <= '12:00' and a2.id = a1.id and date like '2009-01-01' order by time),
time2 = (select top 1 time from a2 where time > '12:00' and a2.id = a1.id and date like '2009-01-01' order by time desc)
time3 = (select top 1 time from a2 where time <= '12:00' and a2.id = a1.id and date like '2009-01-02' order by time),
time4 = (select top 1 time from a2 where time > '12:00' and a2.id = a1.id and date like '2009-01-02' order by time desc)
time5 = (select top 1 time from a2 where time <= '12:00' and a2.id = a1.id and date like '2009-01-03' order by time),
time6 = (select top 1 time from a2 where time > '12:00' and a2.id = a1.id and date like '2009-01-03' order by time desc)
time7 = (select top 1 time from a2 where time <= '12:00' and a2.id = a1.id and date like '2009-01-04' order by time),
time8 = (select top 1 time from a2 where time > '12:00' and a2.id = a1.id and date like '2009-01-04' order by time desc)
time9 = (select top 1 time from a2 where time <= '12:00' and a2.id = a1.id and date like '2009-01-05' order by time),
time10 = (select top 1 time from a2 where time > '12:00' and a2.id = a1.id and date like '2009-01-05' order by time desc)，
......
time N条

from a1

我要写几十条，求一个精简的循环语句，不用写这么多代码。

[dawugui]

create table a1(id int, name varchar(10)) 
insert into a1 values(1001 , 'a') 
insert into a1 values(1002 , 'b') 
insert into a1 values(1003 , 'c') 
create table a2(id int, time varchar(10) , date varchar(10)) 
insert into a2 values(1001 , '08:12','2009-01-01') 
insert into a2 values(1001 , '15:33','2009-01-01') 
insert into a2 values(1002 , '08:11','2009-01-01') 
insert into a2 values(1003 , '08:44','2009-01-01') 
insert into a2 values(1002 , '18:22','2009-01-01') 
insert into a2 values(1001 , '08:12','2009-01-02') 
insert into a2 values(1001 , '15:33','2009-01-02') 
insert into a2 values(1002 , '08:11','2009-01-02') 
insert into a2 values(1003 , '08:44','2009-01-02') 
insert into a2 values(1002 , '18:22','2009-01-02') 
go 

declare @sql varchar(8000)
set @sql = 'select id , name '
select @sql = @sql + ' , max(case convert(varchar(10),date,120) when ''' + date + ''' then time1 end) [' + date + '_time1]'
                   + ' , max(case convert(varchar(10),date,120) when ''' + date + ''' then time2 end) [' + date + '_time2]'
from (select distinct convert(varchar(10),date,120) date from a2) as a
set @sql = @sql + ' from (select a1.id , a1.name , t.date ,
      time1 = (select top 1 time from a2 where time <= ''12:00'' and a2.id = a1.id and date = t.date order by time),  
      time2 = (select top 1 time from a2 where time >  ''12:00'' and a2.id = a1.id and date = t.date order by time desc) 
from a1 , a2 t
where a1.id = t.id) m group by id , name'

exec(@sql) 

drop table a1 , a2

/*
id          name       2009-01-01_time1 2009-01-01_time2 2009-01-02_time1 2009-01-02_time2 
----------- ---------- ---------------- ---------------- ---------------- ---------------- 
1001        a          08:12            15:33            08:12            15:33
1002        b          08:11            18:22            08:11            18:22
1003        c          08:44            NULL             08:44            NULL
*/

[sunshinebbs]

又是你啊，大哥

刚测试了一下，sql语句超过8000个字符，可能我数据太多，有一年多的数据，现在我只想查询 2009-01-01 到2009-01-31内的数据

[等不到来世]

sql2005的话，declare @sql varchar(8000)改为declare @sql varchar(max)
2000的话，一段一段生成，再自己手动接起来。。

[dawugui]

[Quote=引用 2 楼 sunshinebbs 的回复:]
又是你啊，大哥

刚测试了一下，sql语句超过8000个字符，可能我数据太多，有一年多的数据，现在我只想查询 2009-01-01 到2009-01-31内的数据
[/Quote]

create table a1(id int, name varchar(10)) 
insert into a1 values(1001 , 'a') 
insert into a1 values(1002 , 'b') 
insert into a1 values(1003 , 'c') 
create table a2(id int, time varchar(10) , date varchar(10)) 
insert into a2 values(1001 , '08:12','2009-01-01') 
insert into a2 values(1001 , '15:33','2009-01-01') 
insert into a2 values(1002 , '08:11','2009-01-01') 
insert into a2 values(1003 , '08:44','2009-01-01') 
insert into a2 values(1002 , '18:22','2009-01-01') 
insert into a2 values(1001 , '08:12','2009-01-02') 
insert into a2 values(1001 , '15:33','2009-01-02') 
insert into a2 values(1002 , '08:11','2009-01-02') 
insert into a2 values(1003 , '08:44','2009-01-02') 
insert into a2 values(1002 , '18:22','2009-01-02') 
go 

declare @sql varchar(8000)
set @sql = 'select id , name '
select @sql = @sql + ' , max(case convert(varchar(10),date,120) when ''' + date + ''' then time1 end) [' + date + '_time1]'
                   + ' , max(case convert(varchar(10),date,120) when ''' + date + ''' then time2 end) [' + date + '_time2]'
from (select distinct convert(varchar(10),date,120) date from a2 where convert(varchar(7),date,120) = '2009-01') as a
set @sql = @sql + ' from (select a1.id , a1.name , t.date ,
      time1 = (select top 1 time from a2 where time <= ''12:00'' and a2.id = a1.id and date = t.date order by time),  
      time2 = (select top 1 time from a2 where time >  ''12:00'' and a2.id = a1.id and date = t.date order by time desc) 
from a1 , a2 t
where a1.id = t.id and convert(varchar(7),t.date,120) = ''2009-01'') m group by id , name'

exec(@sql) 

drop table a1 , a2

/*
id          name       2009-01-01_time1 2009-01-01_time2 2009-01-02_time1 2009-01-02_time2 
----------- ---------- ---------------- ---------------- ---------------- ---------------- 
1001        a          08:12            15:33            08:12            15:33
1002        b          08:11            18:22            08:11            18:22
1003        c          08:44            NULL             08:44            NULL

*/

[sunshinebbs]

CSDN真是程序员的乐土阿！
dawugui 大哥响应这么快速，应该是CSDN的工作人员吧，呵呵，祝CSDN越办越好，dawugui 前途似锦

[sdhdy]

多定义几个变量 varchar(8000)，然后一个一个接起来，就没问题了。