主键分2个字段
A [INT]
B [INT]
想在这个表做类似以下的分页SELECT TOP 10
ID
FROM
T
WHERE
ID < (
SELECT MIN ID FROM (SELECT TOP 10 ID FROM T ORDER BY ID DESC) AS Temp
)
ORDER BY
ID DESC
以上取的是第11-20条记录
利用了前N条最小ID号作为筛选条件现在问题是,混合主键不能做单纯的 > 或者 < 比较
并且也取不到TOP N 中的最后一条记录如下表
A B3 1
3 2
3 3
3 4
3 52 1
2 2
2 3
2 4
2 5只是问题的一个简化版本,请教各位,有没有什么好的办法可以做到高效分页,
谢谢
A [INT]
B [INT]
想在这个表做类似以下的分页SELECT TOP 10
ID
FROM
T
WHERE
ID < (
SELECT MIN ID FROM (SELECT TOP 10 ID FROM T ORDER BY ID DESC) AS Temp
)
ORDER BY
ID DESC
以上取的是第11-20条记录
利用了前N条最小ID号作为筛选条件现在问题是,混合主键不能做单纯的 > 或者 < 比较
并且也取不到TOP N 中的最后一条记录如下表
A B3 1
3 2
3 3
3 4
3 52 1
2 2
2 3
2 4
2 5只是问题的一个简化版本,请教各位,有没有什么好的办法可以做到高效分页,
谢谢
SELECT TOP 10
ID
FROM
T
WHERE
ID < (
SELECT MIN ID+col FROM (SELECT TOP 10 ID+col FROM T ORDER BY ID DESC) AS Temp
)
ORDER BY
ID DESC
SELECT TOP 10
ID
FROM
T
WHERE
ID < (
SELECT MIN ltrim(ID)+ltrim(col) FROM (SELECT TOP 10 ltrim(ID)+ltrim(col) FROM T ORDER BY ID DESC) AS Temp
)
ORDER BY
ID DESC
假设
A = 5
B = 1并且都是1位数的
那么我可以 A * 10 + B
但是现在情况是A和B都是INT,没有说A或者B可以限制为多少位,或者说是9位数。
,是加不了的,非常感谢2楼的答复。
如果可以这么用的话,我已经把字段设为VARCHAR(20)了,谢谢
right('000000000' + ltrim(a),10) + right('000000000' + ltrim(b),10)如果不够,自己再加大位数.
是这样的A字段是INT可以为任意值,
B字段最多是个8位数那么
A * 100,000,000 + B
合并为一个BIGINT
这样应该是没问题的,至少要比VARCHAR要好点吧,不过假设2个字段都没有限制(合并为BIGINT会溢出),我想知道是否有其他更快的方式
A,B
FROM
tb
WHERE NOT EXISTS(
SELECT TOP 1 1
FROM (SELECT TOP 20 A,B FROM rfid_transaction_table ORDER BY seq_no ) AS Temp
WHERE tb.A<Temp.A AND tb.B<Temp.B
)
ORDER BY A,B
A B3 1
3 2
3 3
3 4
3 52 1
2 2
2 3
2 4
2 5假设我要选前3条,WHERE A < 4 AND B < 4会导致最后2条取不到
我是按照这2个列排序的,checksum不对吧?
汗
是我测试的表,忘了改成tb了。
SELECT TOP 10
A,B
FROM
tb
WHERE NOT EXISTS(
SELECT TOP 1 1
FROM (SELECT TOP 20 A,B FROM tb ORDER BY A,B) AS Temp
WHERE tb.A<Temp.A AND tb.B<Temp.B
)
ORDER BY A,B
CREATE TABLE T1(
[A] [INT],
[B] [INT]
)
GOINSERT INTO T1([A],[B]) SELECT 1,1
INSERT INTO T1([A],[B]) SELECT 1,2
INSERT INTO T1([A],[B]) SELECT 1,3
INSERT INTO T1([A],[B]) SELECT 1,4
INSERT INTO T1([A],[B]) SELECT 1,5INSERT INTO T1([A],[B]) SELECT 2,1
INSERT INTO T1([A],[B]) SELECT 2,2
INSERT INTO T1([A],[B]) SELECT 2,3
INSERT INTO T1([A],[B]) SELECT 2,4
INSERT INTO T1([A],[B]) SELECT 2,5INSERT INTO T1([A],[B]) SELECT 3,1
INSERT INTO T1([A],[B]) SELECT 3,2
INSERT INTO T1([A],[B]) SELECT 3,3
INSERT INTO T1([A],[B]) SELECT 3,4
INSERT INTO T1([A],[B]) SELECT 3,5CREATE UNIQUE CLUSTERED INDEX [idx_T1] ON [T1]([A] ASC, [B] ASC)
SELECT * FROM T1
SELECT TOP 7
A,B
FROM
T1
WHERE NOT EXISTS(
SELECT TOP 1 1
FROM (SELECT TOP 7 A,B FROM T1 ORDER BY A,B) AS Temp
WHERE T1.A<Temp.A AND T1.B<Temp.B
)
ORDER BY A,B结果如下
1 2
1 3
1 4
1 5
2 1
2 2
2 3
A,B
FROM
T1
WHERE NOT EXISTS(
SELECT TOP 1 1
FROM (SELECT TOP 1 * FROM (SELECT TOP 1 A,B FROM T1 ORDER BY A ,B )Temp ORDER BY A DESC,B DESC)t
WHERE T1.A<t.A OR (T1.A=t.A AND T1.B<t.B)
)
ORDER BY A,B
--8~14笔
SELECT TOP 7
A,B
FROM
T1
WHERE NOT EXISTS(
SELECT TOP 1 1
FROM (SELECT TOP 1 * FROM (SELECT TOP 8 A,B FROM T1 ORDER BY A ,B )Temp ORDER BY A DESC,B DESC)t
WHERE T1.A<t.A OR (T1.A=t.A AND T1.B<t.B)
)
ORDER BY A,B...
还是2005用ROW_NUMBER()比较方便.
不过还是谢谢你提供这个思路