如果有主键,也可以这样: select * from 表名 where 主键 not in (select top 50 percent 主键 from 表名)
我要的是不排序的情况,是表中单纯的后50%,也没有自动增列行.是取它的物理的后50% ---如果没有字段列的话,先把数据放在一个有自增字段的临时表,然后从临时表里面读取;select identity(int,1,1) as id,* into #t from a select top 50 PERCENT * from #t Order By id Desc drop table #t
1.如果只是查询,那么就用Identity(Date_Type,Seed,Increment) as Column_name插入临时表,这样就按照原来的物理位置设置一个序号id,那么然后查询即可,就如phantomMan的做法2.如果不用临时表,那么这种方法也可以,但是必须要求每条纪录要有一个ID值,不要求是序号,也就是可以标识这条纪录和别的纪录不一样,排列可以杂乱无序,例如:create table #trans_tab(prod_name varchar(20),item_code varchar(20),qty numeric(18,0)) insert into #trans_tab select 'a001','a',100 union ALL select 'b001','a',55 union ALL select 'a001','b',50 union ALL select 'b001','b',11 union ALL select 'a001','c',30 union ALL select 'b001','c',80SELECT * FROM #trans_tab ---prod_name + item_code决定唯一一笔记录 /* prod_name item_code qty -------------------- -------------------- -------------------- a001 a 100 b001 a 55 a001 b 50 b001 b 11 a001 c 30 b001 c 80(6 row(s) affected) */ declare @sn int declare @snc varchar(10) declare @sqlA as nvarchar(4000) set @sn = 50 set @snc = convert(nvarchar(10),100 - @sn) /*取50%,那么余下100-50%=50%,如果是80%,那么余下20%,以次类推*/ set @sqlA =N'select * from #trans_tab T1 where not exists (select * from (select top '+ @snc + ' percent * from #trans_tab)T2 where T1.prod_name = T2.prod_name and T1. item_code = T2. item_code) ' execute sp_executesql @sqlA /* prod_name item_code qty -------------------- -------------------- -------------------- b001 b 11 a001 c 30 b001 c 80(3 row(s) affected) */ */
select * from 表名 where 主键 not in (select top 50 percent 主键 from 表名)
select * from 表名
where 主键 not in (select top 50 percent 主键 from 表名)
---如果没有字段列的话,先把数据放在一个有自增字段的临时表,然后从临时表里面读取;select identity(int,1,1) as id,* into #t from a
select top 50 PERCENT * from #t Order By id Desc
drop table #t
insert into #trans_tab
select 'a001','a',100
union ALL
select 'b001','a',55
union ALL
select 'a001','b',50
union ALL
select 'b001','b',11
union ALL
select 'a001','c',30
union ALL
select 'b001','c',80SELECT * FROM #trans_tab ---prod_name + item_code决定唯一一笔记录
/*
prod_name item_code qty
-------------------- -------------------- --------------------
a001 a 100
b001 a 55
a001 b 50
b001 b 11
a001 c 30
b001 c 80(6 row(s) affected)
*/
declare @sn int
declare @snc varchar(10)
declare @sqlA as nvarchar(4000)
set @sn = 50
set @snc = convert(nvarchar(10),100 - @sn)
/*取50%,那么余下100-50%=50%,如果是80%,那么余下20%,以次类推*/
set @sqlA =N'select * from #trans_tab T1
where not exists (select * from (select top '+ @snc +
' percent * from #trans_tab)T2 where T1.prod_name = T2.prod_name
and T1. item_code = T2. item_code) '
execute sp_executesql @sqlA
/*
prod_name item_code qty
-------------------- -------------------- --------------------
b001 b 11
a001 c 30
b001 c 80(3 row(s) affected)
*/
*/