判断库存是数量是否OK,这样写怎么不行那.???
if exists(select 1 from
(select skucode,ordcom,skupch,sum(qty) qty from ord where ordno='ZQAL201310280001' group by skucode,ordcom,skupch ) t1,
(select skucode,recname,skupch,sum(skuqty) qty from sellord group by skucode,recname,skupch ) t2
where t1.skucode = t2.skucode and t1.ordcom=t2.recname and t1.skupch=t2.skupch
and t1.qty > t2.qty)
begin
print '库存不足'
end
else
begin
print '库存OK'
end
/*库存OK*/
以下是数据!
select skucode,ordcom,skupch,sum(qty) qty from ord where ordno='ZQAL201310280001' group by skucode,ordcom,skupch
/*skucodeordcomskupchqty
185469 科技有限公司31
570798 科技有限公司SA9316DW5*/
select skucode,recname,skupch,sum(skuqty) qty from sellord group by skucode,recname,skupch
/*skucoderecnameskupchqty
185469科技有限公司277
185470科技有限公司60
570311科技有限公司150
571818科技有限公司1500
982762科技有限公司SA631024471
982762科技有限公司SA631024GB2
982762科技有限公司SA631024GH1*/
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-10-28 10:21:44
-- 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: )
--
----------------------------------------------------------------
--> 测试数据：[sellord]
if object_id('[sellord]') is not null drop table [sellord]
go
create table [sellord]([skucode] int,[recname] varchar(12),[skupch] varchar(10),[qty] int)
insert [sellord]
select 185469,'科技有限公司','277',null union all
select 185470,'科技有限公司','60',null union all
select 570311,'科技有限公司','150',null union all
select 571818,'科技有限公司','1500',null union all
select 982762,'科技有限公司','SA63102447',1 union all
select 982762,'科技有限公司','SA631024GB',2 union all
select 982762,'科技有限公司','SA631024GH',1
--> 测试数据：[ord]
if object_id('[ord]') is not null drop table [ord]
go
create table [ord]([skucode] int,[ordcom] varchar(12),[skupch] varchar(8),[qty] int)
insert [ord]
select 185469,'科技有限公司','31',null union all
select 570798,'科技有限公司','SA9316DW',5
--------------开始查询--------------------------
IF EXISTS (
select 1 from (SELECT skucode,recname ,SUM([qty])[qty] FROM [sellord] GROUP BY skucode,recname)a
WHERE not EXISTS (SELECT 1 FROM (SELECT skucode,ordcom,SUM([qty])[qty] FROM [ord] GROUP BY skucode,ordcom) b WHERE a.skucode=b.skucode AND a.recname=b.ordcom AND a.qty>b.qty))
BEGIN
PRINT '库存不足'
END
ELSE
BEGIN
PRINT '库存足'
END
----------------结果----------------------------
/*
库存不足
*/
----------------------------------------------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2013-10-28 10:48:55
-- Verstion:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
--Feb 10 2012 19:39:15
--Copyright (c) Microsoft Corporation
--Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[发货表]
if object_id('[发货表]') is not null drop table [发货表]
go
create table [发货表]([skucode] int,[ordcom] varchar(12),[skupch] varchar(8),[qty] int)
insert [发货表]
select 185469,'科技有限公司',NULL,31 union all
select 570798,'科技有限公司','SA9316DW',5
--> 测试数据：[库存表]
if object_id('[库存表]') is not null drop table [库存表]
go
create table [库存表]([skucode] int,[recname] varchar(12),[skupch] varchar(10),[qty] int)
insert [库存表]
select 185469,'科技有限公司',NULL,277 union all
select 185470,'科技有限公司',NULL,60 union all
select 570311,'科技有限公司',NULL,150 union all
select 571818,'科技有限公司',NULL,1500 union all
select 982762,'科技有限公司','SA63102447',1 union all
select 982762,'科技有限公司','SA631024GB',2 union all
select 982762,'科技有限公司','SA631024GH',1
--------------开始查询--------------------------
IF EXISTS(SELECT 1 FROM 发货表 a WHERE NOT EXISTS(SELECT 1 FROM 库存表 WHERE skucode=a.skucode))
PRINT '库存不足'
ELSE IF EXISTS(
SELECT
1
FROM
(
SELECT skucode,ordcom,skupch,SUM(qty) AS qty FROM 发货表 GROUP BY skucode,ordcom,skupch
)a
LEFT JOIN
(
SELECT skucode,recname,skupch,SUM(qty) AS qty FROM 库存表 GROUP BY skucode,recname,skupch
)b
ON
a.skucode = b.skucode and a.ordcom=b.recname and a.skupch=b.skupch
WHERE
a.qty>b.[qty]
)
PRINT '库存不足'
ELSE
PRINT '库存OK'
----------------结果----------------------------
/*
库存不足
*/
