sql 数据汇总解决方案

sql 数据汇总
货号日期仓库数量件数颜色 换算率 标准条码号标准名称
b-02392012-5-193027505 150 69411131900010239 磨砂杯
g-02262012-5-183031010CS彩色柜93 1 69411131903600226 豪华三层整理柜
g-02262012-5-183031010蓝白柜96 1 69411131903600226 豪华三层整理柜
g-02262012-5-193034040黑白柜 1 69411131903600226 豪华三层整理柜
g-02262012-5-193036060蓝白柜96 1 69411131903600226 豪华三层整理柜
g-02272012-5-1730333黑白柜 1 69411131903770227 豪华四层整理柜
g-02272012-5-173102727黑白柜 1 69319035027250227 豪华四层整理柜
g-02272012-5-173103030黑白柜 1 69319035027250227 豪华四层整理柜
g-02272012-5-183031515CS彩色柜93 1 69411131903770227 豪华四层整理柜
g-02272012-5-183031515蓝白柜96 1 69411131903770227 豪华四层整理柜

怎样根据货号和颜色两列汇总数量和件数：
SELECT distinct c.cInvAddCode as 货号,  
  d.dDate as 日期,  
  a.cWhCode as 仓库, 
  a.iQuantity as 数量,  
  a.iNum as 件数,  
  a.cFree1 as 颜色 ,  
  a.iInvExchRate as 换算率,  
  c.cInvDefine1 as 标准条码,  
  c.cInvDefine5 as 标准名称
  FROM b,  
  a,  
  c,  
  d
  WHERE ( a.DLID = b.DLID ) and  
  ( b.cInvCode = c.cInvCode ) and  
  ( a.cDLCode = d.cDLCode )

[解决办法]

SQL code

with tas(SELECT distinct c.cInvAddCode as 货号,     d.dDate as 日期,     a.cWhCode as 仓库,    a.iQuantity as 数量,     a.iNum as 件数,     a.cFree1 as 颜色 ,     a.iInvExchRate as 换算率,     c.cInvDefine1 as 标准条码,     c.cInvDefine5 as 标准名称  FROM b,     a,     c,     d  WHERE ( a.DLID = b.DLID ) and     ( b.cInvCode = c.cInvCode ) and     ( a.cDLCode = d.cDLCode ))select [货号],[颜色],SUM([件数]) as [件数] from tgroup by [货号],[颜色]/*货号    颜色    件数b-0239    150    5g-0226    CS彩色柜93    10g-0227    CS彩色柜93    15g-0226    黑白柜    40g-0227    黑白柜    60g-0226    蓝白柜96    70g-0227    蓝白柜96    15*/--结果是要这样的吗
[解决办法]
SQL code
with cte as(    SELECT distinct       c.cInvAddCode ,   --as 货号      d.dDate ,            --as 日期      a.cWhCode ,        --as 仓库      a.iQuantity ,        --as 数量      a.iNum ,            --as 件数      a.cFree1  ,        --as 颜色      a.iInvExchRate ,  --as 换算率      c.cInvDefine1 ,    --as 标准条码         c.cInvDefine5        --as 标准名称      FROM b,         a,         c,         d      WHERE ( a.DLID = b.DLID ) and         ( b.cInvCode = c.cInvCode ) and         ( a.cDLCode = d.cDLCode )  )select     cInvAddCode,    cFree1,    SUM(iQuantity) as [总数量],    SUM(iNum) as [总件数]from cte group by cInvAddCode,cFree1