11,850
社区成员
发帖
与我相关
我的任务
分享
select
A.*
from
(
select
row_number() over (order by CreateTime desc) RowNumber,
*
from
(
select
A.*,
B.LotteryType,
B.State,
C.Account as UserAccount,
C.NickName as UserNickName
from
BettingUsers as A
inner join
Bettings as B
on
A.BettingID = B.ID
inner join
Users as C
on
A.UserID = C.ID
where
B.IsChipped = 1
) as A)
as A
where
A.RowNumber between 1 and 30
--先创建索引
CREATE INDEX ix_BettingUsers_CreateTime ON BettingUsers(CreateTime)
--执行稍简化后的SQL
SELECT A.*
FROM (
SELECT
ROW_NUMBER() OVER(ORDER BY CreateTime DESC) RowNumber,
A.*,
B.LotteryType,
B.State,
C.Account AS UserAccount,
C.NickName AS UserNickName
FROM BettingUsers AS A
INNER JOIN Bettings AS B
ON A.BettingID = B.ID
INNER JOIN Users AS C
ON A.UserID = C.ID
WHERE B.IsChipped = 1
) AS A
WHERE A.RowNumber BETWEEN 1 AND 30
给CreateTime 加一个索引
SELECT Identifier , --主键
ROW_NUMBER() OVER ( ORDER BY Identifier DESC ) AS RowIndex
INTO #tb1_1
FROM BettingUsers AS A
INNER JOIN Bettings AS B ON A.BettingID = B.ID
INNER JOIN Users AS C ON A.UserID = C.ID
WHERE B.IsChipped = 1
SELECT Identifier
INTO #tb1_2
FROM #tb1_1 AS t
WHERE t.RowIndex BETWEEN 1 AND 30
SELECT A.* ,
B.LotteryType ,
B.State ,
C.Account AS UserAccount ,
C.NickName AS UserNickName
FROM BettingUsers AS A
INNER JOIN Bettings AS B ON A.BettingID = B.ID
INNER JOIN Users AS C ON A.UserID = C.ID
WHERE B.IsChipped = 1
AND Identifier IN ( SELECT * --主键在临时表中
FROM #tb1_2 )
ORDER BY Identifier DESC
DROP TABLE #tb1_1
DROP TABLE #tb1_2
另外表关联字段加上索引;然后再贴出加上索引之后的执行计划看看。这样试试:另外表关联字段加上索引;然后再贴出加上索引之后的执行计划看看。SELECT Identifier , --主键 ROW_NUMBER() OVER ( ORDER BY Identifier DESC ) AS RowIndex INTO #tb1_1 FROM BettingUsers AS A INNER JOIN Bettings AS B ON A.BettingID = B.ID INNER JOIN Users AS C ON A.UserID = C.ID WHERE B.IsChipped = 1 SELECT Identifier INTO #tb1_2 FROM #tb1_1 AS t WHERE t.RowIndex BETWEEN 1 AND 30 SELECT A.* , B.LotteryType , B.State , C.Account AS UserAccount , C.NickName AS UserNickName FROM BettingUsers AS A INNER JOIN Bettings AS B ON A.BettingID = B.ID INNER JOIN Users AS C ON A.UserID = C.ID WHERE B.IsChipped = 1 AND Identifier IN ( SELECT * --主键在临时表中 FROM #tb1_2 ) ORDER BY Identifier DESC DROP TABLE #tb1_1 DROP TABLE #tb1_2
select
top 10
A.*,
B.LotteryType,
B.State,
C.Account as UserAccount,
C.NickName as UserNickName
from
BettingUsers as A
inner join
Bettings as B
on
A.BettingID = B.ID
inner join
Users as C
on
A.UserID = C.ID
where
B.IsChipped = 1
上面这个SQL秒出结果,但是下面这个加了排序的就要5秒钟了,求解。。
select
top 10
A.*,
B.LotteryType,
B.State,
C.Account as UserAccount,
C.NickName as UserNickName
from
BettingUsers as A
inner join
Bettings as B
on
A.BettingID = B.ID
inner join
Users as C
on
A.UserID = C.ID
where
B.IsChipped = 1
order by A.CreateTime desc