表名:yuangongId(主键) Id3(主键) Name salary1 a tom 3000
2 b jac 3000
3 c tomy 3000
1 b tomt 9000
2 bb jacky 3000
3 bc tom 3000
6 bnb tomn 3000
要查出第4到第6条数据:
1 b tomt 9000
2 bb jacky 3000
3 bc tom 3000
SQL语句:
select top 3 * from yuangn where Id not in(select top 3 Id from yuangn)
这样的话查出: 6 bnb tomn 3000 这条数据 是错误的
请问要怎么查出第4到第6条数据
可以用子查询吗?
2 b jac 3000
3 c tomy 3000
1 b tomt 9000
2 bb jacky 3000
3 bc tom 3000
6 bnb tomn 3000
要查出第4到第6条数据:
1 b tomt 9000
2 bb jacky 3000
3 bc tom 3000
SQL语句:
select top 3 * from yuangn where Id not in(select top 3 Id from yuangn)
这样的话查出: 6 bnb tomn 3000 这条数据 是错误的
请问要怎么查出第4到第6条数据
可以用子查询吗?
if object_id('tempdb.dbo.#A') is not null drop table #A
go
create table #A (Id int,Id3 varchar(3),Name varchar(5),salary int)
insert into #A
select 1,'a','tom',3000 union all
select 2,'b','jac',3000 union all
select 3,'c','tomy',3000 union all
select 1,'b','tomt',9000 union all
select 2,'bb','jacky',3000 union all
select 3,'bc','tom',3000 union all
select 6,'bnb','tomn',3000select IDD=IDENTITY(INT,1,1),* INTO #T from #A
SELECT * FROM #T WHERE IDD BETWEEN 4 AND 6/*IDD Id Id3 Name salary
----------- ----------- ---- ----- -----------
4 1 b tomt 9000
5 2 bb jacky 3000
6 3 bc tom 3000(所影响的行数为 3 行)
go
create table #A (Id int,Id3 varchar(3),Name varchar(5),salary int)
insert into #A
select 1,'a','tom',3000 union all
select 2,'b','jac',3000 union all
select 3,'c','tomy',3000 union all
select 1,'b','tomt',9000 union all
select 2,'bb','jacky',3000 union all
select 3,'bc','tom',3000 union all
select 6,'bnb','tomn',3000
with t1 as
(
select *, row_number() over(order by getdate()) sn from #A
)
--select top(3)* from t1 where sn not in(select top(3) sn from t1)
--select * from t1 where sn between 4 and 6
select top(3) * from t1 where sn>(select max(sn) from (select top(3) sn from t1) t)
--上面的3个任何一个都可以--方法同上面
select *, identity(int,1,1) as sn into #B from #A
select * from #B where sn between 4 and 6
where cast(id as nvarchar(5))+id3 in (select top 6 cast(id as nvarchar(5))+id3 from a)
and cast(id as nvarchar(5))+id3 not in(select top 3 cast(id as nvarchar(5))+id3 from a)这个我测试过了,是正确的。首先需要把id转化为nvarchar类型,然后和ID3合并为一个字符串(因为是复合主键)。有了以上基础再查询主键在前六条但不在前三条的数据,就可以得到你要的效果了。希望对你有帮助。
--测试表
create table A (Id int,Id3 varchar(3),Name varchar(5),salary int)
insert into A
select 1,'a','tom',3000 union all
select 2,'b','jac',3000 union all
select 3,'c','tomy',3000 union all
select 1,'b','tomt',9000 union all
select 2,'bb','jacky',3000 union all
select 3,'bc','tom',3000 union all
select 6,'bnb','tomn',3000
--查询语句
select * from a
where cast(id as nvarchar(5))+id3 in (select top 6 cast(id as nvarchar(5))+id3 from a)
and cast(id as nvarchar(5))+id3 not in(select top 3 cast(id as nvarchar(5))+id3 from a)
Id(主键) Id3(主键) 联合主键
2005同row_number