货号 日期 仓库 数量 件数 颜色 换算率 标准条码号 标准名称
b-0239 2012-5-19 302 750 5 150 6941113190001 0239 磨砂杯
g-0226 2012-5-18 303 10 10 CS彩色柜93 1 6941113190360 0226 豪华三层整理柜
g-0226 2012-5-18 303 10 10 蓝白柜96 1 6941113190360 0226 豪华三层整理柜
g-0226 2012-5-19 303 40 40 黑白柜 1 6941113190360 0226 豪华三层整理柜
g-0226 2012-5-19 303 60 60 蓝白柜96 1 6941113190360 0226 豪华三层整理柜
g-0227 2012-5-17 303 3 3 黑白柜 1 6941113190377 0227 豪华四层整理柜
g-0227 2012-5-17 310 27 27 黑白柜 1 6931903502725 0227 豪华四层整理柜
g-0227 2012-5-17 310 30 30 黑白柜 1 6931903502725 0227 豪华四层整理柜
g-0227 2012-5-18 303 15 15 CS彩色柜93 1 6941113190377 0227 豪华四层整理柜
g-0227 2012-5-18 303 15 15 蓝白柜96 1 6941113190377 0227 豪华四层整理柜
怎样根据货号和颜色两列汇总数量和件数：
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