1、select * from recharge where loddate in
(select max(loddate) from recharge where to_char(loddate,'yyyy')<'2007'
有4条2006年记录;
2、select * from recharge where loddate in
(select max(loddate) from recharge where to_char(loddate,'yyyy')='2007'
有5条2007年的记录
3、select * from recharge where loddate in
(select max(loddate) from recharge where to_char(loddate,'yyyy')<='2007'
应该有9条记录才对,可是这条语句的统计结果和第2条一样,只统计出2007年的5条记录,没有统计出2006年的记录,为什么会这样,是我什么地方写的不对吗?
(select max(loddate) from recharge where to_char(loddate,'yyyy')<'2007'
有4条2006年记录;
2、select * from recharge where loddate in
(select max(loddate) from recharge where to_char(loddate,'yyyy')='2007'
有5条2007年的记录
3、select * from recharge where loddate in
(select max(loddate) from recharge where to_char(loddate,'yyyy')<='2007'
应该有9条记录才对,可是这条语句的统计结果和第2条一样,只统计出2007年的5条记录,没有统计出2006年的记录,为什么会这样,是我什么地方写的不对吗?
条件是<='2007',这样select出来的max(loddate)还是2007啊。
当然和你第2条的结果是一样的啦(第2条的max(loddate)也是2007);
而你1条的max(loddate)是2006
select * from recharge where loddate in
(select max(loddate) from recharge where to_char(loddate,'yyyy')<'2007'
union
select * from recharge where loddate in
(select max(loddate) from recharge where to_char(loddate,'yyyy')='2007' 在此在指出楼主的一个错误,你的三条SQL达成的效果,并不是象你所理解的查询某年的记录,而是查询某年最大时间点的记录。
比如2006年有5条记录:
2006-1-1
2006-1-2
2006-1-3
2006-12-31
2006-12-31
使用你的SQL返回的是2006-12-31的两条记录,而不是5条记录。
select * from recharge where loddate in
(select distinct loddate from recharge where to_char(loddate,'yyyy')<='2007'
drop table test;
create table test
(
time date
);insert into test values(to_date('2006-01-01'
,'yyyy-mm-dd'));
insert into test values(to_date('2006-02-01'
,'yyyy-mm-dd'));
insert into test values(to_date('2006-03-01'
,'yyyy-mm-dd'));
insert into test values(to_date('2006-04-01'
,'yyyy-mm-dd'));
insert into test values(to_date('2007-05-01'
,'yyyy-mm-dd'));
insert into test values(to_date('2007-06-01'
,'yyyy-mm-dd'));
insert into test values(to_date('2007-07-01'
,'yyyy-mm-dd'));
insert into test values(to_date('2007-08-01'
,'yyyy-mm-dd'));
insert into test values(to_date('2007-09-01'
,'yyyy-mm-dd'));
select * from test where to_char(time,'yyyy') in
(select max(to_char(time,'yyyy')) from test
where to_char(time,'yyyy')<'2007');
第一条: 从年份中找出小余2007的最大年份,也就是2006为最大年份,
接着从条件在最大年份2006中查询信息,也就是输出4条记录。
select * from test where to_char(time,'yyyy') in
(select max(to_char(time,'yyyy')) from test
where to_char(time,'yyyy')='2007');
第二条:和第一条相似,只是最大年份变成了2007。结果输出5条记录
select * from test where to_char(time,'yyyy') in
(select max(to_char(time,'yyyy')) from test
where to_char(time,'yyyy')<='2007');
第三条:和第二条完全没有区别。要想输出9条记录:
select * from test where to_char(time,'yyyy') in
(select to_char(time,'yyyy') from test
where to_char(time,'yyyy')<='2007');
去掉max函数就可以了!!楼主你自己多想想!!
如:有一张数据表(recharge)如下:
card loddate lodamt
1510111 20060101 60
1510222 20070101 80
1511311 20060201 70
1511311 20060602 80
1511311 20080821 90
1511322 20070302 100
1511322 20071105 120
1512311 20080602 100
1512311 20090710 50
红色部分就是我想得到的结果(去掉card<=1510999),各位想想该怎么做
MAX
最大 OK?
loddate<=2007最大不就是2007了嘛
(select max(loddate) from recharge where to_char(loddate) like '2006%'))
union
(select * from recharge where loddate in
(select max(loddate) from recharge where to_char(loddate) like '2007%'))
(select max(loddate) from recharge group by to_char(loddate,'yyyy');
假设你要查的是连续某几年的最后时间点的数据:select * from recharge where loddate in
(select max(loddate) from recharge where loddate > :sDT and loddate < :eDT group by to_char(loddate,'yyyy');
假设你要查的是任意拽定的某几个年份的最后时间点的数据:select * from recharge where loddate in
(select max(loddate) from recharge where to_char(loddate,'yyyy')
in ('2006','2007','2009') group by to_char(loddate,'yyyy')
楼主你提的需求就是想得到最后时间点的数据啊。如果是要得到所有时间点的数据就没有必要这么复杂了,简单的select * from where 就可以查询了。不知道楼主你实际上是想要得到什么样的数据呢?
(select max(loddate) from recharge where to_char(loddate,'yyyy')<='2007'
group by to_char(loddate,'yyyy'))
1510111 20060101 60
1510222 20070101 80
1511311 20060201 70
1511311 20060602 80
1511311 20080821 90
1511322 20070302 100
1511322 20071105 120
1512311 20080602 100
1512311 20090710 50
注意:
1、1510xxxx不在统计范围
2、1511311,在2007年时没有更新,因此数据和2006年一样
3、1512311,在2008年之前没有记录,因此不需要统计
loddate<=to_date('2007-12-31 23:59:59','yyyy-mm-dd hh24:mi:ss'),这样可以用上索引.
稍微优化后可以这样实现:SELECT *
FROM RECHARGE
WHERE LODDATE IN
(SELECT MAX(LODDATE) LODDATE
FROM RECHARGE
WHERE LODDATE <=
TO_DATE('2007-12-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
AND LODDATE >= TO_DATE('2007-01-01', 'yyyy-mm-dd')
UNION ALL
SELECT MAX(LODDATE) LODDATE
FROM RECHARGE
WHERE LODDATE <=
TO_DATE('2006-12-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
AND LODDATE >= TO_DATE('2006-01-01', 'yyyy-mm-dd'))
SELECT *
FROM recharge
WHERE (ID,LODDATE) IN
(SELECT ID,MAX(LADDATE) FROM recharge GROUP BY ID);