34,590
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :SQL77(只为思齐老)
-- Date :2010-03-10 18:12:33
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([JZNUM] int,[QDNUM] int,[ALM] int)
insert #TB
select 3,2,0 union all
select 3,3,1 union all
select 4,1,0 union all
select 5,1,0
--------------开始查询--------------------------
select * from #TB T WHERE QDNUM=(SELECT MIN(QDNUM) FROM #TB WHERE JZNUM=T.JZNUM)
----------------结果----------------------------
/*
(所影响的行数为 4 行)
JZNUM QDNUM ALM
----------- ----------- -----------
3 2 0
4 1 0
5 1 0
(所影响的行数为 3 行)
*/
select JZNUM , QDNUM, ALM from (
select *,count(*) over(partition by JZNUM) as num from
) a
where num=1
select *
from tb t
where not exists(select 1 from tb where JZNUM=t.JZNUM and QDNUM<t.QDNUM)