有一个表
2个字段
datadate,date
每天有一条数据
比如
2010-5-1 80
2010-5-2 90
.....
2010-5-31 84
2010-6-1 79
2010-6-2 87
一直到今天
2010-6-14 97
现在要求一个sql查出下面的结果
datadate pdata cdata
1 80 79
2 90 87
。
。
31 97 84我现场的查询会少了31号的数据,大家讨论一下
什么样才能把31号的数据一起查出来
2个字段
datadate,date
每天有一条数据
比如
2010-5-1 80
2010-5-2 90
.....
2010-5-31 84
2010-6-1 79
2010-6-2 87
一直到今天
2010-6-14 97
现在要求一个sql查出下面的结果
datadate pdata cdata
1 80 79
2 90 87
。
。
31 97 84我现场的查询会少了31号的数据,大家讨论一下
什么样才能把31号的数据一起查出来
现在是要一个sql查出2个月的对比数据来
pdata表示上月数据
cdata表示当月数据
select a.datadate as data,a.date as cdata,b.date as pdatafrom (select * from 表名 where datename(month,datadate)=datename(month,getdate())) aleft join (select * from 表名 where datename(month,datadate)=datename(month,getdate())-1) bon (a.datadate=b.datadate and datepart(dd,a.datadate)<=datepart(dd,getdate()))
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
比如4月份跟5月分最后一天的对比就丢了
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
看看还是这意思?
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;
(
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 * 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
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
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;