select name from table
where money=(select money
from(select count(0) as cou,money
from table
group by money
order by cou,money)
where rownum=1)
and rownum=1
order by time方法比较笨,见笑
where money=(select money
from(select count(0) as cou,money
from table
group by money
order by cou,money)
where rownum=1)
and rownum=1
order by time方法比较笨,见笑
--------------------------------------------------
SELECT NAME
FROM (SELECT NAME
FROM t_Test
WHERE Money = (SELECT Money
FROM (SELECT Money, COUNT(*)
FROM t_Test
GROUP BY Money
ORDER BY COUNT(*), Money)
WHERE Rownum = 1)
ORDER BY TIME)
WHERE Rownum = 1
另外,逻辑这么复杂,什么时候可以用pl/sql啊?
SQL*PLUS>select * from a;
NAME TIME MONEY
---------- ---------- ----------
aa 03-1月 -04 1000
bb 21-2月 -04 1000
cc 01-2月 -03 2000
aa 23-11月-04 4000
bb 04-3月 -03 2000
dd 04-3月 -01 1000
dd 02-3月 -04 4000已选择7行。已用时间: 00: 00: 01.02
SQL*PLUS>SELECT a.*
2 FROM A,
3 (
4 SELECT MONEY, COUNT(1), MIN(TIME) TIME FROM A GROUP BY MONEY) B
5 WHERE A.MONEY = B.MONEY
6 AND A.TIME = B.TIME;
NAME TIME MONEY
---------- ---------- ----------
dd 04-3月 -01 1000
cc 01-2月 -03 2000
dd 02-3月 -04 4000已用时间: 00: 00: 00.02
SQL*PLUS>
2 /NAME TIME MONEY
---------- -------------------- ----------
AA 20040103 1000
BB 20040221 1000
CC 20030201 2000
AA 20041123 4000
BB 20030304 2000
DD 20010304 1000
DD 20040302 4000選取了 7 列目前歷時: 00:00:00.78------------------------------------------SQL> SELECT * FROM
2 (
3 SELECT * FROM BAO_TEMP
4 WHERE MONEY IN
5 (
6 SELECT MONEY
7 FROM (SELECT MONEY,COUNT(*) AA FROM BAO_TEMP GROUP BY MONEY)
8 WHERE AA IN
9 (
10 SELECT MIN(AA)
11 FROM (SELECT MONEY,COUNT(*) AA FROM BAO_TEMP GROUP BY MONEY)
12 )
13 )
14 ORDER BY TIME
15 )
16 WHERE ROWNUM =1
17 /NAME TIME MONEY
---------- -------------------- ----------
CC 20030201 2000選取了 1 列
SQL> SELECT * FROM BAO_TEMP
2 WHERE (MONEY,TIME) IN (SELECT MONEY,TIME
3 FROM (SELECT * FROM (
4 SELECT MONEY, COUNT(1) AA, MIN(TIME) TIME
5 FROM BAO_TEMP GROUP BY MONEY)
6 ORDER BY AA,TIME
7 )
8 WHERE ROWNUM=1
9 )
10 /NAME TIME MONEY
---------- -------------------- ----------
CC 20030201 2000選取了 1 列目前歷時: 00:00:00.15