别人的sql
SELECT
a.createtime AS CREATETIME,
a.SSSXBH,
a.PID AS AFFAIRID,
a.SSSXBH AS AFFAIRCODE,
a. STATUS AS VALID,
b.SETTINGSNAME AS AFFAIRNAME,
b.SQJB AS AF_LEVEL,
b.SSDEPT AS AFFAIRSDEPTMENTNAME,
c.slsx AS TIMELIMIT,
a.updatestate
FROM
ss_diritem_info AS a
LEFT JOIN directorymanagement AS b ON a.pid = b.SS_DIREITEM_INFO_ID
LEFT JOIN aprvtime AS c ON a.pid = c.SS_DIREITEM_INFO_ID
WHERE a.updatestate ="+ updatestate + @"
GROUP BY sssxbh
ORDER BY
valid desc ,createtime DESC,sssxbh
我的sql
SELECT
a.createtime AS CREATETIME,
a.SSSXBH,
a.PID AS AFFAIRID,
a.SSSXBH AS AFFAIRCODE,
a. STATUS AS VALID,
b.SETTINGSNAME AS AFFAIRNAME,
b.SQJB AS AF_LEVEL,
b.SSDEPT AS AFFAIRSDEPTMENTNAME,
c.slsx AS TIMELIMIT
FROM
ss_diritem_info AS a
RIGHT JOIN (
SELECT
max(createtime) mt,
sssxbh
FROM
ss_diritem_info
WHERE
updatestate = 0
GROUP BY
sssxbh
) AS d ON a.sssxbh = d.sssxbh
AND a.createtime = d.mt
LEFT JOIN directorymanagement AS b ON a.pid = b.SS_DIREITEM_INFO_ID
LEFT JOIN aprvtime AS c ON a.pid = c.SS_DIREITEM_INFO_ID
ORDER BY
valid DESC,
sssxbh
我写的比较繁琐,先右连接获取到满足条件的数据再进行左连接获取其他的扩展数据,别人的是先左连接获取到所有数据后再进行筛选分组,执行时间差不多