复杂SQL的编写要领

SQL的编码显得越来越重要，原因是很多逻辑处理，都可以跟sql挂钩。 个人排斥将逻辑层代码复杂化， 因此sql在未来的项目架构中，举足轻重， 特地开一章节， 来总结一下以往项目中sql的精髓部分。

显示某个店铺的任务列表，同时把某个用户的任务完成情况列出来。 传入用户的id_user值,  看看该用户是否完成任务, 先前写的sql如下:

<select id="listShopTask" parameterType="java.util.Map" resultMap="shopTaskResult">
SELECT
  id_shop,
  id_task,
  is_online_task,
  type_task,
  freq,
  url,
  score,
  descripe,
  title,
  time_start,
  time_end
FROM shop_task t
WHERE t.id_shop = #{id_shop}
<if test="type_task != null "> 
      AND t.type_task =#{type_task}
    </if> 
    order by time_start desc
         <if test="beginIdx != null "> 
        LIMIT ${beginIdx}, ${length}
    </if>
</select>

这个SQL是完全在一张表里面做的， 现在要将用户的情况加入，用户完成一个任务，相应的积分有增加， 这个表叫做score_user表， 为了满足需求，sql是这样设计的:

增加一个任务是否完成的属性status_finish， 需记住: (Left Join是一个表，牢记这点就好):
 

SELECT
            t.id_shop,
            t.id_task,
            t.is_online_task,
            t.type_task,
            t.freq,
            t.url,
            t.score,
            t.descripe,
            t.title,
            t.time_start,
            t.time_end,
            uu.status_finish
          FROM shop_task t
               LEFT JOIN (SELECT
                         COUNT(1) AS status_finish,
                         mark, id_user,type_score, time_get
                       FROM score_user su
                       WHERE su.id_user='519006470'
                       GROUP BY su.mark) uu
              ON t.id_task = uu.mark
          WHERE t.id_shop = '50001020'
          order by time_start desc

由于Left join 存在多个数量时， 返回内容过多，需要调整一下:

SELECT
            t.id_shop,
            t.id_task,
            t.is_online_task,
            t.type_task,
            t.freq,
            t.url,
            t.score,
            t.descripe,
            t.title,
            t.time_start,
            t.time_end,
            uu.time_get,
            uu.id_user
          FROM shop_task t
               LEFT JOIN (SELECT
                         mark, id_user,type_score, time_get
                       FROM score_user su
                       WHERE su.id_user='340001477'
                       And type_score = 6
                       order by time_get desc) uu
              ON t.id_task = uu.mark
          WHERE t.id_shop = '50001020'
          order by time_start desc

由于Left join 存在多个数量时， 返回内容过多，需要调整一下:

SELECT
            t.id_shop,
            t.id_task,
            t.is_online_task,
            t.type_task,
            t.freq,
            t.url,
            t.score,
            t.descripe,
            t.title,
            t.time_start,
            t.time_end,
            uu.time_get,
            uu.id_user
          FROM shop_task t
               LEFT JOIN (SELECT
                         mark, id_user,type_score, time_get
                       FROM score_user su
                       WHERE su.id_user='519006470'
                       And type_score = 6
                       order by time_get desc
                       limit 0,1) uu
              ON t.id_task = uu.mark
          WHERE t.id_shop = '50001020'
          order by time_start desc

limit将导致内容过少… ⊙﹏⊙b汗！

SELECT
            mark, id_user,type_score, time_get
            FROM score_user su
            WHERE su.id_user='519006470'
            And type_score = 6
            order by time_get desc

OK终于可以搞定，Group 是个很强大的语法。

SELECT
            mark, id_user, type_score, MAX(time_get)
            FROM score_user su
            WHERE id_user='519006470'
            AND type_score = 6
            GROUP BY mark

结合以上，我们的结论是:

SELECT
            t.id_shop,
            t.id_task,
            t.is_online_task,
            t.type_task,
            t.freq,
            t.url,
            t.score,
            t.descripe,
            t.title,
            t.time_start,
            t.time_end,
            uu.last_time,
            uu.id_user
          FROM shop_task t
               LEFT JOIN (SELECT
          mark, id_user, type_score, MAX(time_get) AS last_time
          FROM score_user su
          WHERE id_user='519006470'
          AND type_score = 6
          GROUP BY mark) uu
              ON t.id_task = uu.mark
          WHERE t.id_shop = '50001020'
          ORDER BY time_start DESC

总结:

1. Left Joion即增加一个表

2. 去掉笛卡尔常量的方法, 加限定词:  max, group by等