sql子查询优化

[tristia]

SELECT Id, LineId, ControlId, Direction, FieldNo, Remark,
(SELECT TOP (1) Longitude
FROM T_L_Station AS b
WHERE (LineId = a.LineId) AND (Direction = a.Direction)
ORDER BY OrderNo) AS Longitude,
(SELECT TOP (1) Latitude
FROM T_L_Station AS b
WHERE (LineId = a.LineId) AND (Direction = a.Direction)
ORDER BY OrderNo) AS Latitude,
(SELECT TOP (1) GprsId
FROM T_L_Line AS c
WHERE (LineId = a.LineId)) AS GprsId,
(SELECT TOP (1) LineType
FROM T_L_Line AS c
WHERE (LineId = a.LineId)) AS LineType,
(SELECT TOP (1) Name
FROM T_L_Line AS d
WHERE (LineId = a.LineId)) AS LineName,
(SELECT TOP (1) IsSubLine
FROM T_L_Line AS e
WHERE (LineId = a.LineId)) AS IsSubLine,
(SELECT TOP (1) ScreenNo
FROM T_S_Controls AS f
WHERE (ControlId = a.ControlId)) AS ScreenNo
FROM T_S_ControlLine AS a
WHERE (ControlId =
(SELECT TOP (1) ControlId
FROM T_S_ControlUser
WHERE (UserId = 2)))

如上SQL语句有什么思路吗?

[tristia]

我刚才尝试了下面这样,但是一直提示SQL不支持OVER

SELECT Id, LineId, ControlId, Direction, FieldNo, Remark, Longitude, row_number()
OVER (partition BY id
ORDER BY Id, LineId, ControlId, Direction, FieldNo, Remark) AS rn
FROM (SELECT a_1.Id, a_1.LineId, a_1.ControlId, a_1.Direction, a_1.FieldNo, a_1.Remark,
T_L_Station.Longitude, T_L_Station.Latitude
FROM (SELECT Id, LineId, ControlId, Direction, FieldNo, Remark
FROM T_S_ControlLine) AS a_1 LEFT OUTER JOIN
T_L_Station ON a_1.LineId = T_L_Station.LineId AND
a_1.Direction = T_L_Station.Direction) AS A
WHERE a.rn = 1

[pt1314917]

[Quote=引用 7 楼 tristia 的回复:]
我刚才尝试了下面这样,但是一直提示SQL不支持OVER

SELECT Id, LineId, ControlId, Direction, FieldNo, Remark, Longitude, row_number()
OVER (partition BY id
ORDER BY Id, LineId, ControlId, Direction, FieldNo, Remark) ……
[/Quote]

row_number() over()是SQL05里面的用法。你的是2000吧。

[tristia]

to:pt1314917
我用的是2005

to:duanzhi1984
我用了临时表
我想问下2005有什么办法对比查看两个SQL或者两个存储过程的速度呢?

[duanzhi1984]

[Quote=引用 9 楼 tristia 的回复:]
to:pt1314917
我用的是2005

to:duanzhi1984
我用了临时表
我想问下2005有什么办法对比查看两个SQL或者两个存储过程的速度呢?
[/Quote]

用执行计划吧。。。

[hao1hao2hao3]

[Quote=引用 9 楼 tristia 的回复:]
to:pt1314917
我用的是2005

to:duanzhi1984
我用了临时表
我想问下2005有什么办法对比查看两个SQL或者两个存储过程的速度呢?
[/Quote]

用执行计划看执行的过程，或者按下面的方式看执行的时间。精确到了毫秒。

declare @a datetime
select @a = getdate()
--select * from tb 
declare @int int
set @int = 0
while @int <1000000
	begin 
		set @int = @int +1
	end
declare @b datetime 
select @b = getdate()
select datediff(ms,@a,@b)

[hao1hao2hao3]

有点恐怖

[合肥小菜]

有点吓人

[tristia]

换个思路

SELECT Id, LineId, ControlId, Direction, FieldNo, Remark, Longitude
FROM (SELECT a_1.Id, a_1.LineId, a_1.ControlId, a_1.Direction, a_1.FieldNo, a_1.Remark,
T_L_Station.Longitude, T_L_Station.Latitude
FROM (SELECT Id, LineId, ControlId, Direction, FieldNo, Remark
FROM T_S_ControlLine) AS a_1 LEFT OUTER JOIN
T_L_Station ON a_1.LineId = T_L_Station.LineId AND
a_1.Direction = T_L_Station.Direction) AS A


依据GROUP BY Id 取第一条数据
===================================================
上述要怎么写??

[duanzhi1984]

给你个思路,将相同的子查询只是返回的字段不同的一次性查询出来，放入到临时 表或嵌套表中。

因为若多个子查询，势必每次都 会查询此表，比如T_L_Line查询了五次，所以性能可想而知。
查询五次不如一次性查询出来.五次的查询总效率肯定不如一次性查询的效率。。

因不知你的查询目的，所以下面的答案可能 不对，只是提供个思路而已

WITH CTE AS(
SELECT  GprsId ,LineType ,Name,IsSubLine FROM T_L_Line ),
CTE2 AS 
(SELECT Longitude, Latitude,OrderNo FROM T_L_Station)


SELECT Id, LineId, ControlId, Direction, FieldNo, Remark, Longitude, Latitude, GprsId ,LineType ,Name,IsSubLine
(SELECT TOP (1) ScreenNo  FROM T_S_Controls AS f  WHERE (ControlId = a.ControlId)) AS ScreenNo
from T_S_ControlLine a join CTE b on b.LineId = a.LineId
                       join CTE2 c on c.LineId = a.LineId) AND c.Direction = a.Direction

 

[duanzhi1984]

SELECT min(Id), LineId, ControlId, Direction, FieldNo, Remark, Longitude
FROM 
(SELECT a_1.Id, a_1.LineId, a_1.ControlId, a_1.Direction, a_1.FieldNo, a_1.Remark,  
  T_L_Station.Longitude, T_L_Station.Latitude FROM 

(SELECT Id, LineId, ControlId, Direction, FieldNo, Remark  FROM T_S_ControlLine) AS a_1 
LEFT OUTER JOIN  T_L_Station ON a_1.LineId = T_L_Station.LineId AND    a_1.Direction = T_L_Station.Direction) AS A
group by LineId, ControlId, Direction, FieldNo, Remark, Longitude

[duanzhi1984]

根据你的排序进行TOP 1 即可

[非零传说]

这种写法是有点个吓人，效率会非常低的
写SQL语句的时候一定要注意语句的格式

[HsuChine]

[Quote=引用 12 楼 zcwzf 的回复:]
这种写法是有点个吓人，效率会非常低的
写SQL语句的时候一定要注意语句的格式
[/Quote]

言之有理

[noobw]

恐怖！！工作了1年还没遇到这么恐怖的！