想到一个方法,但是要重复使用B表,能进一步优化吗select * from A表 a where not exists( select b.箱号 from B表 b where b.箱号 in ( select b1.箱号 from B表 b1 where b1.单号=a.单号 ) group by b.箱号 having count(b.箱号)>1)
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2014-06-05 22:18: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]([单号] varchar(3),[箱号] varchar(10)) insert [tb] select 'A01','CARTONA011' union all select 'A01','CARTONA012' union all select 'A01','CARTONA013' union all select 'A02','CARTONA021' union all select 'A02','CARTONA022' union all select 'A02','CARTONA023' union all select 'A03','CARTONA011' union all select 'A03','CARTONA031' union all select 'A03','CARTONA032' union all select 'A03','CARTONA033' union all select 'A04','CARTONA041' --------------开始查询--------------------------select * from [tb] t where exists(select 1 from tb where 单号<>t.单号 and 箱号=t.箱号) ----------------结果---------------------------- /* 单号 箱号 ---- ---------- A01 CARTONA011 A03 CARTONA011(2 行受影响) */
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2014-06-05 22:20:20 -- 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) -- ---------------------------------------------------------------- --> 测试数据:[A] if object_id('[A]') is not null drop table [A] go create table [A]([单号] varchar(3)) insert [A] select 'A01' union all select 'A02' union all select 'A03' union all select 'A04' --> 测试数据:[b] if object_id('[b]') is not null drop table [b] go create table [b]([单号] varchar(3),[箱号] varchar(10)) insert [b] select 'A01','CARTONA011' union all select 'A01','CARTONA012' union all select 'A01','CARTONA013' union all select 'A02','CARTONA021' union all select 'A02','CARTONA022' union all select 'A02','CARTONA023' union all select 'A03','CARTONA011' union all select 'A03','CARTONA031' union all select 'A03','CARTONA032' union all select 'A03','CARTONA033' union all select 'A04','CARTONA041' --------------开始查询-------------------------- select * from a where not exists(select 单号 from b t where exists(select 1 from b where 单号<>t.单号 and 箱号=t.箱号) and 单号=a.单号) ----------------结果---------------------------- /* 单号 ---- A02 A04(2 行受影响) */
不愧是版主 我自己走进死胡同里,外查询已经想到用 not exists ,怎么就没想到把内查询的in改用exists
select b.箱号 from B表 b where b.箱号 in
( select b1.箱号 from B表 b1 where b1.单号=a.单号 )
group by b.箱号
having count(b.箱号)>1)
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-06-05 22:18: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]([单号] varchar(3),[箱号] varchar(10))
insert [tb]
select 'A01','CARTONA011' union all
select 'A01','CARTONA012' union all
select 'A01','CARTONA013' union all
select 'A02','CARTONA021' union all
select 'A02','CARTONA022' union all
select 'A02','CARTONA023' union all
select 'A03','CARTONA011' union all
select 'A03','CARTONA031' union all
select 'A03','CARTONA032' union all
select 'A03','CARTONA033' union all
select 'A04','CARTONA041'
--------------开始查询--------------------------select * from [tb] t where exists(select 1 from tb where 单号<>t.单号 and 箱号=t.箱号)
----------------结果----------------------------
/* 单号 箱号
---- ----------
A01 CARTONA011
A03 CARTONA011(2 行受影响)
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-06-05 22:20:20
-- 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)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([单号] varchar(3))
insert [A]
select 'A01' union all
select 'A02' union all
select 'A03' union all
select 'A04'
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([单号] varchar(3),[箱号] varchar(10))
insert [b]
select 'A01','CARTONA011' union all
select 'A01','CARTONA012' union all
select 'A01','CARTONA013' union all
select 'A02','CARTONA021' union all
select 'A02','CARTONA022' union all
select 'A02','CARTONA023' union all
select 'A03','CARTONA011' union all
select 'A03','CARTONA031' union all
select 'A03','CARTONA032' union all
select 'A03','CARTONA033' union all
select 'A04','CARTONA041'
--------------开始查询--------------------------
select * from a where not exists(select 单号 from b t where exists(select 1 from b where 单号<>t.单号 and 箱号=t.箱号) and 单号=a.单号)
----------------结果----------------------------
/* 单号
----
A02
A04(2 行受影响)
*/
不愧是版主
我自己走进死胡同里,外查询已经想到用 not exists ,怎么就没想到把内查询的in改用exists