本人sql不熟悉,写的语句效率极低,请高手出个优化的方法:SELECT KDBH,
(SELECT TOP 1 CheckList.CheckResult
FROM CheckList
WHERE JGClass = 'A1' AND BaseInfo.KDBH = CheckList.KDBH
ORDER BY CheckList.CheckTime DESC) AS A1R,
(SELECT TOP 1 CheckList.CheckTime
FROM CheckList
WHERE JGClass = 'A1' AND BaseInfo.KDBH = CheckList.KDBH
ORDER BY CheckList.CheckTime DESC) AS A1T,
(SELECT TOP 1 CheckList.CheckResult
FROM CheckList
WHERE JGClass = 'A2' AND BaseInfo.KDBH = CheckList.KDBH
ORDER BY CheckList.CheckTime DESC) AS A2R,
(SELECT TOP 1 CheckList.CheckTime
FROM CheckList
WHERE JGClass = 'A2' AND BaseInfo.KDBH = CheckList.KDBH
ORDER BY CheckList.CheckTime DESC) AS A2T,
(SELECT TOP 1 CheckList.CheckResult
FROM CheckList
WHERE JGClass = 'B' AND BaseInfo.KDBH = CheckList.KDBH
ORDER BY CheckList.CheckTime DESC) AS BR,
(SELECT TOP 1 CheckList.CheckTime
FROM CheckList
WHERE JGClass = 'B' AND BaseInfo.KDBH = CheckList.KDBH
ORDER BY CheckList.CheckTime DESC) AS BT
FROM dbo.BaseInfo这个也涉及到行列转换的问题,就是从CheckList中提出每个KDBH,A1\A2\B三种类型记录最新的CheckResult和CheckTime,是否最新用CheckTime判断。
(SELECT TOP 1 CheckList.CheckResult
FROM CheckList
WHERE JGClass = 'A1' AND BaseInfo.KDBH = CheckList.KDBH
ORDER BY CheckList.CheckTime DESC) AS A1R,
(SELECT TOP 1 CheckList.CheckTime
FROM CheckList
WHERE JGClass = 'A1' AND BaseInfo.KDBH = CheckList.KDBH
ORDER BY CheckList.CheckTime DESC) AS A1T,
(SELECT TOP 1 CheckList.CheckResult
FROM CheckList
WHERE JGClass = 'A2' AND BaseInfo.KDBH = CheckList.KDBH
ORDER BY CheckList.CheckTime DESC) AS A2R,
(SELECT TOP 1 CheckList.CheckTime
FROM CheckList
WHERE JGClass = 'A2' AND BaseInfo.KDBH = CheckList.KDBH
ORDER BY CheckList.CheckTime DESC) AS A2T,
(SELECT TOP 1 CheckList.CheckResult
FROM CheckList
WHERE JGClass = 'B' AND BaseInfo.KDBH = CheckList.KDBH
ORDER BY CheckList.CheckTime DESC) AS BR,
(SELECT TOP 1 CheckList.CheckTime
FROM CheckList
WHERE JGClass = 'B' AND BaseInfo.KDBH = CheckList.KDBH
ORDER BY CheckList.CheckTime DESC) AS BT
FROM dbo.BaseInfo这个也涉及到行列转换的问题,就是从CheckList中提出每个KDBH,A1\A2\B三种类型记录最新的CheckResult和CheckTime,是否最新用CheckTime判断。
KDBH,
max(case when JGClass = 'A1' then CheckResult else '' end) as 'A1R',
...
from
CheckList
group by
KDBH
max(case when JGClass='a1' then b.CheckTime else '' end) as a1t,
max(case when JGClass='a1' then b.CheckResult else '' end) as a1r,
...
FROM dbo.BaseInfo a,
(
SELECT
JGClass,CheckTime,CheckResult
FROM CheckList t
where CheckTime=(select max(CheckTime) from CheckList where JGClass=t.JGClass)
and JGClass in('a1','a2','b')) b where a.KDBH =b.KDBH
select
r.*
from dbo.BaseInfo t join
(select KDBH,JGClass,CheckResult
from CheckList h
where JGClass in ('A1','A2','B') and CheckTime in
(select max(CheckTime) from CheckList where KDBH = h.KDBH and JGClass = h.JGClass)
)
r
on t.KDBH = r.KDBH
max(case when A.JGClass='A1' then A.CheckResult else null end) as A1R ,
max(case when A.JGClass='A1' then A.CheckTime else null end) as A1T ,
max(case when A.JGClass='A2' then A.CheckResult else null end) as A2R ,
max(case when A.JGClass='A2' then A.CheckTime else null end) as A2T ,
max(case when A.JGClass='B' then A.CheckResult else null end) as BR ,
max(case when A.JGClass='B' then A.CheckTime else null end) as BT
from
(
select KDBH,JGClass,
CheckTime =MAX(CheckTime),
CheckResult = (select top 1 CheckResult from CheckList where KDBH =CheckList.KDBH and JGClass=CheckList.JGClass order by CheckTime desc )
from CheckList group by KDBH,JGClass
) as A
group by A.KDBH
select A.*,B.* from BaseInfo A inner join
(
select A.KDBH,
max(case when A.JGClass='A1' then A.CheckResult else null end) as A1R ,
max(case when A.JGClass='A1' then A.CheckTime else null end) as A1T ,
max(case when A.JGClass='A2' then A.CheckResult else null end) as A2R ,
max(case when A.JGClass='A2' then A.CheckTime else null end) as A2T ,
max(case when A.JGClass='B' then A.CheckResult else null end) as BR ,
max(case when A.JGClass='B' then A.CheckTime else null end) as BT
from
(
select KDBH,JGClass,
CheckTime =MAX(CheckTime),
CheckResult = (select top 1 CheckResult from CheckList where KDBH =CheckList.KDBH and JGClass=CheckList.JGClass order by CheckTime desc )
from CheckList group by KDBH,JGClass
) as A
group by A.KDBH) B
on A.KDBH= B.KDBH