求一SQL简洁写法，谢谢！

显示全部楼层 · 2014-7-11 07:56:59

drop table tab_break_point;
create table TAB_BREAK_POINT
(
tab_namevarchar2(20),
BILLING_LINE_ID NUMBER(9),
POINT_VALUE NUMBER(15),
CREATE_DATE DATE
);
insert into tab_break_point values ('TEST1',1000123,0,sysdate);
insert into tab_break_point values ('TEST1',1000123,30,sysdate+1/20);
insert into tab_break_point values ('TEST1',1000123,90,sysdate+1/10);

我想取出tab_name和billing_line_id作为一个分组，我每次取出日期最新的记录的create_value值，该如何取
我写了这个，感觉太土了，有简洁一些的吗，谢谢！
比如当前我就是取90的值
selectpoint_value
from tab_break_point
where tab_name = 'TEST1'
and billing_line_id = '1000123'
and create_date in
(select create_date

from (select tab_name,

billing_line_id,

max(create_date) create_date

from tab_break_point wheretab_name = 'TEST1'

and billing_line_id = '1000123'

group by tab_name, billing_line_id));
[ 本帖最后由 wabjtam123 于 2009-9-17 16:10 编辑 ]

千问 · 2014-7-11 07:56:59

如果确实能保证最新的记录的create_value只有一个！
具体如下：
SQL> select tab_name,
2 billing_line_id,
3 point_value,
4 to_char(create_date, 'YYYY-MM-DD HH24:MI:SS') create_date
5from tab_break_point;
TAB_NAME BILLING_LINE_ID POINT_VALUE CREATE_DATE
------------ --------------- ----------- -------------------
TEST1
1000123 0 2009-09-17 16:53:12
TEST1
1000123
30 2009-09-17 18:05:12
TEST1
1000123
90 2009-09-17 19:17:12
SQL>
SQL> select tab_name,
2 billing_line_id,
3 max(point_value) keep(dense_rank first order by create_date desc) point_value,
4 to_char(max(create_date) keep(dense_rank first order by create_date desc), 'YYYY-MM-DD HH24:MI:SS') create_date
5from tab_break_point
6group by tab_name, billing_line_id;
TAB_NAME BILLING_LINE_ID POINT_VALUE CREATE_DATE
------------ --------------- ----------- -------------------
TEST1
1000123
90 2009-09-17 19:17:12
SQL>

[ 本帖最后由 bell6248 于 2009-9-17 16:58 编辑 ]

千问 · 2014-7-11 07:56:59

多谢，这个比我简洁多了！
从业务上来看，这个时间点一样几乎是不可能的，因为我这个记录跑完一个业务后，新增一条记录到这个断点记录表中。
跑完一个业务花费的时间不可能是0秒，所以这个重复就不可能了。
当然我是写存储过程的，用的是selectinto value来取最新的，如果出现两条程序就要出错了，所以这个表还是要做一个校验，在程序读这个表之前，先检查有没有时间完全重复的异常记录。

千问 · 2014-7-11 07:56:59

原帖由 wabjtam123 于 2009-9-17 16:51 发表
多谢，这个比我简洁多了！
从业务上来看，这个时间点一样几乎是不可能的，因为我这个记录跑完一个业务后，新增一条记录到这个断点记录表中。
跑完一个业务花费的时间不可能是0秒，所以这个重复就不可能了。
当然我是写存储过程的，用的是selectinto value来取最新的，如果出现两条程序就要出错了，所以这个表还是要做一个校验，在程序读这个表之前，先检查有没有时间完全重复的异常记录。

如果真的如你所述的，那还可以简化，具体语句见#2楼的！

千问 · 2014-7-11 07:56:59

原帖由 bell6248 于 2009-9-17 16:11 发表

如果确实能保证最新的记录的create_value只有一个！
具体如下：
SQL> select tab_name,
2 billing_line_id,
3 point_value,
4 to_char(create_date, 'YYYY-MM-DD HH24:MI:SS') create_date
5from tab_break_point;
TAB_NAME BILLING_LINE_ID POINT_VALUE CREATE_DATE
------------ --------------- ----------- -------------------
TEST1
1000123 0 2009-09-17 16:53:12
TEST1
1000123
30 2009-09-17 18:05:12
TEST1
1000123
90 2009-09-17 19:17:12
SQL>
SQL> select tab_name,
2 billing_line_id,
3 max(point_value) keep(dense_rank first order by create_date desc) point_value,
4 to_char(max(create_date) keep(dense_rank first order by create_date desc), 'YYYY-MM-DD HH24:MI:SS') create_date
5from tab_break_point
6group by tab_name, billing_line_id;
TAB_NAME BILLING_LINE_ID POINT_VALUE CREATE_DATE
------------ --------------- ----------- -------------------
TEST1
1000123
90 2009-09-17 19:17:12
SQL>

