求查询数据状态的sql,该如何解决
求查询数据状态的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 全部发货
------解决方案--------------------
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 [状态]