select a.company_id,a.sord_id,a.cust_id,sum(b.qty) qty,sum(case c.tag when 0 then b.qty-c.qty_over when 1 then b.qty end) rest_qty
from table1 a join table2 b on a.company_id=b.company_id join table3 c on b.product_id=c.product_id
group by b.sord_id
order by a.company_id
from table1 a join table2 b on a.company_id=b.company_id join table3 c on b.product_id=c.product_id
group by b.sord_id
order by a.company_id
from table1 a join table2 b on a.company_id=b.company_id join table3 c on b.product_id=c.product_id
group by b.company_id,b.sord_id
order by b.company_id,b.sord_id
SELECT a.company_id,a.SORD_ID,a.CUST_ID,b.qty
, REST=b.QTY-COALESCE((select sum(QTY) from table3 where company_id=b.company_id and sord_id=b.sord_id and sord_lineno=b.line_no and check_tag='1' and out_type='0'),0) +COALESCE((select sum(QTY) from table3 where company_id=b.company_id and sord_id=b.sord_id and sord_lineno=b.line_no and check_tag='1' and out_type='1'),0)
FROM view1 as a
, table2 as b
where a.company_id='1' and a.company_id=b.company_id
and a.sord_id=b.sord_id and a.order_date<='2002-08-12'
order by convert(int,a.sord_id) asc 得到结果如下:
company_id SORD_ID CUST_ID qty REST
1 1 2 100.0000 .0000
1 2 5 200.0000 .0000
1 3 2 3000.0000 3000.0000
1 4 5 500.0000 500.0000
1 4 5 400.0000 400.0000
1 4 5 600.0000 600.0000但我希望得到结果如下:company_id SORD_ID CUST_ID qty REST
1 1 2 100.0000 .0000
1 2 5 200.0000 .0000
1 3 2 3000.0000 3000.0000
1 4 5 1500.0000 1500.0000即company_id,sord_id相同的记录显示为一条语句:
1 4 5 500.0000 500.0000
1 4 5 400.0000 400.0000
1 4 5 600.0000 600.0000
请问怎么改?
当check_tag='1' and out_type='0'时,rest=table1.qty-table3.qty;
当check_tag='1' and out_type='1'时,rest=table1.qty+table3.qty大家明白我的意思了吗?
, sum(b.QTY-COALESCE((select sum(QTY) from table3 where company_id=b.company_id and sord_id=b.sord_id and sord_lineno=b.line_no and check_tag='1' and out_type='0'),0) +COALESCE((select sum(QTY) from table3 where company_id=b.company_id and sord_id=b.sord_id and sord_lineno=b.line_no and check_tag='1' and out_type='1'),0)) REST
FROM view1 as a
, table2 as b
where a.company_id='1' and a.company_id=b.company_id
and a.sord_id=b.sord_id and a.order_date<='2002-08-12'
group by a.company_id,a.sord_id
order by convert(int,a.sord_id) asc
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
我的rest_qty是通过table3的另外两个字段,qty_over,tag标示的,如果该记录的字段为0,则“rest_qty=qty-qty_over“,如果tag为"1",则
rest_qty=qty
table3是一个冲销表,有company_id,sord_id,sord_lineno,qty,check_tag,out_type等字段,其中company_id,sord_id与table1,table2相关联,sord_lineno与table2.line_no字段相关联
当check_tag='1' and out_type='0'时,rest=table1.qty-table3.qty;
当check_tag='1' and out_type='1'时,rest=table1.qty+table3.qty请问你要得到的结果集中 Qty是单据明细的总量?你的rest_qty 同你后面说的rest是什么关系,还有……请你把这三个表的关系说清楚,并把你要得到的结果说清楚,我现在只明白了一个大概
当check_tag='1' and out_type='0'时,rest=table1.qty-table3.qty;
当check_tag='1' and out_type='1'时,rest=table1.qty+table3.qty这个说明才是正确的,至于刚开始那个声明可以不用考虑,我想得到的结果是
一个company_id对应一个sord_id里的所有记录的总量qty及其剩余量rest,
而company_id,sord_id,cust_id,order_date等字段是存放在table1(这个一个主表),我把table1的内容扩展到view1,而table2是通过company_id,sord_id,line_no(行号)来标识talbe1下每条记录的明细,而rest量得通过以上table3来得到结果。这样说你完全明白了吗?