加一个自增列,如果有就不用加。
alter table datatable add new_id int identityselect * from datatable A where new_id in (select top 10 new_id from datatable where typeid=A.typeid order by new_id)
alter table datatable add new_id int identityselect * from datatable A where new_id in (select top 10 new_id from datatable where typeid=A.typeid order by new_id)
where a.typeid = b.id group by order by b.id
select identity(int,1,1) id,* from #临时表
select * from #临时表 tem where 编号 in (select top 10 编号 from #临时表 where typeid=tem.typeid)
as
select identity(1,1) id,* into #temp from datatable
select * from datatable d where id in (select top 10 id from datatable where typeid=d.typeid)
go
执行:exec sel_top
as
set nocount on
select identity(1,1) id,* into #temp from datatable
select * from datatable d where id in (select top 10 id from datatable where typeid=d.typeid)
drop table #temp
set nocount off
go
执行:exec sel_top
as
set nocount on
select identity(1,1) id,* into #temp from datatable
select * from datatable d where id in (select top 10 id from #temp where typeid=d.typeid)
drop table #temp
set nocount off
go
执行:exec sel_top
--创建数据测试环境
create table typetable(typeid int,type varchar(20))
create table datatable(id int,value varchar(20))insert into typetable(typeid,type) values(1,'A类型')
insert into typetable(typeid,type) values(2,'B类型')
insert into typetable(typeid,type) values(3,'C类型')insert into datatable(id,value) values(1,'划不来')
insert into datatable(id,value) values(1,'水来')
insert into datatable(id,value) values(1,'划后来')
insert into datatable(id,value) values(1,'垢来')
insert into datatable(id,value) values(2,'遥感技术')
insert into datatable(id,value) values(2,'晃荡')
insert into datatable(id,value) values(3,'人工')
insert into datatable(id,value) values(3,'飘散')
insert into datatable(id,value) values(3,'烟消云散')
insert into datatable(id,value) values(1,'临时工')
insert into datatable(id,value) values(3,'嘬')
insert into datatable(id,value) values(3,'老于世故')
insert into datatable(id,value) values(2,'虚荣')
insert into datatable(id,value) values(1,'超期')
insert into datatable(id,value) values(2,'回暖')--生成临时表
select myid=identity(int,1,1),b.id,a.type,b.value
into #temp
from typetable a inner join datatable b
on a.typeid=b.id
order by b.id--得到结果,因为我的数据较少,帮只显示前3行,只要将 top 3 改为 top 10 就可以满足你的要求了.
select id,type,value
from #temp a
where myid in (select top 3 myid from #temp where id=a.id)go
--用存储过程的方式
create procedure test
as
set nocount on
select myid=identity(int,1,1),b.id,a.type,b.value
into #temp
from typetable a inner join datatable b
on a.typeid=b.id
order by b.idselect id,type,value
from #temp a
where myid in (select top 3 myid from #temp where id=a.id)
set nocount off
go--调用存储过程
exec test
rollback tran
将
where myid in (select top 3 myid from #temp where id=a.id)
改为
where myid in (select top 10 myid from #temp where id=a.id)
就可满足你的要求.
select a.id,b.type,a.value from datatable as A join typetable B on a.id=b.typeid
where value in ( select top 3 value from datatable where id=A.id)
如果有主键:
select * from datatable tem where 主键 in (select top 10 主键 from datatable where typeid=tem.typeid)
--关键是后面的 where typeid=tem.typeid如果你没有主键:create proc 过程名
as
select identity(int,1,1) 编号,* into #临时表 from datatable
select * from #临时表 tem where 编号 in (select top 10 编号 from #临时表 where typeid=tem.typeid)
go--关键是identity(int,1,1),* into 出带唯一标识的列
数据库不在行,这个A是什么用法?
尤其感谢 大力和zjcxc(邹建)
讲解得够详细!
如果datatable有主键字段x
select * from datatable A,typetable b where a.x in (select top 10 x from datatable where typeid=A.typeid order by x)
and a.typeid=b.id哈,哈哈,哈哈哈......
where cast(col1 as char) + cast(col2 as char) + ... in
(select top 10 cast(col1 as char) + cast(col2 as char) + ... from datatable
where typeid = A.typeid)