帮忙写条sql语句

[秦始皇不抽烟]

就是想取得分组后表中第二大的id

例如：id fzu

5 1
6 1
7 2
8 2
9 2
取得第大id：
SELECT MAX(id) FROM table1
WHERE GROUP BY fzu
现在我想取得第二大怎么办？

[todayq]

select min(id)  from tb where id in(select top 2 id from tb 
order by id desc)

[EnForGrass]

[code=SQL]
 CREATE TABLE  WT
(
	id int,
	fzu  int
)
INSERT INTO WT
SELECT 5,1 UNION ALL 
SELECT 6,1 UNION ALL 
SELECT 7,2 UNION ALL 
SELECT 8,2 UNION ALL 
SELECT 9,2 

select t.* from 
(
	select max(id) id,fzu,row_number() over(order by fzu desc) as num from WT group by fzu 
) t
	 where t.num=2 

id          fzu         num
----------- ----------- --------------------
6           1           2

(1 行受影响)

[/code]

[shelly]

同意1楼的

[men52676521]

[Quote=引用 1 楼 的回复:]

SQL code
select min(id) from tb where id in(select top 2 id from tb
order by id desc)
[/Quote]

能实现

[你们是不是在找BUG]

SELECT TOP (1) id FROM
(SELECT TOP (2) id FROM table1
ORDER BY id DESC) AS temTable　order by id asc
这种写法可以去到范围的数据

[Wicho开源开发中]

    SELECT MAX(id) as id,fzu FROM (SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM (SELECT MAX(id) as id,fzu FROM table1 GROUP BY fzu) AS T)) AS D GROUP BY fzu  

[IT刀客]

一楼正解

[王子文龙]

[Quote=引用 5 楼 的回复:]
引用 1 楼 的回复:

SQL code
select min(id) from tb where id in(select top 2 id from tb
order by id desc)


+1
[/Quote]
肯定不行，人家要得是分组之后的第二位

[弯月满弦]

[Quote=引用 1 楼 的回复:]
SQL code

select min(id) from tb where id in(select top 2 id from tb
order by id desc)
[/Quote]
+++++++1

[XBodhi.]

 
SELECT TOP 1 * FROM tb WHERE IN (SELECT TOP 2 ID FROM tb GROUP BY ID ORDER BY ID DESC) 
ORDER BY ASC 

或
用 row_Number （） grouping 函数。

[xiaodong369258]

select max(id) from tb
where id not in(select max(id) from tb group by fzu )
group by fzu

[算盘]

[Quote=引用楼主 的回复:]
就是想取得分组后表中第二大的id

例如：id fzu

5 1
6 1
7 2
8 2
9 2
取得第大id：
SELECT MAX(id) FROM table1
WHERE GROUP BY fzu
现在我想取得第二大……
[/Quote]
同意。

[xb12369]

[Quote=引用 1 楼 的回复:]

SQL code
select min(id) from tb where id in(select top 2 id from tb
order by id desc)
[/Quote]

+1