34,593
社区成员
发帖
与我相关
我的任务
分享
if object_id('[价格表]') is not null drop table [价格表]
go
create table [价格表]([物料ID] varchar(1),[客户ID] int,[销售单价] numeric(3,1))
insert [价格表]
select 'A',10001,1.2 union all
select 'A',10001,1.4 union all
select 'A',10001,1.5 union all
select 'B',20001,20 union all
select 'B',20001,22 union all
select 'B',20001,25
go
if object_id('[销售订单表]') is not null drop table [销售订单表]
go
create table [销售订单表]([订单号] varchar(5),[客户ID] int,[物料ID] varchar(1),[物料数量] int,[物料单价] numeric(3,1))
insert [销售订单表]
select 'S0001',10001,'A',2000,1.2 union all
select 'S0001',10001,'A',1000,1.8 union all
select 'S0001',20001,'B',1000,22 union all
select 'S0001',20001,'B',3000,25 union all
select 'S0001',20001,'C',2000,25 --为测试增加的记录
go
-->查询:
select * from 销售订单表 t
where not exists(select 1 from 价格表 where 物料ID=t.物料ID and 销售单价=t.物料单价)
/**
订单号 客户ID 物料ID 物料数量 物料单价
----- ----------- ---- ----------- ---------------------------------------
S0001 10001 A 1000 1.8
S0001 20001 C 2000 25.0
(2 行受影响)
**/
SELECT *
FROM 销售订单表 a
WHERE NOT EXISTS (SELECT *
FROM 价格表 b
WHERE b.物料id = a.物料id
AND b.客户id = a.客户id
AND b.销售单价 = a.物料单价)
select * from 销售订单表 x left outer join 价格表 j on x.物料ID = j.物料ID
where j.物料id is null or j.销售单价<>x.物料单价
select * from 销售订单表 a where not exists(select * from 价格表 where
物料ID = a.物料ID and 客户ID = a.客户ID and 销售单价 = a.物料单价
)