非常感谢
疑问：
1、你这个to_char是否是没必要的，这个create_date字段本来是date形有何不妥协呢？是否你只是因为SQLPLUS下无法展现时间精度？
2、为什么说只有确认了不重复才可以简化了？
3、迄今为止未曾用过分析函数中的KEEP，只用过OVER，不过这个不好意思作为疑问了，我找点资料恶补一下
[ 本帖最后由 wabjtam123 于 2009-9-17 19:03 编辑 ]

千问 · 2014-7-11 07:56:59

我明白这个KEEP写法含义了，就是取出最大的或者是最小排序的第一条记录或最后一条记录
不过实际上在代码中简化程度并不明显，呵呵，如下，注释部分和未注释部分代码几乎数量差不多
v_point_start number;
v_point_endnumber;
v_new_point_startnumber;
v_row number; /* 记录数 */
begin
/*
selectpoint_value into v_point_start
from
(select tab_name,
billing_line_id,
point_value,
create_date,
row_number() over(partition by tab_name, billing_line_id order by create_date desc) rn
from tab_break_point where TAB_NAME='TEST1' AND BILLING_LINE_ID=1000123)
where rn = 1;
*/
select point_value into v_point_start
from
(
select tab_name,
billing_line_id,
max(point_value) keep(dense_rank first order by create_date desc) point_value
from tab_break_point
where TAB_NAME='TEST1' AND BILLING_LINE_ID=1000123
group by tab_name, billing_line_id);

千问 · 2014-7-11 07:56:59

我以前也没用过这样的keep写法学习了

千问 · 2014-7-11 07:56:59

原帖由 bell6248 于 2009-9-17 16:11 发表

如果确实能保证最新的记录的create_value只有一个！
具体如下：
SQL> select tab_name,
2 billing_line_id,
3 point_value,
4 to_char(create_date, 'YYYY-MM-DD HH24:MI:SS') create_date
5from tab_break_point;
TAB_NAME BILLING_LINE_ID POINT_VALUE CREATE_DATE
------------ --------------- ----------- -------------------
TEST1
1000123 0 2009-09-17 16:53:12
TEST1
1000123
30 2009-09-17 18:05:12
TEST1
1000123
90 2009-09-17 19:17:12
SQL>
SQL> select tab_name,
2 billing_line_id,
3 max(point_value) keep(dense_rank first order by create_date desc) point_value,
4 to_char(max(create_date) keep(dense_rank first order by create_date desc), 'YYYY-MM-DD HH24:MI:SS') create_date
5from tab_break_point
6group by tab_name, billing_line_id;
TAB_NAME BILLING_LINE_ID POINT_VALUE CREATE_DATE
------------ --------------- ----------- -------------------
TEST1
1000123
90 2009-09-17 19:17:12
SQL>

你这个可以简化：
to_char(max(create_date) keep(dense_rank first order by create_date desc), 'YYYY-MM-DD HH24:MI:SS') create_date
不就是MAX(create_date)吗？
本例中只有一个point_value，如果有其他列用KEEP, 不能保证它们是同一行的。这时得用分析函数。
如果CREATE_DATE有重复的最大值而且这些行都必须选中，用RANK()

千问 · 2014-7-11 07:56:59

select point_value from(

select a.tab_name,

a.billing_line_id,

point_value,

rank() over(partition by a.tab_name, a.billing_line_id order by create_date desc) as ranknum

from tab_break_point a) where ranknum=1

千问 · 2014-7-11 07:56:59

原帖由 newkid 于 2009-9-17 21:42 发表
你这个可以简化：
to_char(max(create_date) keep(dense_rank first order by create_date desc), 'YYYY-MM-DD HH24:MI:SS') create_date
不就是MAX(create_date)吗？
本例中只有一个point_value，如果有其他列用KEEP, 不能保证它们是同一行的。这时得用分析函数。
如果CREATE_DATE有重复的最大值而且这些行都必须选中，用RANK()
哦，确实是，不过我还进一步简化了，这个CREATE_DATE字段根本无需展现，因为我只要POINT_VALUE值就够了

select point_value
into v_point_start
from (select tab_name,

billing_line_id,

max(point_value) keep(dense_rank first order by create_date desc) point_value

from tab_break_point
where TAB_NAME = 'TEST1'

AND BILLING_LINE_ID = 1000123
group by tab_name, billing_line_id);