34,590
社区成员
发帖
与我相关
我的任务
分享
select
*
from
(
SELECT
ROW_NUMBER() over (order by li.id desc ) rn
,li.*
FROM
[ListInfo] li
left join UserAccounts ua on ua.UserID = li.UserID
where 1=1
-- and ua.[userstate] != 0
) lis
where
rn between 1 and 20
SELECT *
FROM ( SELECT row_number() OVER ( ORDER BY id DESC ) AS rn ,
*
FROM listinfo
WHERE EXISTS ( SELECT 1
FROM useraccounts
WHERE listinfo.userid = userid
AND userstate != 0 )
) list
WHERE rn BETWEEN 1 AND 20
SELECT TOP 10 *
FROM [ListInfo]
WHERE id > (
SELECT ISNULL(MIN(id), 0)
FROM (
SELECT TOP 20 id
FROM [ListInfo] li
INNER JOIN UserAccounts ua
ON ua.UserID = li.UserID
WHERE ua.[userstate] != 0
ORDER BY li.id DESC
) t
)
select
*
from
(
SELECT
ROW_NUMBER() over (order by li.id desc ) rn
,li.*
FROM
[ListInfo] li
inner join UserAccounts ua on ua.UserID = li.UserID
where 1=1
-- and ua.[userstate] != 0
) lis
where
rn between 1 and 20
SELECT *
FROM (
SELECT ROW_NUMBER() OVER ( ORDER BY li.id DESC ) rn ,
li.id
FROM [ListInfo] li
LEFT JOIN UserAccounts ua
ON ua.UserID = li.UserID
WHERE ua.[userstate] != 0
) a
JOIN [ListInfo] AS b
ON a.id = b.id
WHERE a.rn BETWEEN 1 AND 20