xinkong1010
- 浏览: 261371 次
- 性别:
- 来自: 长沙
-
文章分类
最新评论
-
ae6623：
楼主，没必要用json2.js，jquery自己就可以去解析j ...
JQuery、JSON、Ajax在Servlet中的应用 -
hw1287789687：
http://ljhzzyx.blog.163.com/blo ...
MyEclipse中如何去掉JS/JSP 语法错误提示 -
xiaokang1582830：
不知所云...
处理 Struts2 框架上传的文件 -
halfwaters：
使用vs2010,加上扩展管理器安装的js插件
MyEclipse中如何去掉JS/JSP 语法错误提示 -
ayumi11111：
附件呢 我····
jQuery+Ajax+Struts2. js javascript
/* Formatted on 2011/04/19 20:40 (Formatter Plus v4.8.6) */
SELECT bill_type billtype, query_type querytype,
materiel_type_id materieltypeid, z0000 z0000, z0731 z0731,
z0733 z0733, z0732 z0732, z0734 z0734, z0737 z0737, z0730 z0730,
z0736 z0736, z0744 z0744, z0746 z0746, z0745 z0745, z0738 z0738,
z0739 z0739, z0735 z0735, z0743 z0743
FROM (SELECT '0' query_type, v.bill_type, v.materiel_type_id,
SUM (DECODE (DECODE (v.area_id, '0000', v.materiel_num),
'', 0,
v.materiel_num
)
) z0000,
SUM (DECODE (DECODE (v.area_id, '0731', v.materiel_num),
'', 0,
v.materiel_num
)
) z0731,
SUM (DECODE (DECODE (v.area_id, '0733', v.materiel_num),
'', 0,
v.materiel_num
)
) z0733,
SUM (DECODE (DECODE (v.area_id, '0732', v.materiel_num),
'', 0,
v.materiel_num
)
) z0732,
SUM (DECODE (DECODE (v.area_id, '0734', v.materiel_num),
'', 0,
v.materiel_num
)
) z0734,
SUM (DECODE (DECODE (v.area_id, '0737', v.materiel_num),
'', 0,
v.materiel_num
)
) z0737,
SUM (DECODE (DECODE (v.area_id, '0730', v.materiel_num),
'', 0,
v.materiel_num
)
) z0730,
SUM (DECODE (DECODE (v.area_id, '0736', v.materiel_num),
'', 0,
v.materiel_num
)
) z0736,
SUM (DECODE (DECODE (v.area_id, '0744', v.materiel_num),
'', 0,
v.materiel_num
)
) z0744,
SUM (DECODE (DECODE (v.area_id, '0746', v.materiel_num),
'', 0,
v.materiel_num
)
) z0746,
SUM (DECODE (DECODE (v.area_id, '0745', v.materiel_num),
'', 0,
v.materiel_num
)
) z0745,
SUM (DECODE (DECODE (v.area_id, '0738', v.materiel_num),
'', 0,
v.materiel_num
)
) z0738,
SUM (DECODE (DECODE (v.area_id, '0739', v.materiel_num),
'', 0,
v.materiel_num
)
) z0739,
SUM (DECODE (DECODE (v.area_id, '0735', v.materiel_num),
'', 0,
v.materiel_num
)
) z0735,
SUM (DECODE (DECODE (v.area_id, '0743', v.materiel_num),
'', 0,
v.materiel_num
)
) z0743
FROM v_inout_month_report v
WHERE 1 = 1
AND ( v.districtid IN (
SELECT man_obj_id
FROM td_sge_keeping_ref
WHERE keeping_id = 'csck1'
AND manager_level = 1)
OR v.storehouse_id IN (
SELECT man_obj_id
FROM td_sge_keeping_ref
WHERE keeping_id = 'csck1'
AND manager_level = 0)
)
GROUP BY v.materiel_type_id, v.bill_type
UNION
SELECT '1' query_type, bill_type, materiel_type_id,
SUM (DECODE (a.area_id, '0000', 1, 0)) z0000,
SUM (DECODE (a.area_id, '0731', 1, 0)) z0731,
SUM (DECODE (a.area_id, '0733', 1, 0)) z0733,
SUM (DECODE (a.area_id, '0732', 1, 0)) z0732,
SUM (DECODE (a.area_id, '0734', 1, 0)) z0734,
SUM (DECODE (a.area_id, '0737', 1, 0)) z0737,
SUM (DECODE (a.area_id, '0730', 1, 0)) z0730,
SUM (DECODE (a.area_id, '0736', 1, 0)) z0736,
SUM (DECODE (a.area_id, '0744', 1, 0)) z0744,
SUM (DECODE (a.area_id, '0746', 1, 0)) z0746,
SUM (DECODE (a.area_id, '0745', 1, 0)) z0745,
SUM (DECODE (a.area_id, '0738', 1, 0)) z0738,
SUM (DECODE (a.area_id, '0739', 1, 0)) z0739,
SUM (DECODE (a.area_id, '0735', 1, 0)) z0735,
SUM (DECODE (a.area_id, '0743', 1, 0)) z0743
FROM (SELECT DISTINCT v.area_id, v.bill_type, v.bill_id,
v.materiel_type_id
FROM v_inout_month_report v
WHERE 1 = 1
AND ( v.districtid IN (
SELECT man_obj_id
FROM td_sge_keeping_ref
WHERE keeping_id = 'csck1'
AND manager_level = 1)
OR v.storehouse_id IN (
SELECT man_obj_id
FROM td_sge_keeping_ref
WHERE keeping_id = 'csck1'
AND manager_level = 0)
)) a
GROUP BY a.bill_type, materiel_type_id
UNION
SELECT '2' query_type, bill_type, materiel_type_id,
SUM (DECODE (a.area_id, '0000', 1, 0)) z0000,
SUM (DECODE (a.area_id, '0731', 1, 0)) z0731,
SUM (DECODE (a.area_id, '0733', 1, 0)) z0733,
SUM (DECODE (a.area_id, '0732', 1, 0)) z0732,
SUM (DECODE (a.area_id, '0734', 1, 0)) z0734,
SUM (DECODE (a.area_id, '0737', 1, 0)) z0737,
SUM (DECODE (a.area_id, '0730', 1, 0)) z0730,
SUM (DECODE (a.area_id, '0736', 1, 0)) z0736,
SUM (DECODE (a.area_id, '0744', 1, 0)) z0744,
SUM (DECODE (a.area_id, '0746', 1, 0)) z0746,
SUM (DECODE (a.area_id, '0745', 1, 0)) z0745,
SUM (DECODE (a.area_id, '0738', 1, 0)) z0738,
SUM (DECODE (a.area_id, '0739', 1, 0)) z0739,
SUM (DECODE (a.area_id, '0735', 1, 0)) z0735,
SUM (DECODE (a.area_id, '0743', 1, 0)) z0743
FROM (SELECT DISTINCT v.materiel_id, v.materiel_type_id,
v.area_id, v.bill_type
FROM v_inout_month_report v
WHERE 1 = 1
AND ( v.districtid IN (
SELECT man_obj_id
FROM td_sge_keeping_ref
WHERE keeping_id = 'csck1'
AND manager_level = 1)
OR v.storehouse_id IN (
SELECT man_obj_id
FROM td_sge_keeping_ref
WHERE keeping_id = 'csck1'
AND manager_level = 0)
)) a
GROUP BY materiel_type_id, bill_type)
ORDER BY bill_type, query_type, materiel_type_id
SELECT bill_type billtype, query_type querytype,
materiel_type_id materieltypeid, z0000 z0000, z0731 z0731,
z0733 z0733, z0732 z0732, z0734 z0734, z0737 z0737, z0730 z0730,
z0736 z0736, z0744 z0744, z0746 z0746, z0745 z0745, z0738 z0738,
z0739 z0739, z0735 z0735, z0743 z0743
FROM (SELECT '0' query_type, v.bill_type, v.materiel_type_id,
SUM (DECODE (DECODE (v.area_id, '0000', v.materiel_num),
'', 0,
v.materiel_num
)
) z0000,
SUM (DECODE (DECODE (v.area_id, '0731', v.materiel_num),
'', 0,
v.materiel_num
)
) z0731,
SUM (DECODE (DECODE (v.area_id, '0733', v.materiel_num),
'', 0,
v.materiel_num
)
) z0733,
SUM (DECODE (DECODE (v.area_id, '0732', v.materiel_num),
'', 0,
v.materiel_num
)
) z0732,
SUM (DECODE (DECODE (v.area_id, '0734', v.materiel_num),
'', 0,
v.materiel_num
)
) z0734,
SUM (DECODE (DECODE (v.area_id, '0737', v.materiel_num),
'', 0,
v.materiel_num
)
) z0737,
SUM (DECODE (DECODE (v.area_id, '0730', v.materiel_num),
'', 0,
v.materiel_num
)
) z0730,
SUM (DECODE (DECODE (v.area_id, '0736', v.materiel_num),
'', 0,
v.materiel_num
)
) z0736,
SUM (DECODE (DECODE (v.area_id, '0744', v.materiel_num),
'', 0,
v.materiel_num
)
) z0744,
SUM (DECODE (DECODE (v.area_id, '0746', v.materiel_num),
'', 0,
v.materiel_num
)
) z0746,
SUM (DECODE (DECODE (v.area_id, '0745', v.materiel_num),
'', 0,
v.materiel_num
)
) z0745,
SUM (DECODE (DECODE (v.area_id, '0738', v.materiel_num),
'', 0,
v.materiel_num
)
) z0738,
SUM (DECODE (DECODE (v.area_id, '0739', v.materiel_num),
'', 0,
v.materiel_num
)
) z0739,
SUM (DECODE (DECODE (v.area_id, '0735', v.materiel_num),
'', 0,
v.materiel_num
)
) z0735,
SUM (DECODE (DECODE (v.area_id, '0743', v.materiel_num),
'', 0,
v.materiel_num
)
) z0743
FROM v_inout_month_report v
WHERE 1 = 1
AND ( v.districtid IN (
SELECT man_obj_id
FROM td_sge_keeping_ref
WHERE keeping_id = 'csck1'
AND manager_level = 1)
OR v.storehouse_id IN (
SELECT man_obj_id
FROM td_sge_keeping_ref
WHERE keeping_id = 'csck1'
AND manager_level = 0)
)
GROUP BY v.materiel_type_id, v.bill_type
UNION
SELECT '1' query_type, bill_type, materiel_type_id,
SUM (DECODE (a.area_id, '0000', 1, 0)) z0000,
SUM (DECODE (a.area_id, '0731', 1, 0)) z0731,
SUM (DECODE (a.area_id, '0733', 1, 0)) z0733,
SUM (DECODE (a.area_id, '0732', 1, 0)) z0732,
SUM (DECODE (a.area_id, '0734', 1, 0)) z0734,
SUM (DECODE (a.area_id, '0737', 1, 0)) z0737,
SUM (DECODE (a.area_id, '0730', 1, 0)) z0730,
SUM (DECODE (a.area_id, '0736', 1, 0)) z0736,
SUM (DECODE (a.area_id, '0744', 1, 0)) z0744,
SUM (DECODE (a.area_id, '0746', 1, 0)) z0746,
SUM (DECODE (a.area_id, '0745', 1, 0)) z0745,
SUM (DECODE (a.area_id, '0738', 1, 0)) z0738,
SUM (DECODE (a.area_id, '0739', 1, 0)) z0739,
SUM (DECODE (a.area_id, '0735', 1, 0)) z0735,
SUM (DECODE (a.area_id, '0743', 1, 0)) z0743
FROM (SELECT DISTINCT v.area_id, v.bill_type, v.bill_id,
v.materiel_type_id
FROM v_inout_month_report v
WHERE 1 = 1
AND ( v.districtid IN (
SELECT man_obj_id
FROM td_sge_keeping_ref
WHERE keeping_id = 'csck1'
AND manager_level = 1)
OR v.storehouse_id IN (
SELECT man_obj_id
FROM td_sge_keeping_ref
WHERE keeping_id = 'csck1'
AND manager_level = 0)
)) a
GROUP BY a.bill_type, materiel_type_id
UNION
SELECT '2' query_type, bill_type, materiel_type_id,
SUM (DECODE (a.area_id, '0000', 1, 0)) z0000,
SUM (DECODE (a.area_id, '0731', 1, 0)) z0731,
SUM (DECODE (a.area_id, '0733', 1, 0)) z0733,
SUM (DECODE (a.area_id, '0732', 1, 0)) z0732,
SUM (DECODE (a.area_id, '0734', 1, 0)) z0734,
SUM (DECODE (a.area_id, '0737', 1, 0)) z0737,
SUM (DECODE (a.area_id, '0730', 1, 0)) z0730,
SUM (DECODE (a.area_id, '0736', 1, 0)) z0736,
SUM (DECODE (a.area_id, '0744', 1, 0)) z0744,
SUM (DECODE (a.area_id, '0746', 1, 0)) z0746,
SUM (DECODE (a.area_id, '0745', 1, 0)) z0745,
SUM (DECODE (a.area_id, '0738', 1, 0)) z0738,
SUM (DECODE (a.area_id, '0739', 1, 0)) z0739,
SUM (DECODE (a.area_id, '0735', 1, 0)) z0735,
SUM (DECODE (a.area_id, '0743', 1, 0)) z0743
FROM (SELECT DISTINCT v.materiel_id, v.materiel_type_id,
v.area_id, v.bill_type
FROM v_inout_month_report v
WHERE 1 = 1
AND ( v.districtid IN (
SELECT man_obj_id
FROM td_sge_keeping_ref
WHERE keeping_id = 'csck1'
AND manager_level = 1)
OR v.storehouse_id IN (
SELECT man_obj_id
FROM td_sge_keeping_ref
WHERE keeping_id = 'csck1'
AND manager_level = 0)
)) a
GROUP BY materiel_type_id, bill_type)
ORDER BY bill_type, query_type, materiel_type_id
分享到：
发表评论
-
转载]linux下启动oracle
2012-03-06 14:56 1093启动数据库实例，分为两步：第一步，启动监听；第二步，启动数据库 ... -
sql中时间相减(case when then的用法)
2011-04-19 20:24 3433select f.FILE_ID,f.FILE_NAME,f. ... -
Oracle的取整和四舍五入函数 时间相减
2011-03-24 18:50 4571注:以下内容转载自http://hi.baidu.com/de ... -
【转】oracle中导入导出dmp数据库文件语句使用
2010-12-18 11:30 6675Oracle数据导入导出imp/exp ... -
oracle表中怎么建序列
2010-11-30 19:13 1226在oracle中sequence就是所 ... -
oracle伪列 分页
2010-11-27 17:52 1220/* Formatted on 2010/11/26 09:0 ... -
oracle中截取字符串的方法
2010-09-06 20:52 6371oracle 中截取字符串的情况如下： (1) 可以截取某 ... -
sql语句做数据初始化（科创）
2010-09-04 17:09 1390delete from td_project_integral ... -
Oracle通过job定时创建表-入门基础
2010-08-31 15:14 1664在项目中，经常会遇到需要定时完成的任务，比如定时更新数据， ... -
数据库应用开发工具Toad使用笔记
2010-08-07 16:45 1381TOAD使用笔记 1、把 ... -
Oracle 创建删除用户、角色、表空间、导入导出、备份 命令总结
2010-05-20 17:30 2924导入导出实用程序用于实施数据库的逻辑备份和恢复 导出实用将数据 ... -
oracle 的 Sql操作流程
2010-05-20 17:30 1099没有给你用户和密码的时候： 1、创建用户： 打开 ... -
oracle pl/sql语句大全
2010-05-20 15:16 2049例 select ename,sal,next_day(s ...
相关推荐
SQL语句的DECODE和NVL，肯定能帮到你，好用！
使用DECODE实现统计，是比较常用的。 但是在遇到需要组合条件进行统计时，有时却不如case when 或者另建视图好用。 可是有时就想用DECODE，咋办？ 这里给大家介绍一下DECODE如何实现组合条件查询，一句SQL查询一张...
DECODE函数，是ORACLE公司的SQL软件ORACLE PL/SQL所提供的特有函数计算方式，以其...使用方法： Select decode（columnname，值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值） From talbename Where … 其中columnn
我们要做到不但会写SQL，还要做到写出性能优良的SQL语句。 （1）选择最有效率的表名顺序(只在基于规则的优化器中有效)： Oracle的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表...
（1） 选择最有效率的表名顺序(只在基于规则...（5） 在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量 ,建议值为200 （6） 使用DECODE函数来减少处理时间 等优化方法详解
在一个SELECT 语句中使用多个表119 正确地找到列123 等值联合124 不等值联合129 外部联合与内部联合130 表的自我联合132 总结134 问与答134 校练场134 练习135 第七天子查询内嵌的SQL 子句136 目标136 建立一个子...
主要作用：将查询结果翻译成其他值（即以...使用方法： Select decode（columnname，值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值） From talbename Where … 其中columnname为要选择的table中所定义的column，
但是，如果在SQL语句的where子句中写的SQL代码不合理，就会造成优化器删去索引而使用全表扫描，一般就这种SQL语句就是所谓的劣质SQL语句。在编写SQL语句时我们应清楚优化器根据何种原则来删除索引，这有助于写出高...
Oracle 中 decode 函数用法 含义解释： decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值) 该函数的含义如下： IF 条件=值1 THEN RETURN(翻译值1) ELSIF 条件=值2 THEN RETURN(翻译值2) ...... ...
Oracle中Decode()函数的有关用法Oracle中Decode()函数的有关用法
存储过程 SQL SERVER 使用c# winform编写 ２０２０_03_23 疯汉三发布 使用了委托编写 ，版本还没有写完
查詢高消耗CPU的pid的SQL,SELECT /*+ ...DECODE (sql_hash_value, 0, prev_sql_addr, sql_address) FROM v$session b WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = '&pid')) ORDER BY piece ASC
*Sql语句中大量使用函数时候会导致很多索引无法使用上，要针对具体问题分析 4.其它 避免使用Select *,因为系统需要去帮你将*转换为所有的列名，这个需要额外去查询数据字典。 Count(1)和Count(*)差别不大。 ...
关于oracle decode函数的用法
1 DECODE 中的if-then-else逻辑 在逻辑编程中，经常用到If – Then –Else 进行逻辑判断。在DECODE的语法中，实际上就是这样的逻辑处理过程。
Oracle中Decode()函数使用技巧Oracle中Decode()函数使用技巧Oracle中Decode()函数使用技巧
常用的SQL，及SQL优化
DECODE
非常全面的ORACLE SQL性能优化介绍及实例：优化器的选择、共享SQL语句、访问Table的方式、减少访问数据库的次数、使用DECODE函数、减少对表的查询、通过内部函数提高SQL效率、用NOT EXISTS替代NOT IN、使用EXPLAIN ...