统计各部门的费用和涉及到两张表
用两个sql语句分别是:
select sum(price_total_tax) price,department from order_req group by department;
select sum(price_total_tax) price,department from cost_req group by department;
如何合成一个sql语句,这样把每个部门的两种费用一次分组计算出来,还能避免有张表费用总和为空时会产生计算错误的情况呢?
用两个sql语句分别是:
select sum(price_total_tax) price,department from order_req group by department;
select sum(price_total_tax) price,department from cost_req group by department;
如何合成一个sql语句,这样把每个部门的两种费用一次分组计算出来,还能避免有张表费用总和为空时会产生计算错误的情况呢?
group by t.department;
from (select sum(price_total_tax) price1, department
from order_req
group by department) a,
(select sum(price_total_tax) price2, department
from cost_req
group by department) b
where a.department = b.department
这样可以吗。如果不可以最好贴数据跟想要的结果
group by t.department;
用这个计算出来的数据计算不正确,并且空处理似乎没有起到作用:
分别用两个sql查出来的两个表中各部门的数据如下:
表t中只有一个部门A有费用为140515.04
表t1中有两个部门有费用,A部门为25284.68,B部门为204819.75
因此汇总结果应为:
A总计165799.72,B部门为204819.75
但实际的查询结果是:
A部门总计2349247.92,B部门费用总计为空
怎么办呢?
a.price1和b.price2怎样处理可以分部门得到总和?
数据以及想要的结果我写在4楼啦,再帮忙分析一下,谢谢~
select sum(price_total_tax) price,department from order_req group by department;
UNION
select sum(price_total_tax) price,department from cost_req group by department; Select sum(price_total_tax) price,department From view_order_cost group by department是不是你要的结果
我把详细的需求重新写一下:应用环境是spring+orcl,要求是从jsp页面输入查询的起始日期(start_date)和结束日期(end_date)得出各部门的这段时间内的费用总和,记录费用明细的表有两张T1和T2使用以下两个sql语句查询
query1:select sum(price_total_tax) price,department from T1
where sign_date between to_date('${param.start_date}','yyyy-mm-dd') and to_date('${param.end_date}','yyyy-mm-dd')+1
group by department;
query2:select sum(price_total_tax) price,department from T2
where out_date between to_date('${param.start_date}','yyyy-mm-dd') and to_date('${param.end_date}','yyyy-mm-dd')+1
and order_type='C'
group by department;
可以得到:
query1的结果:部门A price=140515.04
query2的结果:部门A price=25284.68,
部门B price=204819.75
因此用一条语句查询得到的结果应为: 部门A price=165799.72
部门B price=204819.75 目前用各位的方法还是无法达到这个目的,继续求助……
SELECT X.DEPARTMENT, X.PRICE PRICE1, Y.PRICE PRICE2
FROM (SELECT SUM(PRICE_TOTAL_TAX) PRICE, DEPARTMENT
FROM T1
WHERE SIGN_DATE BETWEEN
TO_DATE('${param.start_date}', 'yyyy-mm-dd') AND
TO_DATE('${param.end_date}', 'yyyy-mm-dd') + 1
GROUP BY DEPARTMENT) X
FULL JOIN (SELECT SUM(PRICE_TOTAL_TAX) PRICE, DEPARTMENT
FROM T2
WHERE OUT_DATE BETWEEN
TO_DATE('${param.start_date}', 'yyyy-mm-dd') AND
TO_DATE('${param.end_date}') Y 'yyyy-mm-dd') + 1 AND ORDER_TYPE = 'C'
GROUP BY DEPARTMENT) Y ON X.DEPARTMENT = Y.DEPARTMENT;
SELECT nvl(X.DEPARTMENT,Y.DEPARTMENT) DEPARTMENT, X.PRICE PRICE1, Y.PRICE PRICE2
FROM (SELECT SUM(PRICE_TOTAL_TAX) PRICE, DEPARTMENT
FROM T1
WHERE SIGN_DATE BETWEEN
TO_DATE('${param.start_date}', 'yyyy-mm-dd') AND
TO_DATE('${param.end_date}', 'yyyy-mm-dd') + 1
GROUP BY DEPARTMENT) X
FULL JOIN (SELECT SUM(PRICE_TOTAL_TAX) PRICE, DEPARTMENT
FROM T2
WHERE OUT_DATE BETWEEN
TO_DATE('${param.start_date}', 'yyyy-mm-dd') AND
TO_DATE('${param.end_date}') Y 'yyyy-mm-dd') + 1 AND ORDER_TYPE = 'C'
GROUP BY DEPARTMENT) Y ON X.DEPARTMENT = Y.DEPARTMENT;测试如下:
WITH x AS (SELECT 'a' dep,140515.04 price FROM dual),
y AS (SELECT 'a' dep,25284.68 price FROM dual UNION ALL
SELECT 'b' dep,204819.75 price FROM dual)
SELECT nvl(x.dep,y.dep) dep,x.price p1,y.price p2 FROM x FULL JOIN y ON x.dep=y.dep;
输出:
DEP P1 P2
a 140515.04 25284.68
b 204819.75
SELECT nvl(X.DEPARTMENT,Y.DEPARTMENT) DEPARTMENT, nvl(X.PRICE,0)+nvl(Y.PRICE,0) PRICE
FROM (SELECT SUM(PRICE_TOTAL_TAX) PRICE, DEPARTMENT
FROM T1
WHERE SIGN_DATE BETWEEN
TO_DATE('${param.start_date}', 'yyyy-mm-dd') AND
TO_DATE('${param.end_date}', 'yyyy-mm-dd') + 1
GROUP BY DEPARTMENT) X
FULL JOIN (SELECT SUM(PRICE_TOTAL_TAX) PRICE, DEPARTMENT
FROM T2
WHERE OUT_DATE BETWEEN
TO_DATE('${param.start_date}', 'yyyy-mm-dd') AND
TO_DATE('${param.end_date}') Y 'yyyy-mm-dd') + 1 AND ORDER_TYPE = 'C'
GROUP BY DEPARTMENT) Y ON X.DEPARTMENT = Y.DEPARTMENT;
WITH x AS (SELECT 'a' dep,140515.04 price FROM dual),
y AS (SELECT 'a' dep,25284.68 price FROM dual UNION ALL
SELECT 'b' dep,204819.75 price FROM dual)
SELECT nvl(x.dep,y.dep) dep,nvl(x.price,0) + nvl(y.price,0) p2
FROM x FULL JOIN y ON x.dep=y.dep;
输出:
DEP P2
a 165799.72
b 204819.75
--试一下:select t1.d, t1.price, t2.price
from (select sum(nvl(price_total_tax, 0)) price, department d
from order_req
where to_char(Your_date, 'yyyymmdd') >= '20081001'
and to_char(Your_date, 'yyyymmdd') <= '20081031'
group by department) t1,
(select sum(nvl(price_total_tax, 0)) price, department d
from cost_req
where to_char(Your_date, 'yyyymmdd') >= '20081001'
and to_char(Your_date, 'yyyymmdd') <= '20081031'
group by department) t2
where t1.d = t2.d(+);
from (select sum(nvl(price_total_tax,0)) order_price, department d
from order_req
where to_char(sign_date,'yyyymmdd') between '20081001' and '20081031'
group by department) t1,
(select sum(nvl(price_total_tax,0)) cost_price, department d
from cost_req
where to_char(sign_date,'yyyymmdd') between '20081001' and '20081031'
group by department) t2
where t1.d = t2.d(+);
SELECT DEPARTMENT, SUM(PRICE) PRICE
FROM (SELECT SUM(NVL(PRICE_TOTAL_TAX, 0)) PRICE, DEPARTMENT
FROM T1
WHERE SIGN_DATE BETWEEN
TO_DATE('${PARAM.START_DATE}', 'YYYY-MM-DD') AND
TO_DATE('${PARAM.END_DATE}', 'YYYY-MM-DD') + 1
GROUP BY DEPARTMENT
UNION
SELECT SUM(NVL(PRICE_TOTAL_TAX, 0)) PRICE, DEPARTMENT
FROM T2
WHERE OUT_DATE BETWEEN TO_DATE('${PARAM.START_DATE}', 'YYYY-MM-DD') AND
TO_DATE('${PARAM.END_DATE}') Y 'YYYY-MM-DD') + 1 AND
ORDER_TYPE = 'C'
GROUP BY DEPARTMENT)
GROUP BY DEPARTMENT
是不是要这样的???
我用的是oracle10g,会是什么问题?14楼的方法测试中~虽然问题还没解决,但还是要先谢过各位的帮忙!
from
(
select price_total_tax,department from order_req
union all
select price_total_tax,department from cost_req
)
group by department