看看是不是这个意思: select * from Table T where Fclass in(select Top 1 Fclass from Table where Fclass=T.Fclass Group by Fclass) Or Fid=@@IDENTITY
table(Fid,Fclass,Fnumber,Fname)select distinct Fid,(select top 1 Fclass,Fnumber,Fname where Fid=a.Fid order by newid()) from table a union select top 1 * from table order by newid()这样无法保证最后一条记录和其他记录不相同.如果实在是对这个要求很Care,那我感觉只能用临时表了.
上面的语句运行出错! 其实只要能拿出9条就可以了,因为最后一条是比较容易的。 可以保证最后一条记录不重复,只要加上一个条件:sleect * from table1 where Fid not in......
sorry 子查询中忘了写FROM子句select distinct Fid, (select top 1 Fclass,Fnumber,Fname from table where Fid=a.Fid order by newid()) from table a
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[chengji] [decimal](18, 2) NULL ,
CONSTRAINT [PK_chengji] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GOselect id,chengji from dbo.chengjiinsert chengji(chengji)
values(100)
insert chengji(chengji)
values(93.3)
insert chengji(chengji)
values(78.5)
insert chengji(chengji)
values(56.2)
insert chengji(chengji)
values(88)--测试
select top 1 id,chengji into #t from dbo.chengji ORDER BY NEWID()
select id,chengji from dbo.chengji
union all
select id,chengji from #t
drop table #t
谢谢 aw511(点点星灯) ,但我希望用我所用的表来解决,如果用你的解决方法,我还得再去研究你的想法,不是吗???
另外请注意:table表中可不止九条记录的啊,每个Fclass都会对应有很多条记录的,
是要多这么多条记录中取出10条记录,并且要符合以上条件!!!
select * from Table T where Fclass in(select Top 1 Fclass from Table where Fclass=T.Fclass Group by Fclass) Or Fid=@@IDENTITY
from table a
union
select top 1 * from table order by newid()这样无法保证最后一条记录和其他记录不相同.如果实在是对这个要求很Care,那我感觉只能用临时表了.
其实只要能拿出9条就可以了,因为最后一条是比较容易的。
可以保证最后一条记录不重复,只要加上一个条件:sleect * from table1 where Fid not in......
(select top 1 Fclass,Fnumber,Fname
from table
where Fid=a.Fid
order by newid())
from table a
但为什么我老是运行出错呢,把table改为table1之后还是运行不了,table是一个关键字
(select top 1 Fclass,Fnumber,Fname
from [table]
where Fid=a.Fid
order by newid())
from [table] a