sql语句查询结果只取从第m条开始到第n条结束请问该怎么做 请问sqlserver，怎么查询第N到M条记录，其中ID为唯...
\u8bf7\u6559\u4e00\u4e0bsql\u8bed\u53e5limit \u4ece\u7b2c\u4e09\u4e2a\u6570\u636e\u5f00\u59cb\u5230\u7ed3\u675f,\u600e\u4e48\u5199?SELECT * FROM table LIMIT 2,-1
select top M-N * from \u8868\u540d where id in \uff08select distinct\uff08id\uff09 from \u8868\u540d\uff09
取n到m行1.
select top m * from tablename where id not in (select top n id from tablename order by id asc/*|desc*/)
2.
select top m * into 临时表(或表变量) from tablename order by columnname -- 将top m笔插入到临时表
set rowcount n --只取n条结果
select * from 表变量 order by columnname desc
3.
select top n * from
(select top m * from tablename order by columnname) a
order by columnname desc
4.如果tablename里没有其他identity列，那么：
先生成一个序列,存储在一临时表中.
select identity(int) id0,* into #temp from tablename
取n到m条的语句为：
select * from #temp where id0 > =n and id0 <= m
如果你在执行select identity(int) id0,* into #temp from tablename这条语句的时候报错,那是因为你的DB中间的select into/bulkcopy属性没有打开要先执行：
exec sp_dboption 你的DB名字,'select into/bulkcopy',true
5.如果表里有identity属性，那么简单：
select * from tablename where identity_col between n and m
6.SQL2005开始.可以使用row_number() over()生成行号
;with cte as
(
select id0=row_number() over(order by id),* from tablename
)
select * from cte where id0 between n to m
什么数据库?不同数据的sql是不同的.
mysql,postgresql用limit和offset.
sql server 用两次top.
firebird用rows.
其它的我不知道.
oracle的话语法如下
1.如果需要id排序
select * from (
select t.* , rownum as seq from (
select * from yourTable
order by id )t)
where seq>= m and seq<= n ;
2.如果不需要id排序
select *
from (select t.*, rownum as seq from yourTable t)
where seq >= m
and seq <= n
SELECT TOP n-m+1 * FROM 表名 WHERE (id NOT IN (SELECT TOP m-1 id FROM 表名))
select * from table limit m , (n-m);
绛旓細濡傛灉浣犵殑鏌ヨ缁撴灉鏄寜鏌愬瓧娈靛浐瀹氭帓搴忕殑锛堣褰曞湪琛ㄤ腑鐨勫簭鍙锋槸鍥哄畾鐨勶級锛屽彲浠ョ敤浜屾ゼ鐨勬柟娉曪紝涓嶈繃搴旇鍐欐垚锛歴elect top n-m * from 琛 where 涓婚敭 not in (select top m 涓婚敭 from 琛)杩欐墠鏄浠巑+1鑷硁琛岀殑璁板綍,浜屾ゼ鍐欑殑鏄粠m+1寮濮嬬殑n琛岃褰 ...
绛旓細by 鎺掑簭鏉′欢 select * from #table order by 鎺掑簭鏉′欢 璇存槑:杩欐槸鐢ㄨ櫄鎷熻〃鍐欑殑,鏂规硶姣旇緝绗,绠鍗曠殑閾佸畾浼氭湁,浣犲鎯虫兂 鎴戞槸閲囧彇鍙栧嚭鏉ョ洰鐨勮褰,鐒跺悗杩囨护鎺変笉瑕佺殑璁板綍,鍓╀笅鐨勫氨鏄綘鎯宠鐨勪簡 鍥犱负浣犳槸瑕200~300鏉,鎵浠ュ繀椤昏鏈夋寜浠涔堟潵鎺掑簭鐨勬潯浠,鎺掑簭涓嶅悓,缁撴灉涔熶笉鍚岀殑 浣犺瘯璇,OK,璇烽噰绾 ...
绛旓細select a.*from 琛ㄥ悕 a inner join ( select A,max(B) B,max(C) C from 琛ㄥ悕 group by A ) b on a.A=b.A and a.B=b.B and a.C=b.
绛旓細鍦璇彞鏈鍚庡姞涓 鏄剧ず limit n 鍓峮琛 limit n,m 绗琻琛屽埌绗琻+m琛 order by colname desc limit n 鍚巒琛
绛旓細鎵浠ユ垜浠湪鎯虫槸涓嶆槸浠庢暟鎹簱鍙栧嚭鏃舵瀯閫犵壒瀹鐨凷QL璇彞鍙彇鍑鏈〉鐨勫唴瀹癸紵姣斿鎴戞兂鍙栧嚭绗浜岄〉鐨勮褰曪紝SQL璇彞杩涜杩欐牱鐨勬瀯閫狅細瀛鏌ヨ鍙栧嚭鍓2椤电殑鍐呭锛岀劧鍚庡啀鍦ㄥ瓙鏌ヨ涓彇鍑哄悗涓椤电殑鍐呭锛屼篃灏辨槸绗簩椤电殑鍐呭銆傚熀浜庤繖绉嶆兂娉曞幓鏋勯燬QL锛屾瘮濡傛兂鍙栧嚭绗21鏉″埌绗40鏉＄殑鍐呭锛屽綋鐒舵槸姣忛〉鏄剧ず20鏉¤褰曟椂锛屽彲浠...
绛旓細浣犳槸宸茬粡鏌ヨ鍑烘潵浜嗗緢澶氳銆備絾鏄彧瑕 绗竴琛屾暟鎹紵Oracle 鐨勮瘽锛歋ELECT FROM (浣犵殑閭ｄ釜鏌ヨ )WHERE ROWNUM = 1;DB2 鐨勮瘽锛氫綘鐨勯偅涓煡璇 fetch first 1 rows only;SQL Server 鐨勮瘽锛歋ELECT TOP 1 浣犵殑鏌ヨ鐨勫叾浠栧垪 FROM 浣犵殑琛 WHERE 浣犵殑鏉′欢 MySQL 鐨勮瘽锛氫綘鐨勯偅涓煡璇 LIM...
绛旓細left(瀛楃涓,瑕鍙鐨勫瓧绗︽暟)浠庡乏杈圭涓涓捣鍙栧瓧绗︿覆鐨凬涓瓧绗 妤间笂姝ｈВ
绛旓細鍦璇彞鏈鍚庡姞涓 鏄剧ず limit n 鍓峮琛 limit n,m 绗琻琛屽埌绗琻+m琛 order by colname desc limit n 鍚巒琛
绛旓細substring('2009/2/5',1,6)SUBSTRING (<expression>锛 <starting_ position>锛 length)杩斿洖浠庡瓧绗︿覆宸﹁竟绗瑂tarting_ position 涓瓧绗﹁捣length涓瓧绗︾殑閮ㄥ垎銆俵eft('2009/2/5',6)LEFT (<character_expression>锛 <integer_expression>)杩斿洖character_expression 宸﹁捣 integer_expression 涓瓧绗︺--- CONV...
绛旓細SELECT T1.* FROM consign_date T1 INNER JOIN (SELECT [绠卞彿] as Y,max([鏃ユ湡]) as M FROM consign_date group by [绠卞彿]) T2 ON T1.[绠卞彿]=T2.[Y] AND T1.[鏃ユ湡]=T2.[M]
