优化一个查询语句的问题

[z_yanjie]

本人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判断。

[--小F--]

把嵌套改成连接

[--小F--]

select 
   KDBH,
   max(case when JGClass = 'A1'  then CheckResult else '' end) as 'A1R',
   ...
from
   CheckList
group by
   KDBH

[Yang_]

最新好像比较麻烦

[SQL77]

SELECT 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 

[Yang_]

2楼用max显然不对

[SQL77]

[Quote=引用 4 楼 sql77 的回复:]
SQL code
SELECT 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
……
[/Quote]
少了GROUP BY ,自己补一下,如果需要动态的等他们帖

[nianran520]

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

[SQL77]

[Quote=引用 7 楼 nianran520 的回复:]
SQL code

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 KD……
[/Quote]
我晕KDBH都没写上,

[fwacky]

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

[--小F--]

[Quote=引用 5 楼 yang_ 的回复:]
2楼用max显然不对
[/Quote]
呵呵 后面的一堆 我就没加了

[z_yanjie]

如果把BaseInfo中所有的都列出呢（checklist中一条记录也没有的）

[fwacky]

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