有两个表,一个是部门表,一个预算表表结构如下
select * from td_dep //部门表
depID depName
1 生产部
2 办公室
3 质检站 select * from td_pay //预算表
payID payName payDate fee_money bankroll_money depid
1 报出车费 2007-01-02 100.50 200.5 1
2 报招待费 2007-01-04 50 100 2
3 报柴油费 2007-02-24 500 1000 1
4 报柴油费 2007-03-03 200 1000 1
5 报出车费 2007-04-04 50 100 1
6 报党建费 2007-05-06 50 500 1
7 报出车费 2007-05-15 100 100 1 现在我要合计出生产部3、4、5月的费用情况并得出合计,我写了一个SQL语句,但是老是不显示合计
这个是我的SQL语句:
select decode(yy.payid || ' ', ' ', '本月小计 ',yy.payid || ' ') as payid,
yy.payname,
tt.paydate,
tt.sum_fee_money,
tt.sum_bankroll_money,
yy.depName,
tt.ym
from (
select grouping_id(to_char(tp.payDate, 'yyyymm '),tp.payDate),
tp.payDate,
to_char(tp.payDate, 'yyyymm ') as ym,
sum(tp.fee_money) as sum_fee_money,
sum(tp.bankroll_money) as sum_bankroll_money
from td_dep td,
td_pay tp
where td.depID = tp.depid
and td.depid = 1
group by rollup(to_char(tp.payDate, 'yyyymm '),tp.payDate)
)tt
,
(select *
from td_dep td,
td_pay tp
where td.depID = tp.depid
and td.depid = 1
)yy
where tt.payDate = yy.payDate(+)
and tt.ym >= '200703 '
and tt.ym <= '200705 '
and tt.ym is not null 我得出的结果是这样的,没有每个月合计行。
payid payname paydate sum_fee_money sum_bankroll_money depName ym
4 报柴油费 2007-3-3 200 1000 生产部 200703
5 报出车费 2007-4-4 50 100 生产部 200704
6 报党建费 2007-5-6 50 500 生产部 200705
7 报出车费 2007-5-15 100 100 生产部 200705
请各位高手赐教!万分感谢!
select * from td_dep //部门表
depID depName
1 生产部
2 办公室
3 质检站 select * from td_pay //预算表
payID payName payDate fee_money bankroll_money depid
1 报出车费 2007-01-02 100.50 200.5 1
2 报招待费 2007-01-04 50 100 2
3 报柴油费 2007-02-24 500 1000 1
4 报柴油费 2007-03-03 200 1000 1
5 报出车费 2007-04-04 50 100 1
6 报党建费 2007-05-06 50 500 1
7 报出车费 2007-05-15 100 100 1 现在我要合计出生产部3、4、5月的费用情况并得出合计,我写了一个SQL语句,但是老是不显示合计
这个是我的SQL语句:
select decode(yy.payid || ' ', ' ', '本月小计 ',yy.payid || ' ') as payid,
yy.payname,
tt.paydate,
tt.sum_fee_money,
tt.sum_bankroll_money,
yy.depName,
tt.ym
from (
select grouping_id(to_char(tp.payDate, 'yyyymm '),tp.payDate),
tp.payDate,
to_char(tp.payDate, 'yyyymm ') as ym,
sum(tp.fee_money) as sum_fee_money,
sum(tp.bankroll_money) as sum_bankroll_money
from td_dep td,
td_pay tp
where td.depID = tp.depid
and td.depid = 1
group by rollup(to_char(tp.payDate, 'yyyymm '),tp.payDate)
)tt
,
(select *
from td_dep td,
td_pay tp
where td.depID = tp.depid
and td.depid = 1
)yy
where tt.payDate = yy.payDate(+)
and tt.ym >= '200703 '
and tt.ym <= '200705 '
and tt.ym is not null 我得出的结果是这样的,没有每个月合计行。
payid payname paydate sum_fee_money sum_bankroll_money depName ym
4 报柴油费 2007-3-3 200 1000 生产部 200703
5 报出车费 2007-4-4 50 100 生产部 200704
6 报党建费 2007-5-6 50 500 生产部 200705
7 报出车费 2007-5-15 100 100 生产部 200705
请各位高手赐教!万分感谢!
解决方案 »
- 求一Oracle的查询语句
- 怎么在lotus workflow组织库中建组织信息
- oralce9i 支持 层次查询吗?
- 用序列做缺省值的问题
- oracle 备份与恢复?????????????????????????????????????????????????
- 求助视图权限问题,急,在线等待。。。
- 得到叶子结点所在树的跟结点的sql如何写?
- linux 下 oracle导出问题?
- 在来16道
- 关于ORACL的查询问题
- 在procedure中如何调用参数是游标类型的
- 问一个比较弱质的问题啊,.Net开发的时候,装了oracle data provider for .net 还需要装Orcle客户端吗?
select decode(yy.payid || ' ', ' ', '本月小计 ',yy.payid || ' ') as payid,
yy.payname,
tt.paydate,
tt.sum_fee_money,
tt.sum_bankroll_money,
yy.depName,
tt.ym
from (
select grouping_id(to_char(td_pay.payDate, 'yyyymm '),td_pay.payDate),
td_pay.payDate,
to_char(td_pay.payDate, 'yyyymm ') as ym,
sum(td_pay.fee_money) as sum_fee_money,
sum(td_pay.bankroll_money) as sum_bankroll_money
from td_dep, td_pay
where td_dep.depID = td_pay.deptid
and td_dep.depid = 1
group by rollup(to_char(td_pay.payDate, 'yyyymm '),td_pay.payDate)
)tt
,
(select *
from td_dep, td_pay
where td_dep.depID = td_pay.deptid
and td_dep.depid = 1
)yy
where tt.payDate = yy.payDate(+)
and tt.ym >= '200703 '
and tt.ym <= '200705 '
and tt.ym is not null;PAYID PAYNAME PAYDATE SUM_FEE_MONEY SUM_BANKROLL_MONEY DEPNAME YM
---------- -------------------- ----------- ------------- ------------------ -------------------- --------
4 报菜油费 2007-3-3 200 1000 生产部 200703
5 报出车费 2007-4-4 50 100 生产部 200704
6 报党建费 2007-5-6 50 500 生产部 200705
7 报出车费 2007-5-15 100 100 生产部 200705
and tt.ym <= '200705 '
and tt.ym is not null
这个条件怎么用在连接上呢?
一下子把集计的行都去掉了.赫赫
加到tt的条件里面去。
主要问题是在where tt.payDate = yy.payDate(+),这句上
这里,别名为TT的查询结果为分组合计,合计里tt.payDate为空,所以最终的查询结果里没有了合计项目
我把SQL语句改成了这样select DISTINCT
CASE when tt.paydate is null then '本月小计 'else '' || yy.payid end as payid,
yy.payname,
tt.paydate,
tt.sum_fee_money,
tt.sum_bankroll_money,
yy.depName,
tt.ym
from ( select grouping_id(to_char(tp.payDate, 'yyyymm '),tp.payDate) as ids,
tp.payDate,
to_char(tp.payDate, 'yyyymm ') as ym,
sum(tp.fee_money) as sum_fee_money,
sum(tp.bankroll_money) as sum_bankroll_money
from td_dep td,
td_pay tp
where td.depID = tp.depid
and td.depid = 1
group by rollup(to_char(tp.payDate, 'yyyymm '),tp.payDate)
)tt ,
(select td.*,tp.* ,to_char(tp.payDate, 'yyyymm ') as ym
from td_dep td,
td_pay tp
where td.depID = tp.depid
and td.depid = 1
)yy
where tt.ym = yy.ym(+)
and tt.ym >= '200703 '
and tt.ym <= '200705 ' 查询出来的结果为
payid payname paydate sum_fee_money sum_bankroll_money depName ym
4 报柴油费 2007-3-3 200 1000 生产部 200703
本月小计 报柴油费 200 1000 生产部 200703
5 报出车费 2007-4-4 50 100 生产部 200704
本月小计 报出车费 50 100 生产部 200704
6 报党建费 2007-5-6 50 500 生产部 200705
6 报党建费 2007-5-15 100 100 生产部 200705
7 报出车费 2007-5-6 50 500 生产部 200705
7 报出车费 2007-5-15 100 100 生产部 200705
本月小计 报出车费 150 600 生产部 200705
本月小计 报党建费 150 600 生产部 200705 现在问题又出来了,我5月分有两条记录,查询结果就显示了两次,这个问题是怎么回事,有什么办法避免吗?
我的sql语句查出来的结果有重复项,有办法避免吗?
where tt.payDate = yy.payDate(+)
---- 我写上面这个句的目的是当tt内联试图里的payDate字段为空时这条记录就是每个月合计的记录,具体是那个月呢?是根据tt内联试图里的ym子段来区别的。所以你写下面这条语句的时候就会出现重复项.
where tt.ym = yy.ym(+)
然后我把我写的sql语句、相应的结果和你写的sql语句、相应的结果比对一下就知道了。我的:
sql:select decode(yy.payid || '', '', '本月小计', yy.payid || '') as payid,
yy.payname,
tt.paydate,
tt.sum_fee_money,
tt.sum_bankroll_money,
yy.depName,
tt.ym
from (select grouping_id(to_char(tp.payDate, 'yyyymm'), tp.payDate),
tp.payDate,
to_char(tp.payDate, 'yyyymm') as ym,
sum(tp.fee_money) as sum_fee_money,
sum(tp.bankroll_money) as sum_bankroll_money
from td_dep td,
td_pay tp
where td.depID = tp.depid
and td.depName = '生产部'
group by rollup(to_char(tp.payDate, 'yyyymm'), tp.payDate)) tt,
(select *
from td_dep td,
td_pay tp
where td.depID = tp.depid
and td.depName = '生产部') yy
where tt.payDate = yy.payDate(+)
and tt.ym >= '200703'
and tt.ym <= '200705'
and tt.ym is not null;
result:
PAYID PAYNAME PAYDATE SUM_FEE_MONEY SUM_BANKROLL_MONEY DEPNAME YM
---------------------------------------- ---------------- ----------- ------------- ------------------ ------------ ------
4 报柴油费 3/3/2007 200 1000 生产部 200703
5 报出车费 4/4/2007 50 100 生产部 200704
6 报党建费 5/6/2007 50 500 生产部 200705
7 报出车费 5/15/2007 100 100 生产部 200705
本月小计 150 600 200705
本月小计 50 100 200704
本月小计 200 1000 2007037 rows selected
你的:sql:
select DISTINCT CASE
when tt.paydate is null then
'本月小计'
else
'' || yy.payid
end as payid,
yy.payname,
tt.paydate,
tt.sum_fee_money,
tt.sum_bankroll_money,
yy.depName,
tt.ym
from (select grouping_id(to_char(tp.payDate, 'yyyymm'), tp.payDate) as ids,
tp.payDate,
to_char(tp.payDate, 'yyyymm') as ym,
sum(tp.fee_money) as sum_fee_money,
sum(tp.bankroll_money) as sum_bankroll_money
from td_dep td,
td_pay tp
where td.depID = tp.depid
and td.depid = 1
group by rollup(to_char(tp.payDate, 'yyyymm'), tp.payDate)) tt,
(select td.*, tp.*, to_char(tp.payDate, 'yyyymm') as ym
from td_dep td,
td_pay tp
where td.depID = tp.depid
and td.depid = 1) yy
where tt.ym = yy.ym(+)
and tt.ym >= '200703'
and tt.ym <= '200705';
result:PAYID PAYNAME PAYDATE SUM_FEE_MONEY SUM_BANKROLL_MONEY DEPNAME YM
---------------------------------------- ---------------- ----------- ------------- ------------------ ------------ ------
4 报柴油费 3/3/2007 200 1000 生产部 200703
5 报出车费 4/4/2007 50 100 生产部 200704
6 报党建费 5/6/2007 50 500 生产部 200705
6 报党建费 5/15/2007 100 100 生产部 200705
7 报出车费 5/6/2007 50 500 生产部 200705
7 报出车费 5/15/2007 100 100 生产部 200705
本月小计 报柴油费 200 1000 生产部 200703
本月小计 报出车费 50 100 生产部 200704
本月小计 报出车费 150 600 生产部 200705
本月小计 报党建费 150 600 生产部 20070510 rows selectedXD,如果还不是很明白就加我QQ吧: 307813421
Good luck to you!
tp.payname,
tp.paydate,
sum(tp.fee_money) over(partition by tp.payDate) as sum_fee_money,
sum(tp.bankroll_money) over(partition by tp.payDate) as sum_bankroll_money,
td.depname,
to_char(tp.payDate, 'yyyymm') as ym
from td_dep td,
td_pay tp
where td.depID = tp.depid
and td.depName = '生产部'
and to_char(tp.payDate, 'yyyymm') >= '200703'
and to_char(tp.payDate, 'yyyymm') <= '200705'
union all
select distinct
'本月小计' as payid,
null as payname,
null as paydate,
sum(tp.fee_money) over(partition by to_char(tp.payDate, 'yyyymm')) as sum_fee_money,
sum(tp.bankroll_money) over(partition by to_char(tp.payDate, 'yyyymm')) as sum_bankroll_money,
null as depname,
to_char(tp.payDate, 'yyyymm') as ym
from td_dep td,
td_pay tp
where td.depID = tp.depid
and td.depName = '生产部'
and to_char(tp.payDate, 'yyyymm') >= '200703'
and to_char(tp.payDate, 'yyyymm') <= '200705';