下面这个sql语句执行起来非常慢,不知道有什么办法能优化一下SELECT t1.wareid as wid, t1.OrgId, jc.creatdate FROM chuguan AS jc INNER JOIN
(SELECT jc.OrgId, jq.wareid, MAX(jc.kdanhao) AS kdanhao
FROM chuguan AS jc WITH (nolock)
INNER JOIN qingdan AS jq WITH (nolock) ON jc.kdanhao = jq.kdanhao
WHERE (jc.churu = '入库') AND (jc.feilei = '放货' OR jc.feilei = '移动')
AND (jq.wareid >= 1000) AND (jq.wareid <= 100000)
AND (jc.OrgId IN (6,3,10,4,9,544,600,432,322,545,5,7,8,316))
GROUP BY jc.OrgId, jq.wareid) AS t1
ON jc.kdanhao = t1.kdanhao
(SELECT jc.OrgId, jq.wareid, MAX(jc.kdanhao) AS kdanhao
FROM chuguan AS jc WITH (nolock)
INNER JOIN qingdan AS jq WITH (nolock) ON jc.kdanhao = jq.kdanhao
WHERE (jc.churu = '入库') AND (jc.feilei = '放货' OR jc.feilei = '移动')
AND (jq.wareid >= 1000) AND (jq.wareid <= 100000)
AND (jc.OrgId IN (6,3,10,4,9,544,600,432,322,545,5,7,8,316))
GROUP BY jc.OrgId, jq.wareid) AS t1
ON jc.kdanhao = t1.kdanhao
;with t1
as
(SELECT jc.OrgId, jq.wareid, MAX(jc.kdanhao) AS kdanhao
FROM chuguan AS jc WITH (nolock)
INNER JOIN qingdan AS jq WITH (nolock) ON jc.kdanhao = jq.kdanhao
WHERE (jc.churu = '入库') AND (jc.feilei = '放货' OR jc.feilei = '移动')
AND (jq.wareid >= 1000) AND (jq.wareid <= 100000)
AND (jc.OrgId IN (6,3,10,4,9,544,600,432,322,545,5,7,8,316))
GROUP BY jc.OrgId, jq.wareid)
select t1.wareid as wid, t1.OrgId, jc.creatdate FROM chuguan AS jc
INNER JOIN t1 on jc.kdanhao = t1.kdanhao
SELECT jc.OrgId, jq.wareid, MAX(jc.kdanhao) AS kdanhao
FROM chuguan AS jc WITH (nolock)
INNER JOIN qingdan AS jq WITH (nolock) ON jc.kdanhao = jq.kdanhao
WHERE (jc.churu = '入库') AND (jc.feilei = '放货' OR jc.feilei = '移动')
AND (jq.wareid >= 1000) AND (jq.wareid <= 100000)
AND (jc.OrgId IN (6,3,10,4,9,544,600,432,322,545,5,7,8,316))
GROUP BY jc.OrgId, jq.wareid
AND (jq.wareid >= 1000) AND (jq.wareid <= 100000)
AND (jc.OrgId IN (6,3,10,4,9,544,600,432,322,545,5,7,8,316))CREATE INDEX mycolumn_index ON chuguan (churu,feilei ,OrgId )
CREATE INDEX mycolumn_index ON qingdan (wareid ,feilei )