表达式 求第二最小数

wwwwa

Set ff = CurrentDb.OpenRecordset("SELECT TOP 1 * FROM (SELECT * from 表1 where sl>(select min(sl) from 表1)) WHERE ID<7 ORDER BY sl")

myszh

再修改　　通过查询　　分别求出　　按MZ 　A   B　。。各自的第二最小数字

[此贴子已经被作者于2007-4-9 10:32:32编辑过]

wwwwa

1

SELECT *
FROM [SELECT *,(select count(*) from 表1 where a.mz=mz and a.sl>sl) as dd
FROM 表1 a order by 3,mz]. AS [%$##@_Alias]
WHERE dd=1;
2

SELECT mz, min(sl)
FROM [select a.* from 表1 a inner join (
select mz,min(sl) as msl from 表1 group by mz) b on a.mz=b.mz and a.sl>b.msl]. AS [%$##@_Alias]
GROUP BY mz;

wwwwa

3

select mz,max(sl) from (
SELECT *
FROM 表1 AS a
WHERE sl in(select top 2 sl from 表1 where a.mz=mz order by sl)) group by mz

myszh

３步完成？　　怎么好像3个查询没有任何关系？　还有[%$##@_Alias]　是怎么回事呢？
　

wwwwa

4

SELECT *
FROM 表1 AS a
WHERE sl in(select top 1 sl from 表1 where a.mz=mz and sl>dmin('sl','表1','mz= "' & a.mz & '"') order by sl)

wwwwa

以上4种方法均可达到目的。

wwwwa

5

SELECT *
FROM 表1 AS a1
WHERE sl =(select top 1 sl from (SELECT *
FROM 表1 AS a
WHERE sl in(select top 2 sl from 表1 where a.mz=mz order by sl)
) where a1.mz=mz order by sl desc);

wwwwa

6

select * from (
SELECT (select count(*) from 表1 where sl<=a.sl and mz=a.mz) AS js, *
FROM 表1 AS a
ORDER BY a.MZ, a.SL) where js=2
通过对JS＝进行操作，可取出第N个最小数，

在取之前判断一下，选择即可，也可以用VB

A编程，思路如上述。