用两个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语句,这样把每个部门的两种费用一次分组计算出来,还能避免有张表费用总和为空时会产生计算错误的情况呢?
19 个解决方案
#1
select t.department,sum(t.price_total_tax+t1.price_total_tax) price from order_req t,cost_req t1 where t.department=t1.department
group by t.department;
group by t.department;
#2
select a.department, a.price1, b.price2
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
这样可以吗。如果不可以最好贴数据跟想要的结果
#3
刚忘记加为空处理了,现在补上
select t.department,sum(nvl(t.price_total_tax,0)+nvl(t1.price_total_tax,0)) price from order_req t,cost_req t1 where t.department=t1.department
group by t.department;
#4
用这个计算出来的数据计算不正确,并且空处理似乎没有起到作用:
分别用两个sql查出来的两个表中各部门的数据如下:
表t中只有一个部门A有费用为140515.04
表t1中有两个部门有费用,A部门为25284.68,B部门为204819.75
因此汇总结果应为:
A总计165799.72,B部门为204819.75
但实际的查询结果是:
A部门总计2349247.92,B部门费用总计为空
怎么办呢?
#5
a.price1和b.price2怎样处理可以分部门得到总和?
数据以及想要的结果我写在4楼啦,再帮忙分析一下,谢谢~
#6
创建一个视图view_order_cost
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是不是你要的结果
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是不是你要的结果
#7
实在不好意思,其实原始查询的两个sql语句都有还有一些需要传参数的条件约束,所以用视图无法做详细的条件查询吧?
我把详细的需求重新写一下:
应用环境是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
目前用各位的方法还是无法达到这个目的,继续求助……
我把详细的需求重新写一下:
应用环境是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
目前用各位的方法还是无法达到这个目的,继续求助……
#8
试试这个:
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;
#9
修改一下部门名字丢失的问题:
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
#10
好热闹,进来看看
#11
求和的话,再稍微修改成如下:
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
#12
--试一下:
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(+);
#13
--先用sqlplus在数据库中查试一下,如果能行,再在应用程序中改一下:
select t1.d, t1.order_price, t2.cost_price
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(+);
#14
是不是要这样的???
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
#15
试用11楼的方法,得到错误:无法从套接字读取更多的数据
我用的是oracle10g,会是什么问题?
14楼的方法测试中~
虽然问题还没解决,但还是要先谢过各位的帮忙!
我用的是oracle10g,会是什么问题?
14楼的方法测试中~
虽然问题还没解决,但还是要先谢过各位的帮忙!
#16
select sum( nvl(price_total_tax,0)), department
from
(
select price_total_tax,department from order_req
union all
select price_total_tax,department from cost_req
)
group by department
from
(
select price_total_tax,department from order_req
union all
select price_total_tax,department from cost_req
)
group by department
#17
用14楼的方法可以!
谢谢大家,让我学了这么多解决方案!
谢谢大家,让我学了这么多解决方案!
#18
16楼会出现不是单组分组函数的错误
#19
测试。
#20
#1
select t.department,sum(t.price_total_tax+t1.price_total_tax) price from order_req t,cost_req t1 where t.department=t1.department
group by t.department;
group by t.department;
#2
select a.department, a.price1, b.price2
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
这样可以吗。如果不可以最好贴数据跟想要的结果
#3
刚忘记加为空处理了,现在补上
select t.department,sum(nvl(t.price_total_tax,0)+nvl(t1.price_total_tax,0)) price from order_req t,cost_req t1 where t.department=t1.department
group by t.department;
#4
用这个计算出来的数据计算不正确,并且空处理似乎没有起到作用:
分别用两个sql查出来的两个表中各部门的数据如下:
表t中只有一个部门A有费用为140515.04
表t1中有两个部门有费用,A部门为25284.68,B部门为204819.75
因此汇总结果应为:
A总计165799.72,B部门为204819.75
但实际的查询结果是:
A部门总计2349247.92,B部门费用总计为空
怎么办呢?
#5
a.price1和b.price2怎样处理可以分部门得到总和?
数据以及想要的结果我写在4楼啦,再帮忙分析一下,谢谢~
#6
创建一个视图view_order_cost
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是不是你要的结果
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是不是你要的结果
#7
实在不好意思,其实原始查询的两个sql语句都有还有一些需要传参数的条件约束,所以用视图无法做详细的条件查询吧?
我把详细的需求重新写一下:
应用环境是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
目前用各位的方法还是无法达到这个目的,继续求助……
我把详细的需求重新写一下:
应用环境是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
目前用各位的方法还是无法达到这个目的,继续求助……
#8
试试这个:
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;
#9
修改一下部门名字丢失的问题:
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
#10
好热闹,进来看看
#11
求和的话,再稍微修改成如下:
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
#12
--试一下:
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(+);
#13
--先用sqlplus在数据库中查试一下,如果能行,再在应用程序中改一下:
select t1.d, t1.order_price, t2.cost_price
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(+);
#14
是不是要这样的???
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
#15
试用11楼的方法,得到错误:无法从套接字读取更多的数据
我用的是oracle10g,会是什么问题?
14楼的方法测试中~
虽然问题还没解决,但还是要先谢过各位的帮忙!
我用的是oracle10g,会是什么问题?
14楼的方法测试中~
虽然问题还没解决,但还是要先谢过各位的帮忙!
#16
select sum( nvl(price_total_tax,0)), department
from
(
select price_total_tax,department from order_req
union all
select price_total_tax,department from cost_req
)
group by department
from
(
select price_total_tax,department from order_req
union all
select price_total_tax,department from cost_req
)
group by department
#17
用14楼的方法可以!
谢谢大家,让我学了这么多解决方案!
谢谢大家,让我学了这么多解决方案!
#18
16楼会出现不是单组分组函数的错误
#19
测试。