if object_id('[tbA]') is not null drop table [tbA] create table [tbA]([A] int,[B] int,[数量] int) insert [tbA] select 1,1,1000 union all select 2,2,2000if object_id('[tbB]') is not null drop table [tbB] create table [tbB]([A] int,[B] int,[数量] int,[区分] int) insert [tbB] select 1,1,1000,1 union all select 2,2,2000,1 union all select 3,3,4000,1 union all select 4,4,5000,0select * from [tbA] select * from [tbB] SELECT * FROM TbB WHERE NOT EXISTS ( SELECT 1 FROM TBa WHERE tba.a = tba.a AND tbb.b = tba.b AND tbb.数量 = tba.数量 ) AND 区分 = 1 /* A B 数量 区分 3 3 4000 1*/
这样写也OK,看lz的sql server 版本了。SELECT a,b,数量,区分 FROM dbo.tbB WHERE 区分=1 EXCEPT SELECT a,b,数量,1 AS 区分 FROM dbo.tba /* a b 数量 区分 3 3 4000 1*/
---------------------------------------------------------------- -- Author :DBA_Huangzj(發糞塗牆) -- Date :2014-01-08 12:43:52 -- Version: -- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) -- Dec 28 2012 20:23:12 -- Copyright (c) Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) -- ---------------------------------------------------------------- --> 测试数据:[tbA] if object_id('[tbA]') is not null drop table [tbA] go create table [tbA]([A] int,[B] int,[数量] int,[产品] varchar(2)) insert [tbA] select 1,1,1000,'A1' union all select 2,2,2000,'B1' --> 测试数据:[tbB] if object_id('[tbB]') is not null drop table [tbB] go create table [tbB]([A] int,[B] int,[数量] int,[区分] int,[产品] varchar(2)) insert [tbB] select 1,1,1000,1,'A1' union all select 2,2,2000,1,'B1' union ALL select 3,3,4000,1,'C1' union all select 4,4,5000,0,'C1' union all select 5,5,5000,0,'D1' --------------开始查询--------------------------select * from (SELECT * FROM [tbB] WHERE [区分]=1) b WHERE NOT EXISTS (SELECT 1 FROM tba a WHERE a.a=b.a AND a.b=b.b)----------------结果---------------------------- /* A B 数量 区分 产品 ----------- ----------- ----------- ----------- ---- 3 3 4000 1 C1 */
create table [tbA]([A] int,[B] int,[数量] int)
insert [tbA]
select 1,1,1000 union all
select 2,2,2000if object_id('[tbB]') is not null drop table [tbB]
create table [tbB]([A] int,[B] int,[数量] int,[区分] int)
insert [tbB]
select 1,1,1000,1 union all
select 2,2,2000,1 union all
select 3,3,4000,1 union all
select 4,4,5000,0select * from [tbA]
select * from [tbB]
SELECT *
FROM TbB
WHERE NOT EXISTS ( SELECT 1
FROM TBa
WHERE tba.a = tba.a
AND tbb.b = tba.b
AND tbb.数量 = tba.数量 )
AND 区分 = 1
/*
A B 数量 区分
3 3 4000 1*/
这样写也OK,看lz的sql server 版本了。SELECT a,b,数量,区分 FROM dbo.tbB WHERE 区分=1
EXCEPT
SELECT a,b,数量,1 AS 区分 FROM dbo.tba
/*
a b 数量 区分
3 3 4000 1*/
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-01-08 12:43:52
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[tbA]
if object_id('[tbA]') is not null drop table [tbA]
go
create table [tbA]([A] int,[B] int,[数量] int,[产品] varchar(2))
insert [tbA]
select 1,1,1000,'A1' union all
select 2,2,2000,'B1'
--> 测试数据:[tbB]
if object_id('[tbB]') is not null drop table [tbB]
go
create table [tbB]([A] int,[B] int,[数量] int,[区分] int,[产品] varchar(2))
insert [tbB]
select 1,1,1000,1,'A1' union all
select 2,2,2000,1,'B1' union ALL
select 3,3,4000,1,'C1' union all
select 4,4,5000,0,'C1' union all
select 5,5,5000,0,'D1'
--------------开始查询--------------------------select * from (SELECT * FROM [tbB] WHERE [区分]=1) b
WHERE NOT EXISTS (SELECT 1 FROM tba a WHERE a.a=b.a AND a.b=b.b)----------------结果----------------------------
/*
A B 数量 区分 产品
----------- ----------- ----------- ----------- ----
3 3 4000 1 C1
*/