62,614
社区成员
发帖
与我相关
我的任务
分享
SELECT * FROM (SELECT a.p_date p_d, a.p_number pdata, b.p_date c_d, b.p_number cdata FROM test_date a LEFT JOIN test_date b
ON a.p_date = add_months(b.p_date,-1)
ORDER BY a.p_date) t WHERE t.p_d >= trunc(to_date('2010-04-17','yyyy-mm-dd'),'month')
AND t.p_d <= add_months(trunc(to_date('2010-04-17','yyyy-mm-dd'),'month'),1)-1;
SELECT * FROM (SELECT a.p_date p_d, a.p_number pdata, b.p_date c_d, b.p_number cdata FROM test_date a LEFT JOIN test_date b
ON a.p_date = add_months(b.p_date,-1)
ORDER BY a.p_date) t WHERE t.p_d >= to_date('2010-05-01','yyyy-mm-dd')
AND t.p_d <= to_date('2010-05-31','yyyy-mm-dd');
1 2010-5-1 20 2010-6-1 22
2 2010-5-2 30 2010-6-2 51
3 2010-5-3 50
4 2010-5-4 50
5 2010-5-5 50 2010-6-5 33
6 2010-5-6 50
7 2010-5-7 50
8 2010-5-8 50
9 2010-5-11 60 2010-6-11 55
10 2010-5-12 70
11 2010-5-21 50 2010-6-21 50
12 2010-5-22 50
13 2010-5-31 90 2010-6-30 61
SELECT * FROM (SELECT a.p_date p_d, a.p_number pdata, b.p_date c_d, b.p_number cdata FROM test_date a LEFT JOIN test_date b
ON a.p_date = add_months(b.p_date,-1)
ORDER BY a.p_date) t WHERE t.p_d >= to_date('2010-04-01','yyyy-mm-dd')
AND t.p_d <= to_date('2010-04-30','yyyy-mm-dd');
1 2010-4-1 10 2010-5-1 20
2 2010-4-2 30 2010-5-2 30
3 2010-4-3 50 2010-5-3 50
4 2010-4-30 30 2010-5-31 90
with t1 as
(
select '1' day from dual
union all
select '2' day from dual
union all
select '3' day from dual
....
union all
select '31' day from dual
)
select * from t1 a
left (上月份数据) b on to_char(b.date,'dd')=a.day
left (指定月份数据) c on to_char(c.date,'dd')=a.day
where 过滤条件
SELECT a.p_date, a.p_number pdata, b.p_date, b.p_number cdata FROM test_date a LEFT JOIN test_date b
ON a.p_date = add_months(b.p_date,-1)
ORDER BY a.p_date;
INSERT INTO test_date VALUES(to_date('2010-03-31','yyyy-mm-dd'),20);
INSERT INTO test_date VALUES(to_date('2010-04-30','yyyy-mm-dd'),30);
COMMIT;
1 2010-3-31 20
2 2010-4-1 10
3 2010-4-2 30
4 2010-4-3 50
5 2010-4-30 30 2010-3-31 20
6 2010-5-1 20 2010-4-1 10
7 2010-5-2 30 2010-4-2 30
8 2010-5-3 50 2010-4-3 50
9 2010-5-4 50
10 2010-5-5 50
11 2010-5-6 50
12 2010-5-7 50
13 2010-5-8 50
14 2010-5-11 60
15 2010-5-12 70
16 2010-5-21 50
17 2010-5-22 50
18 2010-5-31 90 2010-4-30 30
19 2010-6-1 22 2010-5-1 20
20 2010-6-2 51 2010-5-2 30
21 2010-6-5 33 2010-5-5 50
22 2010-6-11 55 2010-5-11 60
23 2010-6-21 50 2010-5-21 50
24 2010-6-30 61 2010-5-31 90
CREATE TABLE test_date
(
p_date DATE,
p_number NUMBER
);
INSERT INTO test_date VALUES(to_date('2010-04-01','yyyy-mm-dd'),10);
INSERT INTO test_date VALUES(to_date('2010-04-02','yyyy-mm-dd'),30);
INSERT INTO test_date VALUES(to_date('2010-04-03','yyyy-mm-dd'),50);
INSERT INTO test_date VALUES(to_date('2010-05-01','yyyy-mm-dd'),20);
INSERT INTO test_date VALUES(to_date('2010-05-02','yyyy-mm-dd'),30);
INSERT INTO test_date VALUES(to_date('2010-05-03','yyyy-mm-dd'),50);
INSERT INTO test_date VALUES(to_date('2010-05-04','yyyy-mm-dd'),50);
INSERT INTO test_date VALUES(to_date('2010-05-05','yyyy-mm-dd'),50);
INSERT INTO test_date VALUES(to_date('2010-05-06','yyyy-mm-dd'),50);
INSERT INTO test_date VALUES(to_date('2010-05-07','yyyy-mm-dd'),50);
INSERT INTO test_date VALUES(to_date('2010-05-08','yyyy-mm-dd'),50);
INSERT INTO test_date VALUES(to_date('2010-05-11','yyyy-mm-dd'),60);
INSERT INTO test_date VALUES(to_date('2010-05-12','yyyy-mm-dd'),70);
INSERT INTO test_date VALUES(to_date('2010-05-21','yyyy-mm-dd'),50);
INSERT INTO test_date VALUES(to_date('2010-05-22','yyyy-mm-dd'),50);
INSERT INTO test_date VALUES(to_date('2010-05-31','yyyy-mm-dd'),90);
INSERT INTO test_date VALUES(to_date('2010-06-01','yyyy-mm-dd'),22);
INSERT INTO test_date VALUES(to_date('2010-06-02','yyyy-mm-dd'),51);
INSERT INTO test_date VALUES(to_date('2010-06-05','yyyy-mm-dd'),33);
INSERT INTO test_date VALUES(to_date('2010-06-11','yyyy-mm-dd'),55);
INSERT INTO test_date VALUES(to_date('2010-06-21','yyyy-mm-dd'),50);
INSERT INTO test_date VALUES(to_date('2010-06-30','yyyy-mm-dd'),61);
COMMIT;
SELECT a.p_date, a.p_number pdata, b.p_date, b.p_number cdata FROM test_date a LEFT JOIN test_date b
ON a.p_date = add_months(b.p_date,1)
ORDER BY a.p_date;
1 2010-4-1 10
2 2010-4-2 30
3 2010-4-3 50
4 2010-5-1 20 2010-4-1 10
5 2010-5-2 30 2010-4-2 30
6 2010-5-3 50 2010-4-3 50
7 2010-5-4 50
8 2010-5-5 50
9 2010-5-6 50
10 2010-5-7 50
11 2010-5-8 50
12 2010-5-11 60
13 2010-5-12 70
14 2010-5-21 50
15 2010-5-22 50
16 2010-5-31 90
17 2010-6-1 22 2010-5-1 20
18 2010-6-2 51 2010-5-2 30
19 2010-6-5 33 2010-5-5 50
20 2010-6-11 55 2010-5-11 60
21 2010-6-21 50 2010-5-21 50
22 2010-6-30 61 2010-5-31 90