本人数据库盲，特来专区求一SQL，解决给分
要求如下：
表1
id subject
1 aa
2 bb
3 cc
4 dd
表2
id taskid day username
1 1 2007-07-02 admin
2 2 2007-07-02 admin
3 3 2007-07-03 admin
4 4 2007-07-02 admin
结果要求显示：
1 admin aa 2007-07-02
2 admin bb 2007-07-02
3 admin dd 2007-07-02
1 admin cc 2007-07-03
其中，表2的day和username为查询条件
即按时间排序将表1的数据取出来，并且前面显示该天的任务条数编号
如1 2 3 1
[解决办法]
declare @t1 table(id int,subject varchar(6))
insert into @t1 select 1, 'aa '
insert into @t1 select 2, 'bb '
insert into @t1 select 3, 'cc '
insert into @t1 select 4, 'dd '
declare @t2 table(id int,taskid int,day datetime,username varchar(10))
insert into @t2 select 1,1, '2007-07-02 ', 'admin '
insert into @t2 select 2,2, '2007-07-02 ', 'admin '
insert into @t2 select 3,3, '2007-07-03 ', 'admin '
insert into @t2 select 4,4, '2007-07-02 ', 'admin '
select
(select count(*) from @t2 where day <b.day or (day=b.day and taskid <=b.taskid)) as nid,
b.username,a.subject,b.day
from
@t1 a,@t2 b
where
a.id=b.taskid
order by
nid
/*
nid username subject day
----------- ---------- ------- ------------------------------------------------------
1 admin aa 2007-07-02 00:00:00.000
2 admin bb 2007-07-02 00:00:00.000
3 admin dd 2007-07-02 00:00:00.000
4 admin cc 2007-07-03 00:00:00.000
*/
[解决办法]
create table #表1
(id int, subject varchar(5))
insert into #表1 select 1, 'aa '
union all select 2, 'bb '
union all select 3, 'cc '
union all select 4, 'dd '
create table #表2
(id int, taskid int, day datetime, username varchar(10))
insert into #表2 select 1, 1, '2007-07-02 ', 'admin '
union all select 2, 2, '2007-07-02 ', 'admin '
union all select 3, 3, '2007-07-03 ', 'admin '
union all select 4, 4, '2007-07-02 ', 'admin '
select
count(1)[count],min(c.subject)[subject],
min(a.day)[day],min(a.username)[username]
from #表2 a
left join #表2 b
on b.id <=a.id and b.day=a.day
left join #表1 c
on a.taskid=c.id
group by a.id
order by day
count subject day username
----------- ------- ------------------------------------------------------ ---------
1 aa 2007-07-02 00:00:00.000 admin
2 bb 2007-07-02 00:00:00.000 admin
3 dd 2007-07-02 00:00:00.000 admin
1 cc 2007-07-03 00:00:00.000 admin
（所影响的行数为 4 行）
[解决办法]
ustbwuyi() ( ) 信誉：100 Blog 加为好友 2007-07-06 15:36:47 得分: 0
有点不对哈，前面nid
------------------
你的原數據是怎樣的？你運行的語句是怎樣的？
