分析函数
文章平均质量分 58
jgmydsai
道森oracle讲师,课程:sql高级查询改写
展开
-
一次分析函数的应用
下面是一个网友的问题 问:表test(yyyymmdd,price) 按yyyymmdd--yyyymmdd+3,sum(price) 做统计,sql怎么写? 例如: 20131101--20131103 100 20131102--20131104 105 20131103--20131105 95 于是生成环境及语句如下: SQL> DROP TABLE TEST原创 2013-11-07 20:09:57 · 932 阅读 · 0 评论 -
用分析函数优化标量子查询
以下语句表名及部分字段作过替换处理 SELECT ii.*, CASE WHEN (SELECT COUNT(1) FROM ii ii WHERE ii.id > 0 AND ii.itemtranflag = 2原创 2013-11-12 14:48:29 · 1504 阅读 · 0 评论 -
用分析函数去掉标量子查询
原语句如下(里面字段及表名称替换过的) SELECT A.CODE AS CODE, A.M_CODE AS M_CODE, A.STKTYPE AS F_STYPE, A.E_YEAR AS E_YEAR, B.SNAME AS SNAME, A.C_DATE AS C_DATE, TO_CHAR(SYSDAT原创 2014-03-04 13:55:13 · 1445 阅读 · 0 评论 -
小九九
with l as (select level as lv from dual connect by level <= 9), m as (select a.lv as lv_a, b.lv as lv_b, to_char(b.lv) || ' × ' || to_char(a.lv) || ' = ' || rpad(to_char(a原创 2013-08-23 17:03:34 · 868 阅读 · 0 评论 -
LISTAGG之取索引对应列名称
SQL> select i.TABLE_NAME, 2 i.INDEX_NAME, 3 (select listagg(uc.COLUMN_NAME, ',') within group(order by uc.COLUMN_POSITION) 4 from user_ind_columns uc 5 whe原创 2013-08-12 22:40:51 · 1196 阅读 · 0 评论