用 limit,top,in 我都会写,但是不用的话真想不出来 T_T
select x.* from (select * from test order by b limit 2 ) x order by x.b desc limit 1;
top 和 limit 一样
select min (b ) from page_test where b not in
(select min (b ) from page_test );
已经想了好久了,用 count?还是用高级的函数?
1
realpg 2015-08-30 17:31:51 +08:00 1
MYSQL:
select min (xxx ) from table where xxx> (select min (xxx ) from table ); |
2
realpg 2015-08-30 17:45:18 +08:00
更正,你这需求不太明确:
假设 table 表结构如下 pk (主键) xxx 如果值那一列为 1 1 2 3 4 5 6 7 第二小的是 1 还是 2 ?如果单纯按照值来算 第一小的是 1 ,第二小的是 2 如果按照列排序的方式算,第一小的是 1 第二小的还是 1 第一种方式: select min (xxx ) from `table` where xxx> (select min (xxx ) from `table`); 第二种方式: select min (xxx ) from `table` where xxx>=(select min (xxx ) from `table`) and pk<> (select pk from (select pk,min (xxx ) from `table`) a ); |
3
phx13ye 2015-08-30 18:42:47 +08:00
有一道题叫做 Nth Highest Salary ,解法是
SELECT DISTINCT Salary FROM Employee Emp1 WHERE (N-1 ) = (SELECT COUNT (DISTINCT (Salary )) 你可以看一下, 具体思路是,对于这条第二小的记录,肯定有一条记录比他还小 |
4
phx13ye 2015-08-30 18:43:55 +08:00 1
没写完整
SELECT DISTINCT Salary FROM Employee Emp1 WHERE (N-1 ) = (SELECT COUNT (DISTINCT (Salary )) FROM Employee Emp2 WHERE Emp2.Salary > Emp1.Salary ) |
5
buckethead1 OP |