求查询数据状态的sql
orderno fnumber qty outqty
A0001 0301 20 20
A0001 0302 20 10
A0001 0303 50 50 订单A0001查询结果为部分发货
A0002 0301 30 0
A0002 0302 30 0 订单A0002查询结果为未发货
A0003 0301 20 20
A0003 0302 30 30
A0003 0303 30 0 订单A0002查询结果为部分发货
A0004 0301 20 20
A0004 0302 30 30
A0004 0302 30 30
A0004 0304 30 30 订单A0002查询结果为全部发货
如何得到如下的查询结果：
A0001 部分发货
A0002 未发货
A0003 部分发货
A0004 全部发货
[解决办法]
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-04-22 13:15:37
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([rderno] varchar(5),[fnumber] varchar(4),[qty] int,[outqty] int)
insert [huang]
select 'A0001','0301',20,20 union all
select 'A0001','0302',20,10 union all
select 'A0001','0303',50,50 union all
select 'A0002','0301',30,0 union all
select 'A0002','0302',30,0 union all
select 'A0003','0301',20,20 union all
select 'A0003','0302',30,30 union all
select 'A0003','0303',30,0 union all
select 'A0004','0301',20,20 union all
select 'A0004','0302',30,30 union all
select 'A0004','0302',30,30 union all
select 'A0004','0304',30,30
--------------开始查询--------------------------
SELECT a.rderno,CASE WHEN a.状态=0 THEN '未发货' WHEN a.状态=b.countrderno THEN '全部发货' ELSE '部分发货' END [发货状态]
FROM (
SELECT rderno,SUM([状态])[状态]
FROM (
SELECT rderno,[fnumber],CASE WHEN [outqty]=0 THEN 0 WHEN [qty]=[outqty] AND [outqty]<>0 THEN 1 ELSE -1 END [状态]
FROM huang)a
GROUP BY rderno
)a LEFT JOIN (SELECT rderno,COUNT(rderno) [countrderno] FROM huang GROUP BY rderno)b ON a.rderno=b.rderno
----------------结果----------------------------
/*
rderno 发货状态
------ --------
A0001 部分发货
A0002 未发货
A0003 部分发货
A0004 全部发货
*/
--用具体行数据进行验证
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([rderno] varchar(5),[fnumber] varchar(4),[qty] int,[outqty] int)
insert [huang]
select 'A0001','0301',20,20 union all
select 'A0001','0302',20,10 union all
select 'A0001','0303',50,50 union all
select 'A0002','0301',30,0 union all
select 'A0002','0302',30,0 union all
select 'A0003','0301',20,20 union all
select 'A0003','0302',30,30 union all
select 'A0003','0303',30,0 union all
select 'A0004','0301',20,20 union all
select 'A0004','0302',30,30 union all
select 'A0004','0302',30,30 union all
select 'A0004','0304',30,30
select a.[rderno],isnull(isnull(cast(c.[Status] as varchar(32)),b.[Status]),a.[Status]) as [Status]--,*
from
(
select distinct [rderno],'全部发货' as Status from [huang]
) a
left join
(
select distinct [rderno],'部分发货' as Status from [huang]
where [qty]>[outqty]
) b on a.[rderno]=b.[rderno]
left join
(
select [rderno],'未发货' as Status from [huang]
group by [rderno]
having Sum([outqty])=0
) c on a.[rderno]=c.[rderno]
结果：
rdernoStatus
A0001部分发货
A0002未发货
A0003部分发货
A0004全部发货
