复杂SQL的编写要领

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/ostrichmyself/article/details/42581227

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等







展开阅读全文

没有更多推荐了,返回首页