62,047
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([Account] int,[dst] varchar(2))
insert [TB]
select 123,'AB' union all
select 12345,'AA' union all
select 123,'AA' union all
select 789,'CD'
select * from [TB] t where not exists(select 1 from TB where t.Account=Account and t.dst>dst)
/*
Account dst
----------- ----
12345 AA
123 AA
789 CD
(所影响的行数为 3 行)
*/
drop table TB
/*
-- Author:SQL77--RICHIE
-- Version:V1.001 Date:2008-05-15--转Flystone
*/
-- Test Data: TB
If object_id('TB') is not null
Drop table TB
Go
Create table TB(Account int,dst varchar(2))
Go
Insert into TB
select 123,'AB' union all
select 12345,'AA' union all
select 123,'AA' union all
select 789,'CD'
Go
--Start
SELECT * FROM TB T WHERE EXISTS(SELECT 1 FROM TB WHERE [DST]=T.[DST] AND Account <>T.Account )
--Result:
/*
(所影响的行数为 4 行)
Account dst
----------- ----
12345 AA
123 AA
(所影响的行数为 2 行)
*/
--End
SEELCT * FROM TB T WHERE EXISTS(SELECT 1 FROM TB WHERE [DST]=T.[DST] AND Account <>T.Account )
select distinct account ,dst from @t where dst in(
select dst from @t group by dst having Count( distinct(Account)) >1)