SELECT a.zf,b.name FROM ( SELECT SUM() zf,nameid FROM fs GROUP BY nameid ORDER BY zf desc ) a INNER JOIN studen b ON a.nameid=b.nameid INNER JOIN ( SELECT MIN(zf) zf FROM( SELECT TOP 2 DISTINCT zf FROM ( SELECT SUM() zf,nameid FROM fs GROUP BY nameid ORDER BY zf desc ) x ORDER BY zf desc ) y ) c ON c.zf=a.zf未测试
SELECT a.zf,b.name FROM ( SELECT SUM() zf,nameid FROM fs GROUP BY nameid ) a INNER JOIN studen b ON a.nameid=b.nameid INNER JOIN ( SELECT MIN(zf) zf FROM( SELECT TOP 2 DISTINCT zf FROM ( SELECT SUM() zf,nameid FROM fs GROUP BY nameid ) x ORDER BY zf desc ) y ) c ON c.zf=a.zf
select nameid,sum() as s into tempa from fs group by nameid order by s descselect * from ( select nameid,s,pm=(select count(distinct s) from tempa a where a.nameid=b.nameid and a.s<=b.s) from tempa b ) c where pm=2
或者SELECT a.zf,b.name FROM ( SELECT SUM() zf,nameid FROM fs GROUP BY nameid ) a INNER JOIN studen b ON a.nameid=b.nameid INNER JOIN ( SELECT MAX(zf) zf FROM ( SELECT SUM() zf,nameid FROM fs GROUP BY nameid ) a WHERE zf< (SELECT TOP 1 SUM() zf,nameid FROM fs GROUP BY nameid ORDER BY zf DESC ) ) c ON c.zf=a.zf均未测试.
Select name,s From studen Where s=(Select top 1 s From studen Where s<(Select Max() From Studen) Order By s)
select nameid,s from (select nameid,sum() as s from f group by nameid )d where s= ( select s from ( select nameid,sum() as s from f group by nameid ) a where s <( select max(s) from ( select sum() as s from f group by nameid) c) limit 1);
select s.name,d.s from (select nameid,sum() as s from f group by nameid )d,s where s= ( select s from ( select nameid,sum() as s from f group by nameid ) a where s <( select max(s) from ( select sum() as s from f group by nameid) c) limit 1) and d.nameid=s.nameid;
SELECT a.zf,b.name
FROM (
SELECT SUM() zf,nameid
FROM fs
GROUP BY nameid
ORDER BY zf desc
) a
INNER JOIN studen b
ON a.nameid=b.nameid
INNER JOIN
(
SELECT MIN(zf) zf
FROM(
SELECT TOP 2 DISTINCT zf
FROM (
SELECT SUM() zf,nameid
FROM fs
GROUP BY nameid
ORDER BY zf desc
) x
ORDER BY zf desc
) y
) c
ON c.zf=a.zf未测试
FROM (
SELECT SUM() zf,nameid
FROM fs
GROUP BY nameid
) a
INNER JOIN studen b
ON a.nameid=b.nameid
INNER JOIN
(
SELECT MIN(zf) zf
FROM(
SELECT TOP 2 DISTINCT zf
FROM (
SELECT SUM() zf,nameid
FROM fs
GROUP BY nameid
) x
ORDER BY zf desc
) y
) c
ON c.zf=a.zf
select nameid,sum() as s into tempa from fs group by nameid order by s descselect * from (
select nameid,s,pm=(select count(distinct s) from tempa a where a.nameid=b.nameid and a.s<=b.s)
from tempa b ) c where pm=2
FROM (
SELECT SUM() zf,nameid
FROM fs
GROUP BY nameid
) a
INNER JOIN studen b
ON a.nameid=b.nameid
INNER JOIN (
SELECT MAX(zf) zf
FROM (
SELECT SUM() zf,nameid
FROM fs
GROUP BY nameid
) a
WHERE zf<
(SELECT TOP 1 SUM() zf,nameid
FROM fs
GROUP BY nameid
ORDER BY zf DESC
)
) c
ON c.zf=a.zf均未测试.
where s=
( select s from (
select nameid,sum() as s from f group by nameid )
a where s <(
select max(s) from (
select sum() as s from f group by nameid) c)
limit 1);
where s=
( select s from (
select nameid,sum() as s from f group by nameid )
a where s <(
select max(s) from (
select sum() as s from f group by nameid) c)
limit 1) and d.nameid=s.nameid;