Access中排序问题，急啊
select * from tt order by code,fdate,groupcode,ingroupcode
得到查询结果集
code（代号） fdate（日期） GroupCode（组号） InGroupCode（组内号）
1000 2007-04-08 1 1
1000 2007-04-08 1 2
1000 2007-04-08 2 1
1000 2007-04-08 2 2
1000 2007-04-08 3 1
1000 2007-04-09 1 1
1001 2007-04-08 1 1
1001 2007-04-08 1 2
1001 2007-04-08 2 1
1001 2007-04-08 2 2
1001 2007-04-08 3 1
1001 2007-04-09 1 1
现在我需要增加一列查询排序列
实现结果如下：
code（代号） fdate（日期） GroupCode（组号） InGroupCode（组内号） 排序号
1000 2007-04-08 1 1 1
1000 2007-04-08 1 2 2
1000 2007-04-08 2 1 3
1000 2007-04-08 2 2 4
1000 2007-04-08 3 1 5
1000 2007-04-09 1 1 6
1001 2007-04-08 1 1 1
1001 2007-04-08 1 2 2
1001 2007-04-08 2 1 3
1001 2007-04-08 3 1 4
1001 2007-04-09 1 1 5
请问在access中sql语句如何写？
解决利马结贴
[解决办法]
Select *,(Select count(*) from t where code=x.code
and fdate <x.fdate and groupcode=x.groupcode
and InGroupcode=x.InGroupCode)+1 as 排序号
from t as x order by code,fdate,groupcode,ingroupcode
[解决办法]
--改一下
Select *,(Select sum(iif( (fdate=x.fdate and groupcode=x.groupcode and
InGroupCode <x.InGroupCode) or
(fdate=x.fdate and groupcode <x.groupcode ) or
(fdate <x.fdate) , 1,0) ) from t
Where code=x.code and fdate <=x.fdate )+1 as 排序号
from t as x
[解决办法]
--着是SQL的语句,不知道在access中能否使用?
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb(code varchar(10),fdate datetime,GroupCode varchar(10),InGroupCode varchar(10))
insert into tb(code,fdate,GroupCode,InGroupCode) values( '1000 ', '2007-04-08 ', '1 ', '1 ')
insert into tb(code,fdate,GroupCode,InGroupCode) values( '1000 ', '2007-04-08 ', '1 ', '2 ')
insert into tb(code,fdate,GroupCode,InGroupCode) values( '1000 ', '2007-04-08 ', '2 ', '1 ')
insert into tb(code,fdate,GroupCode,InGroupCode) values( '1000 ', '2007-04-08 ', '2 ', '2 ')
insert into tb(code,fdate,GroupCode,InGroupCode) values( '1000 ', '2007-04-08 ', '3 ', '1 ')
insert into tb(code,fdate,GroupCode,InGroupCode) values( '1000 ', '2007-04-09 ', '1 ', '1 ')
insert into tb(code,fdate,GroupCode,InGroupCode) values( '1001 ', '2007-04-08 ', '1 ', '1 ')
insert into tb(code,fdate,GroupCode,InGroupCode) values( '1001 ', '2007-04-08 ', '1 ', '2 ')
insert into tb(code,fdate,GroupCode,InGroupCode) values( '1001 ', '2007-04-08 ', '2 ', '1 ')
insert into tb(code,fdate,GroupCode,InGroupCode) values( '1001 ', '2007-04-08 ', '2 ', '2 ')
insert into tb(code,fdate,GroupCode,InGroupCode) values( '1001 ', '2007-04-08 ', '3 ', '1 ')
insert into tb(code,fdate,GroupCode,InGroupCode) values( '1001 ', '2007-04-09 ', '1 ', '1 ')
go
select code,convert(varchar(10),fdate,120) fdate,GroupCode,InGroupCode , 排序号=(select count(1) from tb
where
(code=a.code and fdate=a.fdate and groupcode=a.groupcode and InGroupCode <a.InGroupCode) or
(code=a.code and fdate=a.fdate and groupcode <a.groupcode) or
(code=a.code and fdate <a.fdate)
)+1
from tb a
drop table tb
/*
code fdate GroupCode InGroupCode 排序号
---------- ---------- ---------- ----------- -----------
1000 2007-04-08 1 1 1
1000 2007-04-08 1 2 2
1000 2007-04-08 2 1 3
1000 2007-04-08 2 2 4
1000 2007-04-08 3 1 5
1000 2007-04-09 1 1 6
1001 2007-04-08 1 1 1
1001 2007-04-08 1 2 2
1001 2007-04-08 2 1 3
1001 2007-04-08 2 2 4
1001 2007-04-08 3 1 5
1001 2007-04-09 1 1 6
（所影响的行数为 12 行）
*/
[解决办法]
学习了 ：）
[解决办法]
--Access使用的是Jet-SQL，可以使用子查询：
--Access中的查询SQL语句：
select
code,
fdate,
GroupCode,
InGroupCode ,
(
select count(*)
from tt
where (code=a.code and fdate=a.fdate and groupcode=a.groupcode and InGroupCode <a.InGroupCode)
or (code=a.code and fdate=a.fdate and groupcode <a.groupcode)
or (code=a.code and fdate <a.fdate)
) + 1 as 排序号
from tt as a
