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>
增加一个任务是否完成的属性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等