本人数据库盲，特来专区求一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] <