SQL 2000 SP4
现在如下表内容。
怎样用SELECT 语句查询出billid字段不重复的值，不能改变现有的排序规则！！
id billid billorder citebillid
1, 667, 122855, 54
2, 667, 122855, 53
3, 653, 122839, 52
4, 659, 122845, 51
5, 664, 122850, 50
6, 665, 122851, 50
7, 656, 122842, 49
8, 654, 122840, NULL
9, 662, 122848, NULL
要想得到的查询的结果如下
billid billorder
667, 122855
653, 122839
659, 122845
664, 122850
665, 122851
656, 122842
654, 122840
662, 122848
sql
------解决方案--------------------------------------------------------
Select * from ta a
where not exists(select 1 from ta where billid = a.billid and id < a.id)
order by id
------解决方案--------------------------------------------------------
if object_id(N'tb',N'U') is not null
drop table tb
create table tb(id int,billid varchar(4),billorder varchar(10),citebillid varchar(4))
insert into tb(id,billid,billorder,citebillid)
select 1,'667','122855','54' union all
select 2,'667','122855','53' union all
select 3,'653','122839','52' union all
select 4,'659','122845','51' union all
select 5,'664','122850','50' union all
select 6,'665','122851','50' union all
select 7,'656','122842','49' union all
select 8,'654','122840',NULL union all
select 9,'662','122848', NULL
select billid,billorder
from tb a with(nolock)
where not exists(
select top(1)1 from tb b where b.billid=a.billid and b.id < a.id)
order by a.id
billid billorder
------ ----------
667 122855
653 122839
659 122845
664 122850
665 122851
656 122842
654 122840
662 122848
(8 行受影响)
------解决方案--------------------------------------------------------
select billid,billorder from tb as a where id in
(select max(id) from tb group by billid)