表中根据不同条件输出10个值并组合，怎么写？例如：

[ybfq2]

例如：
select top 10 * from table1 where f=1 order by id desc
select top 10 * from table1 where f=2 order by id desc
select top 10 * from table1 where f=3 order by id desc
select top 10 * from table1 where f=4 order by id desc
我要这些结果的组合，就是说我要输出40个值，
当f=1根据id排序输出10个，f=2,f=3,f=4都是如此
这个的T-SQL怎么写？可能要用到存储过程，大家帮忙

[ybfq2]

不要用临时表阿，操作不方便，哪位写个存储过程阿

[ybfq2]

我已解决：
两个：
SELECT * FROM table1 a
WHERE 10>(SELECT COUNT(1) FROM table1 b WHERE a.f=b.f AND b.id>a.id) AND f IN(1,2,3,4)
ORDER BY f,ID DESC


Select * From table1
Where ID In (Select TOP 10 ID From table1 Where f=A.f)
And A.f In (1,2,3,4)
ORDER BY f,ID DESC

都可以，谢谢大家

[paoluo]

Select * From table1
Where ID In (Select TOP 10 ID From table1 Where f=A.f ORDER BY f,ID DESC )
And A.f In (1,2,3,4)
ORDER BY f,ID DESC

[paoluo]

當然這個也是可以，如果ID不是自增列或者有重復的情況下可食用


Select * From(
select top 10 * from table1 where f=1 order by id desc) A
Union All
Select * From(
select top 10 * from table1 where f=2 order by id desc) B
Union All
Select * From(
select top 10 * from table1 where f=3 order by id desc) C
Union All
Select * From(
select top 10 * from table1 where f=4 order by id desc ) D

[lxzm1001]

用 union all

[ybfq2]

能否给出详细代码？谢谢

[LouisXIV]

--能否给出详细代码？谢谢
----------------------------------
select top 10 * from table1 where f=1 order by id desc
union all
select top 10 * from table1 where f=2 order by id desc
union all
select top 10 * from table1 where f=3 order by id desc
union all
select top 10 * from table1 where f=4 order by id desc

[lxzm1001]

select top 10 * from table1 where f=1
union all
select top 10 * from table1 where f=2
union all
select top 10 * from table1 where f=3
union all
select top 10 * from table1 where f=4
order by id desc

[ybfq2]

select top 10 * from table1 where f=1 order by id desc
union all
select top 10 * from table1 where f=2 order by id desc
union all
select top 10 * from table1 where f=3 order by id desc
union all
select top 10 * from table1 where f=4 order by id desc
----------------------------------

关键字 'union' 附近有语法错误。

查了帮助：
如果使用 UNION 运算符，那么各个 SELECT 语句不能包含它们自己的 ORDER BY 或 COMPUTE 子句。而只能在最后一个 SELECT 语句的后面使用一个 ORDER BY 或 COMPUTE 子句；该子句适用于最终的组合结果集。只能在各个 SELECT 语句中指定 GROUP BY 和 HAVING 子句。

[ybfq2]

select top 10 * from table1 where f=1
union all
select top 10 * from table1 where f=2
union all
select top 10 * from table1 where f=3
union all
select top 10 * from table1 where f=4
order by id desc
----------------------------------

和我想要的结果就不一样了，最后一个order by 是用于最终的组合结果集

[playwarcraft]

select top 10 * from table1 where f=1
union all
select top 10 * from table1 where f=2
union all
select top 10 * from table1 where f=3
union all
select top 10 * from table1 where f=4
order by f,id desc

[lxzm1001]

select top 10 * from table1 where f=1
union all
select top 10 * from table1 where f=2
union all
select top 10 * from table1 where f=3
union all
select top 10 * from table1 where f=4
order by f,id desc

[ybfq2]

order by f,id desc
----------------------------------

最后一个order by 是用于最终的组合结果集

和我想要的结果就不一样了

[playwarcraft]

f=1,2,3,4所以,先按1,2,3,4排的啊,然後才會按id排,所以結果是一樣的啊~~^^

----------------------
如果非要一個order一個,就用臨時表好了
select * into #t1 from (select top 10 * from table1 where f=1 order by id desc) A
select * into #t2 from (select top 10 * from table1 where f=2 order by id desc) B
select * into #t3 from (select top 10 * from table1 where f=3 order by id desc) C
select * into #t4 from (select top 10 * from table1 where f=4 order by id desc) D

select * from #t1
union all
select * from #t2
union all
select * from #t3
union all
select * from #t4