有两个表,一个是部门表,一个预算表表结构如下
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
我想查出来的结果是这样的:
payid payname payDate fee_money bankroll_money depname
1 报出车费 2007-01-02 100.50 200.5 生产部
本月小计 100.5 200.5
3 报柴油费 2007-02-24 500 1000 生产部
本月小计 500 1000
4 报柴油费 2007-03-03 200 1000 生产部
本月小计 200 1000
5 报出车费 2007-04-04 50 100 生产部
本月小计 50 100
6 报党建费 2007-05-06 50 500 生产部
7 报出车费 2007-05-15 100 100 生产部
本月小计 150 600
请问这个SQL语句该怎么写?
难住我很久了,如解决万分感谢!
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
我想查出来的结果是这样的:
payid payname payDate fee_money bankroll_money depname
1 报出车费 2007-01-02 100.50 200.5 生产部
本月小计 100.5 200.5
3 报柴油费 2007-02-24 500 1000 生产部
本月小计 500 1000
4 报柴油费 2007-03-03 200 1000 生产部
本月小计 200 1000
5 报出车费 2007-04-04 50 100 生产部
本月小计 50 100
6 报党建费 2007-05-06 50 500 生产部
7 报出车费 2007-05-15 100 100 生产部
本月小计 150 600
请问这个SQL语句该怎么写?
难住我很久了,如解决万分感谢!
2 (SELECT 1 ,'生产部' FROM DUAL) UNION ALL
3 (SELECT 2 ,'办公室' FROM DUAL) UNION ALL
4 (SELECT 3 ,'质检站' FROM DUAL) ;3 rows insertedSQL> DROP TABLE td_pay;Table droppedSQL> CREATE TABLE td_pay(payID NUMBER,
2 payName VARCHAR2(10),
3 payDate DATE,
4 fee_money NUMBER,
5 bankroll_money NUMBER,
6 depid NUMBER);Table createdSQL> INSERT INTO td_pay
2 (SELECT 1, '报出车费', TO_DATE('2007-01-02','YYYY-MM-DD'), 100.50, 200.5 ,1 FROM DUAL) UNION ALL
3 (SELECT 2 , '报招待费', TO_DATE('2007-01-04','YYYY-MM-DD'), 50, 100, 2 FROM DUAL) UNION ALL
4 (SELECT 3 , '报柴油费', TO_DATE('2007-02-24','YYYY-MM-DD'), 500 , 1000, 1 FROM DUAL) UNION ALL
5 (SELECT 4 , '报柴油费', TO_DATE('2007-03-03','YYYY-MM-DD'), 200 , 1000, 1 FROM DUAL) UNION ALL
6 (SELECT 5 , '报出车费', TO_DATE('2007-04-04','YYYY-MM-DD'), 50 , 100, 1 FROM DUAL) UNION ALL
7 (SELECT 6 , '报党建费', TO_DATE('2007-05-06','YYYY-MM-DD'), 50 , 500, 1 FROM DUAL) UNION ALL
8 (SELECT 7 , '报出车费', TO_DATE('2007-05-15','YYYY-MM-DD'), 100 , 100 ,1 FROM DUAL) ;7 rows insertedSQL> select payid1 as payid,payname,paydate,fee_money,bankroll_money,depname from
2 (
3 select * from
4 (
5 SELECT to_char(payid) AS payid1, TO_CHAR(A.PAYDATE,'YYYY-MM') AS PAY_MON,A.payID,payName,payDate,fee_money ,bankroll_money,depname FROM td_pay A, TD_DEP B
6 WHERE A.DEPID = B.DEPID
7 UNION ALL
8 SELECT '本月小计' as payid1, TO_CHAR(A.PAYDATE,'YYYY-MM') AS PAY_MON,null,'',null,sum(fee_money) ,sum(bankroll_money),'' FROM td_pay A, TD_DEP B
9 WHERE A.DEPID = B.DEPID group by TO_CHAR(A.PAYDATE,'YYYY-MM')
10 ) order by pay_mon,payid1
11 );PAYID PAYNAME PAYDATE FEE_MONEY BANKROLL_MONEY DEPNAME
---------------------------------------- ---------- ----------- --------- -------------- ----------
1 报出车费 2007-1-2 100.5 200.5 生产部
2 报招待费 2007-1-4 50 100 办公室
本月小计 150.5 300.5
3 报柴油费 2007-2-24 500 1000 生产部
本月小计 500 1000
4 报柴油费 2007-3-3 200 1000 生产部
本月小计 200 1000
5 报出车费 2007-4-4 50 100 生产部
本月小计 50 100
6 报党建费 2007-5-6 50 500 生产部
7 报出车费 2007-5-15 100 100 生产部
本月小计 150 600 12 rows selected
我这里是随便去的一个最小的,按你自己的需要改select min(payID),payname,paydate,sum(fee_money),sum(bankroll_money),td_dep.depName
from td_pay
left outer join td_dep on td_dep.depID= td_pay.depID
group by to_char(paydate,'yyyy-mm'),td_dep.depName,payname
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';
一楼的代码,我试了,可以,重新贴出DROP TABLE TD_DEP; CREATE TABLE td_dep (depID NUMBER,depName VARCHAR2(10)); INSERT INTO td_dep (depID ,depName)
(SELECT 1 , '生产部' FROM DUAL)
UNION ALL
(SELECT 2 ,'办公室' FROM DUAL)
UNION ALL
(SELECT 3 ,'质检站' FROM DUAL) ; DROP TABLE td_pay;
CREATE TABLE td_pay(payID NUMBER,
payName VARCHAR2(10),
payDate DATE,
fee_money NUMBER,
bankroll_money NUMBER,
depid NUMBER); INSERT INTO td_pay
(SELECT 1,'报出车费',TO_DATE('2007-01-02','YYYY-MM-DD'), 100.50, 200.5 ,1 FROM DUAL)
UNION ALL
(SELECT 2,'报招待费',TO_DATE('2007-01-04','YYYY-MM-DD'), 50, 100, 2 FROM DUAL)
UNION ALL
(SELECT 3 ,'报柴油费',TO_DATE('2007-02-24','YYYY-MM-DD'), 500 , 1000, 1 FROM DUAL)
UNION ALL
(SELECT 4 ,'报柴油费',TO_DATE( '2007-03-03','YYYY-MM-DD'), 200 , 1000, 1 FROM DUAL)
UNION ALL
(SELECT 5 ,'报出车费',TO_DATE('2007-04-04','YYYY-MM-DD'), 50 , 100, 1 FROM DUAL)
UNION ALL
(SELECT 6,'报党建费',TO_DATE('2007-05-06','YYYY-MM-DD'), 50 , 500, 1 FROM DUAL)
UNION ALL
(SELECT 7,'报出车费',TO_DATE('2007-05-15','YYYY-MM-DD'), 100 , 100 ,1 FROM DUAL) ; select payid1 as payid,payname,paydate,fee_money,bankroll_money,depname
from
(
select * from
(
SELECT to_char(payid) AS payid1,TO_CHAR(A.PAYDATE, 'YYYY-MM') AS PAY_MON,
A.payID,payName,payDate,fee_money,
bankroll_money,depname
FROM td_pay A, TD_DEP B
WHERE A.DEPID = B.DEPID
UNION ALL
SELECT '本月小计' as payid1, TO_CHAR(A.PAYDATE, 'YYYY-MM') AS PAY_MON,
null, '',null,sum(fee_money) ,sum(bankroll_money),''
FROM td_pay A, TD_DEP B
WHERE A.DEPID = B.DEPID
group by TO_CHAR(A.PAYDATE, 'YYYY-MM')
) order by pay_mon,payid1
);