- -其实说真的linq党表示毫无压力 var sb=(from c in TB select c).skip(30).take(10);
我觉得是用的row_number() over 去回答问题好点,我觉得主考官是从是考你对数据库的掌握程度来考的
Select top 10 * from TB where id not in (Select top 30 id from TB order by id) order by id
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY IDASC) AS ROWNUMBER,* FROM TB) A WHERE ROWNUMBER BETWEEN 31 and 40
select * from TB where id >= min(id)+31 and id <= min(id)+40
这不就是数据分页查询么。pagesize = 10 pageindex = 3
谁解释那什么意思!! 特别是OVER
oracle数据库的话: slect * from (select * ,rownum from tablename where rownum<4*10) where rownum>(4-1)*10
select * from TB where id >= min(id)+31 and id <= min(id)+40
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY IDASC) AS ROWNUMBER,* FROM TB) A WHERE ROWNUMBER BETWEEN 31 and 40
SELECT TOP (10) * FROM (SELECT TOP (40) * FROM tab_orders ORDER BY ID DESC) AS derivedtbl_1
我还是觉得top in 好点,BETWEEN 的话,要是中间有数据删除了呢
SqlServer2005以后用这个,千万别用not in 效率很差
宁愿用left join后判断条件是否为null
SELECT TOP 10 * FROM ( SELECT TOP 40 * FROM TB ORDER BY ID DESC) ORDER BY ID ASC 要注意排序。
SELECT TOP 10 * FROM ( SELECT TOP 40 * FROM TB ORDER BY ID DESC) T ORDER BY ID ASC
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY IDASC) AS ROWNUMBER,* FROM TB) A WHERE ROWNUMBER BETWEEN 31 and 40
select * from TB where id >= min(id)+31 and id <= min(id)+40 这个很好啊
....top 10....in top 40... desc
楼主的写法没有错。 Select top 10 from TB where id not in (Select top 30 id from TB)这是row_number 的写法 select * from ( Select *,row_Number() over(oder by id asc) as row from TB ) as tab1 where 30<=tab1.row and tab1.row <40 ;
补 : 楼主最好还是 加上 order by id asc --- 才是不倒序Select top 10 * from TB where id not in (Select top 30 id from TB) order by id asc
Linq党在这方面是强项,汗啊,偶一直没学Linq
select * from (select Row_number() over(order by id) RowNums,* from TB) as temp where temp.RowNums between 30 and 39
MySql:select * from TB limit 30,40 order by id ASC;
主要应该考察的是 SQL 中的 子查询 和开窗函数的 Row_number()用法
我出的面试题里就有这题,其实我只要求写出来ROW_NUMBER,我就认为对了
但是用top的方式我也不会说不对,招人不会只看某一题的对错
这个写法比较支持或者用ROW_NUMBER,2者都可,唯独select * from tb where id>30 and id<=40不对!
Select top 10 * From (Select top 40 * From D划价数据 order by ID) A Order by ID desc肯定用这个哈.一次扫描就可以解决.
select top 10 * from (select top 40 * from tb order by id) order by id desc
以前用row_number() 现在公司用2000的
select top 40 * from tb where id in(select top 10 from tb where id not in(select top 30 from tb)) 这样应该能也能查询出来,我还是学生,错了还请见谅,没在数据里去式差,直接在这里给你写的
select * from TB limit 10 30
select * from TB limit 30 10
不错,我也遇到好几次啦 我都是这么写的 select top 10 * from TB where id not in (select top 30 id from TB)
select * from TB where id beween 31 and 40 OR select * from tb where id>30 and id<41 之类的只适合tb的ID中间没有断档的情况,31,32,33,40这样,这只能是理想情况还是这样写比较通用些, Select top 10 * from TB where id not in (Select top 30 id from TB order by id) ORDER BY id
Select top 10 id from TB where id in (Select top 40 id from TB order by id desc) 这样也可以
我觉得面试的话,还是用row_number() 这个比较好,给自己加分啊!
原题是:查找出表 TB第31-40条数据,主键id不是连续的;楼主方法可以
网上不是有这一种吗? select top 10 * from table where Id<(select MIN(Id) from ( select top 20 Id from table order by Id desc) ) order by Id desc
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY IDASC) AS ROWNUMBER,* FROM TB) A WHERE ROWNUMBER BETWEEN 31 and 40
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY IDASC) AS ROWNUMBER,* FROM TB) A WHERE ROWNUMBER BETWEEN 31 and 40
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY IDASC) AS ROWNUMBER,* FROM TB) A WHERE ROWNUMBER BETWEEN 31 and 40
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY IDASC) AS ROWNUMBER,* FROM TB) A WHERE ROWNUMBER BETWEEN 31 and 40这个谁能解释一下?
select top10 * from (select top 40 * from tb order by id asc ) a order by a.id desc
你这样写的话查询出来的数据不准确 Select top 10 from TB where id not in (Select top 30 id from TB order by id)order by id 必须加上order by 不然的话 每次查询出来的数据会不同
在不确定TB表是否删除过数据的情况下,以下sql语句通用。 select * from ( select *,ROW_NUMBER() over(order by id) as rowid from TB ) t where rowid between 31 and 40
between 30 and 40是肯定不对的,面试的时候一定不要这样写。
方法1. WITH cte AS ( SELECT ID, ROW_NUMBER() OVER(ORDER BY IDASC) AS ROWNUMBER FROM TABLE ) SELECT * FROM cte WHERE ROWNUMBER > 30 AND ROWNUMBER < 40;方法2. SELECT TOP 10 a.* FROM TABLE AS a WHERE a.ID NOT EXISTS ( SELECT TOP 20 * FROM TABLE WHERE ID = a.ID );方法3. WITH cte AS ( SELECT TOP 30 FROM TABLE ORDER BY ID DESC ) SELECT TOP 10 * FROM cte;。。
这个题目其实就是想考你对分页sql语句的了解 降序: select top 10 id from TB where id<(select min(id) from (select top 30 id from TB order by id desc )) order by id desc 升序: select top 10 id from TB where id>(select max(id) from (select top 30 id from TB order by id asc )) order by id asc换成分页sql语句就是: int pagesize; int pageindex; select top pagesize id from TB where id<(select min(id) from (select top pagesize*(pageindex-1) id from TB order by id desc)) order by id asc
with tb AS ( select row_number() over(order by id asc) as rowID from tbName ) select * from aa where rowid between 31 and 40
晕,上面那个发错了 with tb AS ( select row_number() over(order by id asc) as rowID from tbName ) select * from tb where rowID between 31 and 40
select * from TB t where (select count(*) from TB t1 where t1.ID < t.ID) >= 31 and (select count(*) from TB t1 where t1.ID < t.ID) <= 40
刚才那个错了,假设数据行的号码是从1开始的,那么: select * from TB t where (select count(*) from TB t1 where t1.ID < t.ID) >= (31-1) and (select count(*) from TB t1 where t1.ID < t.ID) < 40
select * from Table_1 WHERE ID>= (SELECT min(ID) FROM Table_1)+30 AND ID<=(SELECT min(ID) FROM Table_1)+39
select * from tb where id>30 and id<=40
这个不对吗?
select * from TB where id beween 30 and 39如何?
所以select * from TB where id beween 30 and 39是错的
Panel pnl = new Panel();
LinkButton lbtn = new LinkButton();
lbtn.Text = ds.Tables[0].Rows[i][1].ToString();
lbtn.ID = ds.Tables[0].Rows[i][0].ToString();
lbtn.CommandArgument = ds.Tables[0].Rows[i][0].ToString();
lbtn.CommandName = "编辑";
pnl.Controls.Add(lbtn);
再将pnl 添加到页面控件上
var sb=(from c in TB
select c).skip(30).take(10);
去回答问题好点,我觉得主考官是从是考你对数据库的掌握程度来考的
WHERE ROWNUMBER BETWEEN 31 and 40
pageindex = 3
谁解释那什么意思!! 特别是OVER
slect * from (select * ,rownum from tablename where rownum<4*10)
where rownum>(4-1)*10
WHERE ROWNUMBER BETWEEN 31 and 40
SELECT TOP (10) *
FROM (SELECT TOP (40) *
FROM tab_orders
ORDER BY ID DESC) AS derivedtbl_1
SELECT TOP 40 * FROM TB ORDER BY ID DESC)
ORDER BY ID ASC
要注意排序。
SELECT TOP 40 * FROM TB ORDER BY ID DESC) T
ORDER BY ID ASC
WHERE ROWNUMBER BETWEEN 31 and 40
Select top 10 from TB
where id not in (Select top 30 id from TB)这是row_number 的写法
select * from
(
Select *,row_Number() over(oder by id asc) as row from TB
) as tab1
where 30<=tab1.row and tab1.row <40
;
楼主最好还是
加上 order by id asc --- 才是不倒序Select top 10 * from TB
where id not in (Select top 30 id from TB)
order by id asc
这样应该能也能查询出来,我还是学生,错了还请见谅,没在数据里去式差,直接在这里给你写的
我都是这么写的
select top 10 * from TB where id not in (select top 30 id from TB)
OR
select * from tb where id>30 and id<41
之类的只适合tb的ID中间没有断档的情况,31,32,33,40这样,这只能是理想情况还是这样写比较通用些,
Select top 10 * from TB
where id not in (Select top 30 id from TB order by id) ORDER BY id
where id in (Select top 40 id from TB order by id desc)
这样也可以
select top 10 * from table where Id<(select MIN(Id) from ( select top 20 Id from table order by Id desc) ) order by Id desc
WHERE ROWNUMBER BETWEEN 31 and 40
WHERE ROWNUMBER BETWEEN 31 and 40
WHERE ROWNUMBER BETWEEN 31 and 40
WHERE ROWNUMBER BETWEEN 31 and 40这个谁能解释一下?
where id not in (Select top 30 id from TB order by id)order by id 必须加上order by 不然的话 每次查询出来的数据会不同
select * from (
select *,ROW_NUMBER() over(order by id) as rowid from TB
) t
where rowid between 31 and 40
WITH cte AS
(
SELECT ID, ROW_NUMBER() OVER(ORDER BY IDASC) AS ROWNUMBER FROM TABLE
)
SELECT * FROM cte WHERE ROWNUMBER > 30 AND ROWNUMBER < 40;方法2.
SELECT TOP 10 a.* FROM TABLE AS a
WHERE a.ID NOT EXISTS
(
SELECT TOP 20 * FROM TABLE WHERE ID = a.ID
);方法3.
WITH cte AS
(
SELECT TOP 30 FROM TABLE ORDER BY ID DESC
)
SELECT TOP 10 * FROM cte;。。
自己查,基本的学习能力
降序:
select top 10 id from TB where id<(select min(id) from (select top 30 id from TB order
by id desc )) order by id desc
升序:
select top 10 id from TB where id>(select max(id) from (select top 30 id from TB order by id asc )) order by id asc换成分页sql语句就是:
int pagesize;
int pageindex;
select top pagesize id from TB where id<(select min(id) from (select top pagesize*(pageindex-1) id from TB order by id desc)) order by id asc
with tb AS
(
select row_number() over(order by id asc) as rowID from tbName
)
select * from aa where rowid between 31 and 40
with tb AS
(
select row_number() over(order by id asc) as rowID from tbName
)
select * from tb where rowID between 31 and 40
and (select count(*) from TB t1 where t1.ID < t.ID) <= 40
刚才那个错了,假设数据行的号码是从1开始的,那么:
select * from TB t where (select count(*) from TB t1 where t1.ID < t.ID) >= (31-1)
and (select count(*) from TB t1 where t1.ID < t.ID) < 40
select * from Table_1 WHERE ID>= (SELECT min(ID) FROM Table_1)+30 AND ID<=(SELECT min(ID) FROM Table_1)+39