七十多行的sql语句 关联三四个表，记录也比较多，怎样优化

没有具体的描述怎么回答。。

请附上七十多行代码，谢谢。

你这个没有SQL的代码，不好回复啊

_eight_brother 发表于 2016-4-21 16:47
请附上七十多行代码，谢谢。

1. <select id="getDetailData" parameterType="java.util.Map" resultType="java.util.Map">
   
2.         SELECT aa.*,DATE_FORMAT(#{startTime},'%Y-%m-%d')startTime,
   
3.         DATE_FORMAT(#{endTime},'%Y-%m-%d') endTime,
   
4.         aa.averageRewardScore rewardScore,
   
5.         aa.averageReduceScore reduceScore,
   
6.         SUM(IF (c.DraweeScore > 0,c.DraweeScore,0)) realRewardScore,
   
7.         SUM(IF (c.DraweeScore &lt; 0, abs(c.DraweeScore),0)) realReduceScore,
   
8.         COUNT(DISTINCT CASE WHEN c.DraweeScore > 0 AND c.DraweeScore IS NOT NULL THEN c.DraweeID END ) realRewardNum
   
9.         FROM
   
10.         (SELECT a.EmpID empID, a.EmpName empName, a.OrgName orgName, a.EmpNo empNo, a.postName,
    
11.         b.AverageReduceScore averageReduceScore,b.ScoreTaskDesc scoreTaskDesc,
    
12.         b.AverageRewardScore averageRewardScore, b.LowestRewardScore lowestRewardScore,b.scoreTaskID scoreTaskID,
    
13.         <if test="taskBeExecutePostIDs==null or taskBeExecutePostIDs==''">
    
14.             (
    
15.             SELECT COUNT(*) FROM (SELECT record.*,
    
16.             (SELECT COUNT(*) FROM ${enterpriseCode}.sys_mobilizepostrecord record_temp WHERE record_temp.CreateTime>record.CreateTime AND record.EmpID =record_temp.EmpID
    
17.             AND date_format(record_temp.CreateTime,'%Y-%m-%d')  &lt; date_format(#{startTime},'%Y-%m-%d')) count,
    
18.             c.isEnable,
    
19.             c.empLeaveDate
    
20.             FROM ${enterpriseCode}.sys_mobilizepostrecord record left join ${enterpriseCode}.sys_emp c on c.EmpID = record.EmpID
    
21.             WHERE date_format(record.CreateTime,'%Y-%m-%d')  &lt; date_format(#{startTime},'%Y-%m-%d'))
    
22.             aa WHERE aa.PostID IN (SELECT PostID FROM ${enterpriseCode}.sys_post WHERE pPostID = a.postID) AND
    
23.             aa.count =0 AND (aa.isEnable = 'true' OR
    
24.             (aa.isEnable='false' AND date_format(aa.empLeaveDate,'%Y-%m-%d') > date_format(#{startTime},'%Y-%m-%d') ))) lowerEmpCount
    
25.         </if>
    
26.         <if test="taskBeExecutePostIDs!=null and taskBeExecutePostIDs!=''">
    
27.             (
    
28.             SELECT COUNT(*) FROM (SELECT record.*,
    
29.             (SELECT COUNT(*) FROM ${enterpriseCode}.sys_mobilizepostrecord record_temp WHERE record_temp.CreateTime>record.CreateTime AND record.EmpID =record_temp.EmpID
    
30.             AND date_format(record_temp.CreateTime,'%Y-%m-%d')  &lt; date_format(#{startTime},'%Y-%m-%d')) count,
    
31.             c.isEnable,
    
32.             c.empLeaveDate
    
33.             FROM ${enterpriseCode}.sys_mobilizepostrecord record left join ${enterpriseCode}.sys_emp c on c.EmpID = record.EmpID
    
34.             WHERE date_format(record.CreateTime,'%Y-%m-%d')  &lt; date_format(#{startTime},'%Y-%m-%d'))
    
35.             aa WHERE FIND_IN_SET(aa.PostID,#{taskBeExecutePostIDs}) AND
    
36.             aa.count =0 AND (aa.isEnable = 'true' OR
    
37.             (aa.isEnable='false' AND date_format(aa.empLeaveDate,'%Y-%m-%d') > date_format(#{startTime},'%Y-%m-%d') ))) lowerEmpCount
    
38.         </if>
    
39.         FROM ${enterpriseCode}.scoretask b
    
40.         LEFT JOIN ${enterpriseCode}.empDetail_view a ON FIND_IN_SET(a.PostID,b.TaskExecutePostIDs)
    
41.         WHERE b.ScoreTaskID=#{scoreTaskID} AND a.PostID IS NOT NULL AND a.PostID != ''
    
42.         <if test="empID!=null and empID!=''">
    
43.             AND FIND_IN_SET(a.EmpID,#{empID})
    
44.         </if>
    
45.         ) aa
    
46.         LEFT JOIN (
    
47.         SELECT * FROM
    
48.         ${enterpriseCode}.scorebill_view WHERE DATE_FORMAT(BillTime, '%Y-%m-%d') >= DATE_FORMAT(#{startTime},
    
49.         '%Y-%m-%d')
    
50.         AND DATE_FORMAT(BillTime, '%Y-%m-%d') &lt;= DATE_FORMAT(#{endTime}, '%Y-%m-%d')
    
51.         AND IsStable = 'false' AND isEnable = 'true') c ON aa.EmpID = c.DrawerID
    
52.         <if test="taskBeExecutePostIDs==null or taskBeExecutePostIDs==''">
    
53.             AND c.DraweeID IN (SELECT EmpID FROM ${enterpriseCode}.sys_emp WHERE PostID IN (SELECT PostID FROM
    
54.             ${enterpriseCode}.sys_post
    
55.             WHERE pPostID IN (SELECT PostID FROM ${enterpriseCode}.sys_emp WHERE FIND_IN_SET(EmpID,aa.EmpID))) AND
    
56.             isEnable ='true'
    
57.             AND PostID IS NOT NULL AND PostID != '')
    
58.         </if>
    
59.         <if test="taskBeExecutePostIDs!=null and taskBeExecutePostIDs!=''">
    
60.             AND c.DraweeID IN (SELECT EmpID FROM ${enterpriseCode}.sys_emp WHERE isEnable ='true' AND
    
61.             FIND_IN_SET(PostID,#{taskBeExecutePostIDs})
    
62.             AND PostID IS NOT NULL AND PostID != '')
    
63.         </if>
    
64.         GROUP BY aa.empID
    
65.         ORDER BY realRewardScore desc
    
66.     </select>

复制代码

_eight_brother 发表于 2016-4-21 16:47
请附上七十多行代码，谢谢。

不好意思，这段时间有点忙，回复的比较晚。上面的就是代码

__________Just 发表于 2016-4-22 17:26
不好意思，这段时间有点忙，回复的比较晚。上面的就是代码

看了你的代码，好像是根据某些条件来拼接sql，能说说具体业务功能吗？

我相信看到你这sql的人，就算是大神来了也只是选择沉默不语。优化原则：1、不select * 2、不连接查询3、where 条件，左侧不使用表达式。...太多不详说，也说不上来多少

数据库SQL优化大总结

这是一个链接,有时间慢慢看!