本人数据库盲，特来专区求一SQL，解决给分

[ustbwuyi]

要求如下：
表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

[mengmou]

看不明白

[子陌红尘]

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
*/

[RunUpwind]

select (select count(*) from 表2 a where a.day<=表1.day)as no, username,subject,day from 表1 join 表2 on 表1.id=表2.id

[ustbwuyi]

谢谢libin_ftsafe(子陌红尘：TS for Banking Card) ，果然数据库高手
我回去试试，解决马上给分
insert into @t1 select 1,'aa'
insert into @t1 select 2,'bb'
insert into @t1 select 3,'cc'
insert into @t1 select 4,'dd'

如果我有N多条数据呢？

[w75251455]

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 行）

[子陌红尘]

如果我有N多条数据呢？
------------------------------------------------------------------------------------
跟数据量应该无关，我只是借助表变量生成了一组测试数据，楼主直接用表名替代@t1/@t2即可。

[mengmou]

还是不明白楼主的意思

[子陌红尘]

--我写的SQL需要修改一下，以实现每天的数据从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 and taskid<=b.taskid) as nid,
b.username,a.subject,b.day
from
@t1 a,@t2 b
where
a.id=b.taskid
order by
day,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
1 admin cc 2007-07-03 00:00:00.000
*/

[w75251455]

ustbwuyi() ( ) 信誉：100 Blog 加为好友 2007-07-05 15:24:20 得分: 0


谢谢libin_ftsafe(子陌红尘：TS for Banking Card) ，果然数据库高手
我回去试试，解决马上给分
insert into @t1 select 1,'aa'
insert into @t1 select 2,'bb'
insert into @t1 select 3,'cc'
insert into @t1 select 4,'dd'

如果我有N多条数据呢？


---------------------
我错了？？？。。。。

[w75251455]

我晕~~~如果这次还比钻钻拿的分少~~我就要和钻钻拼命了......

[mengmou]

等这看结果，哈哈

[w75251455]

mengmou()mengmou() ( ) 信誉：98 Blog 加为好友 2007-07-05 15:31:05 得分: 0


还是不明白楼主的意思


------------------------------------------------------
即按时间排序将表1的数据取出来，并且前面显示该天的任务条数编号

[mengmou]

哦

[ustbwuyi]

得到 2 Admin 打印文档 2007-07-02 00:00:00.000
2 Admin 打印文档 2007-07-02 00:00:00.000
3 Admin 去如皋开会 2007-07-02 00:00:00.000
5 Admin 都是 2007-07-02 00:00:00.000
5 Admin 都是 2007-07-02 00:00:00.000
6 Admin 都是的 2007-07-02 00:00:00.000
7 Admin 都是的好 2007-07-02 00:00:00.000
1 Admin 打印文档2 2007-07-03 00:00:00.000
2 Admin 基督教分开 2007-07-03 00:00:00.000
1 Admin 测试 2007-07-04 00:00:00.000
1 Admin aaa 2007-07-06 00:00:00.000
有点不对哈，前面nid

[ustbwuyi]

w75251455(砍破) (
的可以，非常感谢

[子陌红尘]

这些数据原来在表中是怎样？

[ustbwuyi]

w75251455(砍破) ( ) 信誉：100 Blog 加为好友 2007-7-5 15:34:06 得分: 0



我晕~~~如果这次还比钻钻拿的分少~~我就要和钻钻拼命了......

满足你，哈哈

[paoluo]

ustbwuyi() ( ) 信誉：100 Blog 加为好友 2007-07-06 15:36:47 得分: 0

有点不对哈，前面nid
------------------


你的原數據是怎樣的？你運行的語句是怎樣的？

[ustbwuyi]

哦，已经可以了，多谢libin_ftsafe(子陌红尘：TS for Banking Card)

[ustbwuyi]

下班结贴