博客分类：
一、［基本查询语句展示优化］
#根据type查询
SELECT id,title,type FROM table WHERE type=1;
SELECT id,title,type FROM table WHERE type=2;
#if(expr,true,false)
SELECT id,title,type,if(type=1,1,0) as type1,if(type=2,1,0) as type2 FROM
SELECT id,title,type,if(type=1,1,0) as type1,if(type=2,1,0) as type2 FROM
用case when优化
#case...when...then...when...then...else...end
SELECT id,title,type,case type WHEN 1 THEN 'type1' WHEN 2 THEN 'type2' ELSE 'type error' END as newType FROM
二、［统计数据性能优化］
#两次查询不同条件下的数量
SELECT count(id) AS size FROM table WHERE type=1
SELECT count(id) AS size FROM table WHERE type=2
SELECT sum(if(type=1, 1, 0)) as type1, sum(if(type=2, 1, 0)) as type2 FROM table
#count方法
SELECT count(if(type=1, 1, NULL)) as type1, count(if(type=2, 1, NULL)) as type2 FROM table
#亲测二者的时间差不多
#建议用sum，因为一不注意，count就会统计了if的false中的0
用case when优化
SELECT sum(case type WHEN 1 THEN 1 ELSE 0 END) as type1, sum(case type WHEN 2 THEN 1 ELSE 0 END) as type2 FROM table
SELECT count(case type WHEN 1 THEN 1 ELSE NULL END) as type1, count(case type WHEN 2 THEN 1 ELSE NULL END) as type2 FROM table
亲测查询两次和优化后查询一次的时间一样，优化时间为1/2
浏览: 198304 次
SysApplication的构造函数不能是私有的吧。那这样单 ...
还有可能是禁止root用户登录SSH，还有就是限制某些IP登录 ...
怎么用他分词出词性，主谓宾。看到wordSeq里只有word字 ...
我点击按钮时执行这段代码
Intent intent ...
somefuture 写道Access-Control-All ...2761人阅读
SQL优化（121）
& & & 下面是一张报表的SQL实现，不用深入业务，你可以看到上面的4条SQL和下面的4条SQL其实大致相同，只是部分条件不一样，想要优化的第一感觉是如何减少扫描的次数，这个时候case when就派上用场了。
SELECT DECODE(ALL_COUNT, 0, 0, ROUND(ARRIVED_COUNT / ALL_COUNT, 4) * 100) ARRIVED_RATE
FROM (SELECT E.COUNT_RESULT + F.COUNT_RESULT + G.COUNT_RESULT +
H.COUNT_RESULT ARRIVED_COUNT
FROM (SELECT COUNT(1) COUNT_RESULT
FROM GG_TASK_WORK_SHEET
GG_TASK_PATROL_SHEET_DETAIL PSD,
GG_PATROL_WORK_POINT
GG_PATROL_POINT
GG_PATROL_EQUIPMENT
WHERE PTWS.WORK_SHEET_ID = PSD.WORK_SHEET_ID
AND TO_CHAR(PTWS.WORK_SHEET_ID) = PPWP.WORK_ID
AND PPWP.PATROL_POINT_ID = PPP.PATROL_POINT_ID
AND PPP.PATROL_POINT_ID = PPE.PATROL_POINT_ID
AND PTWS.TASK_TYPE = 4
AND PTWS.SHEET_TYPE = 2
AND PSD.PATROL_TYPE && -1
AND PSD.IS_NEED_PDA = '1'
AND PPWP.PATROL_FLAG = 1
AND PTWS.IS_COMPLETE = '3'
AND TO_CHAR(PTWS.REAL_END_DATE, 'YYYY') = '2013'
AND PPE.DEVICE_ID = ) E,
(SELECT COUNT(1) COUNT_RESULT
FROM GG_TASK_WORK_SHEET
GG_TASK_PATROL_SHEET_DETAIL PSD,
GG_PATROL_WORK_POINT
GG_PATROL_POINT
GG_TASK_PATROL_OBJECT
WHERE PTWS.WORK_SHEET_ID = PSD.WORK_SHEET_ID
AND TO_CHAR(PTWS.WORK_SHEET_ID) = PPWP.WORK_ID
AND PPWP.PATROL_POINT_ID = PPP.PATROL_POINT_ID
AND PPP.PATROL_POINT_ID = PTPO.PATROL_POINT_ID
AND PTWS.TASK_TYPE = 4
AND PTWS.SHEET_TYPE = 2
AND PSD.PATROL_TYPE && -1
AND PSD.IS_NEED_PDA = '1'
AND PPWP.PATROL_FLAG = 1
AND PTWS.IS_COMPLETE = '3'
AND TO_CHAR(PTWS.REAL_END_DATE, 'YYYY') = '2013'
AND PTPO.DEVICE_ID = ) F,
(SELECT COUNT(1) COUNT_RESULT
FROM GG_TASK_WORK_SHEET
GG_TASK_PATROL_SHEET_DETAIL PSD,
GG_TASK_PATROL_OBJECT
GG_PATROL_EQUIPMENT
WHERE PTWS.WORK_SHEET_ID = PSD.WORK_SHEET_ID
AND PTWS.WORK_SHEET_ID = PTPO.WORK_SHEET_ID
AND PTPO.PATROL_LINE_ID = PPE.PATROL_LINE_ID
AND PTPO.FUNCTION_LOCATION_ID = PPE.FUNCTION_LOCATION_ID
AND PTWS.TASK_TYPE = 4
AND PTWS.SHEET_TYPE = 1
AND PTPO.PATROL_TYPE = 1
AND PSD.PATROL_TYPE = -1
AND PSD.IS_NEED_PDA = '1'
AND PTPO.ARRIVE_TYPE = 1
AND PTWS.IS_COMPLETE = '3'
AND TO_CHAR(PTWS.REAL_END_DATE, 'YYYY') = '2013'
AND PPE.DEVICE_ID = ) G,
(SELECT COUNT(1) COUNT_RESULT
FROM GG_TASK_WORK_SHEET
GG_TASK_PATROL_SHEET_DETAIL PSD,
GG_TASK_PATROL_OBJECT
GG_PATROL_EQUIPMENT
WHERE PTWS.WORK_SHEET_ID = PSD.WORK_SHEET_ID
AND PTWS.WORK_SHEET_ID = PTPO.WORK_SHEET_ID
AND PTPO.PATROL_LINE_ID = PPE.PATROL_LINE_ID
AND PTPO.FUNCTION_LOCATION_ID = PPE.FUNCTION_LOCATION_ID
AND PTWS.TASK_TYPE = 4
AND PTWS.SHEET_TYPE = 1
AND PTPO.PATROL_TYPE = 1
AND PSD.PATROL_TYPE && -1
AND PSD.IS_NEED_PDA = '1'
AND PTPO.ARRIVE_TYPE = 1
AND PTWS.IS_COMPLETE = '3'
AND TO_CHAR(PTWS.REAL_END_DATE, 'YYYY') = '2013'
AND PPE.DEVICE_ID = ) H) ARRIVED_DEVICE,
(SELECT A.COUNT_RESULT + B.COUNT_RESULT + C.COUNT_RESULT +
D.COUNT_RESULT ALL_COUNT
FROM (SELECT COUNT(1) COUNT_RESULT
FROM GG_TASK_WORK_SHEET
GG_TASK_PATROL_SHEET_DETAIL PSD,
GG_PATROL_WORK_POINT
GG_PATROL_POINT
GG_PATROL_EQUIPMENT
WHERE PTWS.WORK_SHEET_ID = PSD.WORK_SHEET_ID
AND TO_CHAR(PTWS.WORK_SHEET_ID) = PPWP.WORK_ID
AND PPWP.PATROL_POINT_ID = PPP.PATROL_POINT_ID
AND PPP.PATROL_POINT_ID = PPE.PATROL_POINT_ID
AND PTWS.TASK_TYPE = 4
AND PTWS.SHEET_TYPE = 2
AND PSD.PATROL_TYPE = -1
AND PSD.IS_NEED_PDA = '1'
AND PTWS.IS_COMPLETE = '3'
AND TO_CHAR(PTWS.REAL_END_DATE, 'YYYY') = '2013'
AND PPE.DEVICE_ID = ) A,
(SELECT COUNT(1) COUNT_RESULT
FROM GG_TASK_WORK_SHEET
GG_TASK_PATROL_SHEET_DETAIL PSD,
GG_PATROL_WORK_POINT
GG_PATROL_POINT
GG_TASK_PATROL_OBJECT
WHERE PTWS.WORK_SHEET_ID = PSD.WORK_SHEET_ID
AND TO_CHAR(PTWS.WORK_SHEET_ID) = PPWP.WORK_ID
AND PPWP.PATROL_POINT_ID = PPP.PATROL_POINT_ID
AND PPP.PATROL_POINT_ID = PTPO.PATROL_POINT_ID
AND PTWS.TASK_TYPE = 4
AND PTWS.SHEET_TYPE = 2
AND PSD.PATROL_TYPE && -1
AND PSD.IS_NEED_PDA = '1'
AND PTWS.IS_COMPLETE = '3'
AND TO_CHAR(PTWS.REAL_END_DATE, 'YYYY') = '2013'
AND PTPO.DEVICE_ID = ) B,
(SELECT COUNT(1) COUNT_RESULT
FROM GG_TASK_WORK_SHEET
GG_TASK_PATROL_SHEET_DETAIL PSD,
GG_TASK_PATROL_OBJECT
GG_PATROL_EQUIPMENT
WHERE PTWS.WORK_SHEET_ID = PSD.WORK_SHEET_ID
AND PTWS.WORK_SHEET_ID = PTPO.WORK_SHEET_ID
AND PTPO.PATROL_LINE_ID = PPE.PATROL_LINE_ID
AND PTPO.FUNCTION_LOCATION_ID = PPE.FUNCTION_LOCATION_ID
AND PTWS.TASK_TYPE = 4
AND PTWS.SHEET_TYPE = 1
AND PSD.PATROL_TYPE = -1
AND PSD.IS_NEED_PDA = '1'
AND PTPO.PATROL_TYPE = 1
AND PTWS.IS_COMPLETE = '3'
AND TO_CHAR(PTWS.REAL_END_DATE, 'YYYY') = '2013'
AND PPE.DEVICE_ID = ) C,
(SELECT COUNT(1) COUNT_RESULT
FROM GG_TASK_WORK_SHEET
GG_TASK_PATROL_SHEET_DETAIL PSD,
GG_TASK_PATROL_OBJECT
GG_PATROL_EQUIPMENT
WHERE PTWS.WORK_SHEET_ID = PSD.WORK_SHEET_ID
AND PTWS.WORK_SHEET_ID = PTPO.WORK_SHEET_ID
AND PTPO.PATROL_LINE_ID = PPE.PATROL_LINE_ID
AND PTPO.FUNCTION_LOCATION_ID = PPE.FUNCTION_LOCATION_ID
AND PTWS.TASK_TYPE = 4
AND PTWS.SHEET_TYPE = 1
AND PTPO.PATROL_TYPE = 1
AND PSD.PATROL_TYPE && -1
AND PSD.IS_NEED_PDA = '1'
AND PTWS.IS_COMPLETE = '3'
AND TO_CHAR(PTWS.REAL_END_DATE, 'YYYY') = '2013'
AND PPE.DEVICE_ID = ) D) ALL_DEVICE
使用case when改造后的SQL如下，不用运行就知道效果明显。
SELECT DECODE(ALL_COUNT, 0, 0, ROUND(ARRIVED_COUNT / ALL_COUNT, 4) * 100) ARRIVED_RATE
FROM (SELECT (E.COUNT_RESULT1 + F.COUNT_RESULT1 + G.COUNT_RESULT1 +
H.COUNT_RESULT1) ARRIVED_COUNT,
(E.COUNT_RESULT2 + F.COUNT_RESULT2 + G.COUNT_RESULT2 +
H.COUNT_RESULT2) ALL_COUNT
FROM (SELECT COUNT(case
when (PSD.PATROL_TYPE && -1 AND PPWP.PATROL_FLAG = 1) then
end) COUNT_RESULT1,
COUNT(case
when PSD.PATROL_TYPE = -1 then
end) COUNT_RESULT2
FROM GG_TASK_WORK_SHEET
GG_TASK_PATROL_SHEET_DETAIL PSD,
GG_PATROL_WORK_POINT
GG_PATROL_POINT
GG_PATROL_EQUIPMENT
WHERE PTWS.WORK_SHEET_ID = PSD.WORK_SHEET_ID
AND TO_CHAR(PTWS.WORK_SHEET_ID) = PPWP.WORK_ID
AND PPWP.PATROL_POINT_ID = PPP.PATROL_POINT_ID
AND PPP.PATROL_POINT_ID = PPE.PATROL_POINT_ID
AND PTWS.TASK_TYPE = 4
AND PTWS.SHEET_TYPE = 2
AND PSD.IS_NEED_PDA = '1'
AND PTWS.IS_COMPLETE = '3'
AND TO_CHAR(PTWS.REAL_END_DATE, 'YYYY') = '2013'
AND PPE.DEVICE_ID = ) E,
(SELECT COUNT(case
when PPWP.PATROL_FLAG = 1 then
end) COUNT_RESULT1,
COUNT(1) COUNT_RESULT2
FROM GG_TASK_WORK_SHEET
GG_TASK_PATROL_SHEET_DETAIL PSD,
GG_PATROL_WORK_POINT
GG_PATROL_POINT
GG_TASK_PATROL_OBJECT
WHERE PTWS.WORK_SHEET_ID = PSD.WORK_SHEET_ID
AND TO_CHAR(PTWS.WORK_SHEET_ID) = PPWP.WORK_ID
AND PPWP.PATROL_POINT_ID = PPP.PATROL_POINT_ID
AND PPP.PATROL_POINT_ID = PTPO.PATROL_POINT_ID
AND PTWS.TASK_TYPE = 4
AND PTWS.SHEET_TYPE = 2
AND PSD.PATROL_TYPE && -1
AND PSD.IS_NEED_PDA = '1'
AND PPWP.PATROL_FLAG = 1
AND PTWS.IS_COMPLETE = '3'
AND TO_CHAR(PTWS.REAL_END_DATE, 'YYYY') = '2013'
AND PTPO.DEVICE_ID = ) F,
(SELECT COUNT(case
when PTPO.ARRIVE_TYPE = 1 then
end) COUNT_RESULT1,
COUNT(case
when PSD.PATROL_TYPE = -1 then
end) COUNT_RESULT2
FROM GG_TASK_WORK_SHEET
GG_TASK_PATROL_SHEET_DETAIL PSD,
GG_TASK_PATROL_OBJECT
GG_PATROL_EQUIPMENT
WHERE PTWS.WORK_SHEET_ID = PSD.WORK_SHEET_ID
AND PTWS.WORK_SHEET_ID = PTPO.WORK_SHEET_ID
AND PTPO.PATROL_LINE_ID = PPE.PATROL_LINE_ID
AND PTPO.FUNCTION_LOCATION_ID = PPE.FUNCTION_LOCATION_ID
AND PTWS.TASK_TYPE = 4
AND PTWS.SHEET_TYPE = 1
AND PTPO.PATROL_TYPE = 1
AND PSD.PATROL_TYPE = -1
AND PSD.IS_NEED_PDA = '1'
AND PTWS.IS_COMPLETE = '3'
AND TO_CHAR(PTWS.REAL_END_DATE, 'YYYY') = '2013'
AND PPE.DEVICE_ID = ) G,
(SELECT COUNT(case
when PTPO.ARRIVE_TYPE = 1 then
end) COUNT_RESULT1,
COUNT(1) COUNT_RESULT2
FROM GG_TASK_WORK_SHEET
GG_TASK_PATROL_SHEET_DETAIL PSD,
GG_TASK_PATROL_OBJECT
GG_PATROL_EQUIPMENT
WHERE PTWS.WORK_SHEET_ID = PSD.WORK_SHEET_ID
AND PTWS.WORK_SHEET_ID = PTPO.WORK_SHEET_ID
AND PTPO.PATROL_LINE_ID = PPE.PATROL_LINE_ID
AND PTPO.FUNCTION_LOCATION_ID = PPE.FUNCTION_LOCATION_ID
AND PTWS.TASK_TYPE = 4
AND PTWS.SHEET_TYPE = 1
AND PTPO.PATROL_TYPE = 1
AND PSD.PATROL_TYPE && -1
AND PSD.IS_NEED_PDA = '1'
AND PTWS.IS_COMPLETE = '3'
AND TO_CHAR(PTWS.REAL_END_DATE, 'YYYY') = '2013'
AND PPE.DEVICE_ID = ) H) ARRIVED_DEVICE
&&相关文章推荐
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：934086次
积分：15788
积分：15788
排名：第700名
原创：649篇
转载：108篇
评论：57条
(18)(19)(7)(16)(18)(6)(9)(14)(11)(14)(6)(7)(6)(10)(9)(16)(9)(14)(12)(12)(9)(5)(6)(7)(5)(4)(4)(11)(13)(12)(6)(5)(6)(10)(4)(3)(12)(6)(5)(14)(12)(11)(10)(5)(8)(10)(21)(17)(27)(24)(15)(25)(23)(20)(15)(8)(7)(21)(23)(21)(30)(17)(4)(1)(1)(2)(3)
(window.slotbydup = window.slotbydup || []).push({
id: '4740887',
container: s,
size: '250,250',
display: 'inlay-fix'博客分类：
数据库：SqlServer
--create table--
create table table1 (
column1 nvarchar(200),
column2 nvarchar(200)
--insert data--
insert into table1(column1,column2) values ('_abc_','aasf');
insert into table1(column1,column2) values ('_acbc_','aasf');
insert into table1(column1,column2) values ('23','ggg');
insert into table1(column1,column2) values ('_def_','aasf');
insert into table1(column1,column2) values ('asdf','3123');
--search as type--
type = case when column1 like '%_abc_%' then 'a'
when column2 like '%GGG%' then 'b'
when column1 like '%_def_%' then 'c'
else 'others' end,
from table1
浏览: 37079 次
来自: 上海
ooo456mmm 写道第三种方法。我用的怎么不行呢？？？把a ...
第三种方法。我用的怎么不行呢？？？
好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好 ...