用游标不好,速度慢.在外面再套一层查询。SELECT C1,C2,C3,C4,C5, SUM(C6) AS S6 from(你的查询结果.但得对应字段别名c1,c2,c3,c4,c5,c6)GROUP BY C1,C2,C3,C4,C5
select fpm.id, fpm.purchase_no , fbm.fabric_no, fbm.fabric_name ,fpm.shipping_date ,DATEADD(day, 3, fpm.shipping_date) as madedate ,ISNULL(sum(fpi.purchase_quantity),0)as purchase_quantity ,storequantity -----这就是最后一列。 from fabricpurchasemain fpm inner join fabricmain fbm on fbm.id=fpm.fabricmain_id left outer join fabricpurchaseitem fpi on fpi.fabricpurchasemain_id=fpm.id left outer join fabricstoredetail fsd on fsd.FabricPurchaseMain_Id=fpm.id where fpm.status<2 group by fpm.id,purchase_no,fabric_no,fabric_name,fpm.shipping_date,storequantity
可以这样: select a,b,sum(c) from (select a,sum(b) as b,sum(c) as c from table group by a) tmp group by a,b
可以这样: select a,b,sum(c) from (select a,sum(b) as b,sum(c) as c from table group by a) tmp group by a,b
select column1,column2,column3,column4,column5,sum(column6) as column6 from 表 group by column1,column2,column3,column4,column5测试过,没问题!!!!
可以这样: select a,b,sum(c) from (select a,sum(b) as b,sum(c) as c from table group by a) tmp group by a,b
1 M03-000001 W001 全毛 350.0 1.0
1 M03-000001 W001 全毛 350.0 3.0
1 M03-000001 W001 全毛 350.0 4.0
2 M03-000002 W001 全毛 0.0 343.0
3 M03-000003 N001 氨伦 0.0 NULL
4 M03-000004 LDR001 领底绒 0.0 NULL
则SQL文为
SELECT C1,C2,C3,C4,C5, SUM(C6) AS S6
FROM TABLE1
GROUP BY C1,C2,C3,C4,C5
====查询出----这个结果,已经用了查询了。
1 M03-000001 W001 全毛 350.0 1.0
1 M03-000001 W001 全毛 350.0 3.0
1 M03-000001 W001 全毛 350.0 4.0
2 M03-000002 W001 全毛 0.0 343.0
3 M03-000003 N001 氨伦 0.0 NULL
4 M03-000004 LDR001 领底绒 0.0 NULL
5 M03-000005 W002 全羊绒 0.0 NULL
我想用游标,不知道怎么写,大家可会
用游标不好,速度慢.在外面再套一层查询。SELECT C1,C2,C3,C4,C5, SUM(C6) AS S6 from(你的查询结果.但得对应字段别名c1,c2,c3,c4,c5,c6)GROUP BY C1,C2,C3,C4,C5
fpm.id,
fpm.purchase_no ,
fbm.fabric_no,
fbm.fabric_name
,fpm.shipping_date
,DATEADD(day, 3, fpm.shipping_date) as madedate
,ISNULL(sum(fpi.purchase_quantity),0)as purchase_quantity
,storequantity -----这就是最后一列。
from fabricpurchasemain fpm
inner join fabricmain fbm on fbm.id=fpm.fabricmain_id
left outer join fabricpurchaseitem fpi on fpi.fabricpurchasemain_id=fpm.id
left outer join fabricstoredetail fsd on fsd.FabricPurchaseMain_Id=fpm.id
where fpm.status<2
group by fpm.id,purchase_no,fabric_no,fabric_name,fpm.shipping_date,storequantity
select a,b,sum(c) from
(select a,sum(b) as b,sum(c) as c from table group by a) tmp
group by a,b
select a,b,sum(c) from
(select a,sum(b) as b,sum(c) as c from table group by a) tmp
group by a,b
select a,b,sum(c) from
(select a,sum(b) as b,sum(c) as c from table group by a) tmp
group by a,b
txlicenhe(不做技术高手) 的方法是对了。谢谢大家了。
呆会结分。