MSSQL2000表转为视图重复值的解决方案

MSSQL2000表转为视图重复值的
MSSQL2000表转为视图重复值的
表S_ORDER 主表
单据ID, 客户
BILLID, CLIENTID 
1001 , A01
1002 , K01
1003 , B03
1004 , H08
1005 , A01

表S_ORDERD 从表
单据ID, 货品ID, 数量
BILLID, GOODSID , QTY 
1001 , 9001 , 30
1001 , 9003 , 70
1002 , 9001 , 80
1003 , 9001 , 300
1004 , 9988 , 700
1005 , 9001 , 600

主表和从表的关系:S_ORDER.BILLID=S_ORDERD.BILLID

要求做成视图，结果如下：
(取相同GOODSID的所有QTY数量合计，再取其中一个CLIENTID,如果能做到哪个CLIENTID的QTY合计数量最多就取哪个客户就更好)
客户 , 货品 , 数量合计
CLIENTID , GOODSID , QTY
A01 , 9001 , 1010
A01 , 9003 , 70
H08 , 9988 , 700




[解决办法]
--这样的?

SQL code

select t1.* from (select m.CLIENTID , n.GOODSID , sum(n.QTY) qtyfrom S_ORDER m , S_ORDERD nwhere m.BILLID = n.BILLIDgroup by m.CLIENTID , n.GOODSID) t1 where t1.qty = (select max(qty) from (select m.CLIENTID , n.GOODSID , sum(n.QTY) qtyfrom S_ORDER m , S_ORDERD nwhere m.BILLID = n.BILLIDgroup by m.CLIENTID , n.GOODSID) t2 where t1.GOODSID = t2.GOODSID)
[解决办法]
SQL code
------------------------------ Author  :fredrickhu(小F，向高手学习)-- Date    :2011-11-30 15:39:05-- Version:--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) --    Apr 22 2011 11:57:00 --    Copyright (c) Microsoft Corporation--    Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)--------------------------------> 测试数据：[S_ORDER]if object_id('[S_ORDER]') is not null drop table [S_ORDER]go create table [S_ORDER]([BILLID] varchar(6),[CLIENTID] varchar(8))insert [S_ORDER]select '1001','A01' union allselect '1002','K01' union allselect '1003','B03' union allselect '1004','H08' union allselect '1005','A01'--> 测试数据：[S_ORDERD]if object_id('[S_ORDERD]') is not null drop table [S_ORDERD]go create table [S_ORDERD]([BILLID] int,[GOODSID] int,[QTY] int)insert [S_ORDERD]select 1001,9001,30 union allselect 1001,9003,70 union allselect 1002,9001,80 union allselect 1003,9001,300 union allselect 1004,9988,700 union allselect 1005,9001,600--------------开始查询--------------------------select   *into #tbfrom(select    a.CLIENTID,b.GOODSID,b.qtyfrom   S_ORDER a,   (select BILLID,GOODSID,(select SUM(qty) from [S_ORDERD] where GOODSID=t.GOODSID) as qty from [S_ORDERD] t)bwhere   a.BILLID=b.BILLID)t    select distinct * from #tb t where qty=(select MAX(qty) from #tb where CLIENTID=t.CLIENTID)   drop table #tb ----------------结果----------------------------/* CLIENTID GOODSID     qty-------- ----------- -----------A01      9001        1010B03      9001        1010H08      9988        700K01      9001        1010(4 行受影响)*/
[解决办法]
SQL code
use Tempdbgo--> -->  if not object_id(N'Tempdb..#T1') is null    drop table #T1GoCreate table #T1([BILLID] int,[CLIENTID] nvarchar(3))Insert #T1select 1001,N'A01' union allselect 1002,N'K01' union allselect 1003,N'B03' union allselect 1004,N'H08' union allselect 1005,N'A01'Go if not object_id(N'Tempdb..#T2') is null    drop table #T2GoCreate table #T2([BILLID] int,[GOODSID] int,[QTY] int)Insert #T2select 1001,'9001',30 union allselect 1001,'9003',70 union allselect 1002,'9001',80 union allselect 1003,'9001',300 union allselect 1004,'9988',700 union allselect 1005,'9001',600GoSELECT a.[CLIENTID],a.[GOODSID],b.[QTY]FROM  (Select b.[BILLID],b.[CLIENTID],a.[GOODSID] ,SUM(a.[QTY]) AS [QTY] FROM #T2 AS a  INNER JOIN #T1 AS b  ON a.BILLID=b.[BILLID] GROUP BY b.[CLIENTID],a.[GOODSID],b.[BILLID]) AS a     INNER JOIN (SELECT [GOODSID],SUM([QTY]) AS [QTY] FROM #T2  GROUP BY [GOODSID]) AS b ON a.GOODSID=b.GOODSIDWHERE NOT EXISTS(SELECT 1 FROM  (Select b.[BILLID],b.[CLIENTID],a.[GOODSID] ,SUM(a.[QTY]) AS [QTY] FROM #T2 AS a  INNER JOIN #T1 AS b  ON a.BILLID=b.[BILLID] GROUP BY b.[BILLID],b.[CLIENTID],a.[GOODSID],b.[BILLID])  AS xWHERE [GOODSID]=a.[GOODSID] AND ([QTY]>a.[QTY] OR ([QTY]=a.[QTY]  AND [BILLID]>a.[BILLID])))/*CLIENTID    GOODSID    QTYA01    9001    1010A01    9003    70H08    9988    700*/