---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2014-06-30 14:09:10 -- Version: -- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86) -- Feb 10 2012 19:13:17 -- Copyright (c) Microsoft Corporation -- Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([字段1] varchar(1),[字段2] varchar(2),[字段3] int) insert [tb] select 'A','AA',16 union all select 'A','AB',12 union all select 'B','AC',10 union all select 'C','AD',26 union all select 'C','AE',26 union all select 'D','AA',90 --------------开始查询--------------------------select * from [tb] as t where not exists(select 1 from tb where 字段1=t.字段1 and 字段3>t.字段3 or (字段1=t.字段1 and 字段3=t.字段3 and 字段2>t.字段2)) ----------------结果---------------------------- /* 字段1 字段2 字段3 ---- ---- ----------- A AA 16 B AC 10 C AE 26 D AA 90(4 行受影响) */
谢谢,可以了,如果 字段1 字段2 字段3 A AA 16 A AB 12 B AC 10 C AD 26 C AE 26 C AE 26 D AA 90如何返回: 字段1 字段2 字段3 A AA 16 B AC 10 C AD 26 D AA 90如何修改?
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-06-30 14:09:10
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
-- Feb 10 2012 19:13:17
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([字段1] varchar(1),[字段2] varchar(2),[字段3] int)
insert [tb]
select 'A','AA',16 union all
select 'A','AB',12 union all
select 'B','AC',10 union all
select 'C','AD',26 union all
select 'C','AE',26 union all
select 'D','AA',90
--------------开始查询--------------------------select * from [tb] as t where not exists(select 1 from tb where 字段1=t.字段1 and 字段3>t.字段3 or (字段1=t.字段1 and 字段3=t.字段3 and 字段2>t.字段2))
----------------结果----------------------------
/* 字段1 字段2 字段3
---- ---- -----------
A AA 16
B AC 10
C AE 26
D AA 90(4 行受影响)
*/
字段1 字段2 字段3
A AA 16
A AB 12
B AC 10
C AD 26
C AE 26
C AE 26
D AA 90如何返回:
字段1 字段2 字段3
A AA 16
B AC 10
C AD 26
D AA 90如何修改?