--------------------------------- -- Author: htl258(Tony) -- Date : 2009-07-10 11:16:46 --------------------------------- --> 生成测试数据表:tbIf not object_id('[tb]') is null Drop table [tb] Go Create table [tb]([编号] nvarchar(1),[标志] int) Insert tb Select 'A',0 union all Select 'A',1 union all Select 'A',2 union all Select 'A',0 union all Select 'B',1 union all Select 'B',1 union all Select 'C',2 union all Select 'C',2 union all Select 'C',2 Go --Select * from tb-->SQL查询如下: select distinct * from tb t where not exists(select 1 from tb where [编号]=t.[编号] and [标志]<t.[标志]) /* 编号 标志 ---- ----------- A 0 B 1 C 2(3 行受影响) */
--> 生成测试数据表:tb If not object_id('[tb]') is null Drop table [tb] Go Create table [tb]([编号] nvarchar(1),[标志] int) Insert tb Select 'A',0 union all Select 'A',1 union all Select 'A',2 union all Select 'A',0 union all Select 'B',1 union all Select 'B',1 union all Select 'C',2 union all Select 'C',2 union all Select 'C',2 Go-->测试SQL SELECT [编号],MIN([标志]) 标志 FROM tb GROUP BY [编号]-->测试结果 /* 编号 标志 ---- ----------- A 0 B 1 C 2(3 行受影响) */
select distinct * from tb t where not exists(select 1 from tb where [编号]=t.[编号] and [标志]<t.[标志]
declare @a table (编号 nvarchar(10),标志 int) insert into @a select 'A',0 union all select 'A',1 union all select 'A',2 union all select 'A',0 union all select 'B',1 union all select 'B',1 union all select 'C',2 union all select 'C',2 union all select 'C',2select * from ( select distinct * from @a ) a where not exists (select 1 from (select distinct * from @a) b where a.编号=b.编号 and b.标志<a.标志) (9 行受影响) 编号 标志 ---------- ----------- A 0 B 1 C 2(3 行受影响)
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-10 11:16:46
---------------------------------
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([编号] nvarchar(1),[标志] int)
Insert tb
Select 'A',0 union all
Select 'A',1 union all
Select 'A',2 union all
Select 'A',0 union all
Select 'B',1 union all
Select 'B',1 union all
Select 'C',2 union all
Select 'C',2 union all
Select 'C',2
Go
--Select * from tb-->SQL查询如下:
select distinct * from tb t where not exists(select 1 from tb where [编号]=t.[编号] and [标志]<t.[标志])
/*
编号 标志
---- -----------
A 0
B 1
C 2(3 行受影响)
*/
--> 生成测试数据表:tb
If not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([编号] nvarchar(1),[标志] int)
Insert tb
Select 'A',0 union all
Select 'A',1 union all
Select 'A',2 union all
Select 'A',0 union all
Select 'B',1 union all
Select 'B',1 union all
Select 'C',2 union all
Select 'C',2 union all
Select 'C',2
Go-->测试SQL
SELECT [编号],MIN([标志]) 标志
FROM tb
GROUP BY [编号]-->测试结果
/*
编号 标志
---- -----------
A 0
B 1
C 2(3 行受影响)
*/
insert into @a select 'A',0
union all select 'A',1
union all select 'A',2
union all select 'A',0
union all select 'B',1
union all select 'B',1
union all select 'C',2
union all select 'C',2
union all select 'C',2select * from (
select distinct * from @a ) a where not exists (select 1 from (select distinct * from @a) b where a.编号=b.编号 and b.标志<a.标志) (9 行受影响)
编号 标志
---------- -----------
A 0
B 1
C 2(3 行受影响)