如:select * from ( select 客户编号 编号,客户名称 名称,null 数量,null 金额,客户编号 flag1,1 falg2 from 表 group by 客户编号,客户名称 union all select 料号,名称,sum(数量) 数量,sum(金额) 金额,客户编号,2 from 表 group by 客户编号,料号,名称 ) tem order by flag1,flag2 这种形式。
Try: Select * from ( Select 客户代号,'' as 料品代号,sum(数量) as 数量,sum(金额) as 金额 from 表 group by 客户代号,'' Union all Select 客户代号,料品代号,sum(数量) as 数量,sum(金额) as 金额 from 表 group by 客户代号,料品代号 ) order by 客户代号,料品代号
Try: Select * from ( Select 客户代号,'' as 料品代号,sum(数量) as 数量,sum(金额) as 金额 from 表 group by 客户代号,'' Union all Select 客户代号,料品代号,sum(数量) as 数量,sum(金额) as 金额 from 表 group by 客户代号,料品代号 ) b order by 客户代号,料品代号
成功了,多谢,不过和大力的思路是相同的,代码如下: select * from (SELECT ord20210.cust cust, test=' ', sum(ord20211.txqty) txqty, sum(ord20211.acramt) acramt FROM ord20210, ord20211, cst10100, inv10100 WHERE ( ord20211.txformat = ord20210.txformat ) and ( ord20211.docno = ord20210.docno ) and ( ord20210.cust >= '') and ( ord20210.cust <= 'zz') and ( ord20210.docdate >= '1900-01-01') and ( ord20210.docdate <= '2999-01-01') and ( ord20211.status = 'CL') and ( ord20210.cust = cst10100.cust) and ( ord20211.part = inv10100.part) group by ord20210.cust union all SELECT cst10100.cust cust, ord20211.part test, sum(ord20211.txqty) txqty, sum(ord20211.acramt) acramt
FROM ord20210, ord20211, inv10100, cst10100 WHERE ( ord20211.txformat = ord20210.txformat ) and ( ord20211.docno = ord20210.docno ) and ( ord20210.cust >= '') and ( ord20210.cust <= 'zz') and ( ord20210.docdate >= '1900-01-01') and ( ord20210.docdate <= '2999-01-01') and ( ord20211.status = 'CL') and ( ord20211.part = inv10100.part) and ( ord20210.cust = cst10100.cust) group by cst10100.cust,ord20211.part) b order by cust,test能不能讲解一下,何以union有如此的效果呢?我得理解是,第一个select 得出每个客户以及每个客户的汇总后底数量和金额,即按客户分组,而后一个select按客户和料品分组,看起来,第一个是汇总信息,第二个是前一个的明细,最后的排序则体现了两层的效果;另外就是union all的使用,不会将重复的记录过滤也是一个重点。还有一个问题,就是分组时,如何将其他的栏位列出,比如按料号分组,怎样将此料号的名称栏位贷出,还请帮忙,如过分不够,还可在加。
select 客户编号 编号,客户名称 名称,null 数量,null 金额,客户编号 flag1,1 falg2 from 表 group by 客户编号,客户名称
union all
select 料号,名称,sum(数量) 数量,sum(金额) 金额,客户编号,2 from 表 group by 客户编号,料号,名称
) tem order by flag1,flag2
这种形式。
一行是客户代号和客户名称
其下可能有一行或多行,每行显示料品代号,料品名称,及数量的统计(按料号)和金额的统计(按料号),然后又是一行客户代号和客户名称,依此类推表结构如下:
inv10100 料品主档 ----在此表中抓料品名称(description)
cst10100 客户主档 ----在此表中抓客户名称(description)
ord20210 订单表头档----在此表中抓客户代号(cust)
ord20211 订单明细档----在此表中抓料品代号、数量、金额(part,txqty,acramt)查询条件:
ord20210 订单表头档的cust在某一范围内,docdate在某一范围内 并且ord20211 订单明细档
的status='CL',可参考我得提问里的sql语法
SQL只能分组一层。
Select * from (
Select 客户代号,'' as 料品代号,sum(数量) as 数量,sum(金额) as 金额
from 表 group by 客户代号,''
Union all
Select 客户代号,料品代号,sum(数量) as 数量,sum(金额) as 金额
from 表 group by 客户代号,料品代号
)
order by 客户代号,料品代号
Select * from (
Select 客户代号,'' as 料品代号,sum(数量) as 数量,sum(金额) as 金额
from 表 group by 客户代号,''
Union all
Select 客户代号,料品代号,sum(数量) as 数量,sum(金额) as 金额
from 表 group by 客户代号,料品代号
) b
order by 客户代号,料品代号
select * from (SELECT ord20210.cust cust,
test=' ',
sum(ord20211.txqty) txqty,
sum(ord20211.acramt) acramt
FROM ord20210,
ord20211,
cst10100,
inv10100
WHERE ( ord20211.txformat = ord20210.txformat ) and
( ord20211.docno = ord20210.docno ) and
( ord20210.cust >= '') and
( ord20210.cust <= 'zz') and
( ord20210.docdate >= '1900-01-01') and
( ord20210.docdate <= '2999-01-01') and
( ord20211.status = 'CL') and
( ord20210.cust = cst10100.cust) and
( ord20211.part = inv10100.part)
group by ord20210.cust
union all
SELECT cst10100.cust cust,
ord20211.part test,
sum(ord20211.txqty) txqty,
sum(ord20211.acramt) acramt
FROM ord20210,
ord20211,
inv10100,
cst10100
WHERE ( ord20211.txformat = ord20210.txformat ) and
( ord20211.docno = ord20210.docno ) and
( ord20210.cust >= '') and
( ord20210.cust <= 'zz') and
( ord20210.docdate >= '1900-01-01') and
( ord20210.docdate <= '2999-01-01') and
( ord20211.status = 'CL') and
( ord20211.part = inv10100.part) and
( ord20210.cust = cst10100.cust)
group by cst10100.cust,ord20211.part) b
order by cust,test能不能讲解一下,何以union有如此的效果呢?我得理解是,第一个select 得出每个客户以及每个客户的汇总后底数量和金额,即按客户分组,而后一个select按客户和料品分组,看起来,第一个是汇总信息,第二个是前一个的明细,最后的排序则体现了两层的效果;另外就是union all的使用,不会将重复的记录过滤也是一个重点。还有一个问题,就是分组时,如何将其他的栏位列出,比如按料号分组,怎样将此料号的名称栏位贷出,还请帮忙,如过分不够,还可在加。