SELECT
BRANCH_ADD,COUNT(TRANS_AMOUNT) AS TRANS
FROM
(SELECT
A.BRANCH_ADD,C.TRANS_AMOUNT
FROM
BRANCHES A,accTS B,TRANSactION C
WHERE
a.branch_code = b.branch_code
AND b.acct_no = c.acct_no
ORDER BY
BRANCH_ADD)
GROUP BY
BRANCH_ADD;
上述查询的输出：
为文本：
BRANCH_ADD TRANS
------------ ------
TOWN_HALL 2
CHAMRAJPET 4
CITY_MARKET 4
您需要一些子查询并为count = to max进行过滤
SELECT BRANCH_ADD,my_count AS max_value
FROM (
SELECT
A.BRANCH_ADD,count(C.TRANS_AMOUNT ) my_count
FROM BRANCHES A
INNER JOIN ACCTS B ON a.branch_code=b.branch_code
INNER JOIN TRANSACTION C ON b.acct_no=c.acct_no
GROUP BY A.BRANCH_ADD;
)
WHERE my_count = (
select max(my_count)
from (
SELECT
A.BRANCH_ADD,count(C.TRANS_AMOUNT ) my_count
FROM BRANCHES A
INNER JOIN ACCTS B ON a.branch_code=b.branch_code
INNER JOIN TRANSACTION C ON b.acct_no=c.acct_no
GROUP BY A.BRANCH_ADD;
)
)
使用ROW_NUMBER（）仅返回第一行：
SELECT t.BRANCH_ADD,t.TRANS FROM (
SELECT A.BRANCH_ADD,COUNT(C.TRANS_AMOUNT) TRANS,ROW_NUMBER() OVER (ORDER BY COUNT(C.TRANS_AMOUNT)) rn
FROM BRANCHES A
INNER JOIN ACCTS B ON A.BRANCH_CODE = B.BRANCH_CODE
INNER JOIN TRANSACTION C ON B.ACCT_NO = C.ACCT_NO
GROUP BY BRANCH_ADD
) t
WHERE t.rn = 1
如果您使用的是Oracle 12+版本，则也可以使用FETCH来完成此操作：
SELECT A.BRANCH_ADD,COUNT(C.TRANS_AMOUNT) TRANS
FROM BRANCHES A
INNER JOIN ACCTS B ON A.BRANCH_CODE = B.BRANCH_CODE
INNER JOIN TRANSACTION C ON B.ACCT_NO = C.ACCT_NO
GROUP BY BRANCH_ADD
ORDER BY TRANS
FETCH FIRST 1 ROWS ONLY
您可以使用分析功能
例如MIN(..) KEEP (DENSE_RANK FIRST ORDER BY ..) OVER (PARTITION BY ..)：
SELECT * FROM
(
SELECT A.BRANCH_ADD,C.TRANS_AMOUNT,MIN(C.TRANS_AMOUNT)
KEEP (DENSE_RANK FIRST ORDER BY C.TRANS_AMOUNT)
OVER (PARTITION BY 0) AS lowest
FROM BRANCHES A
JOIN ACCTS B ON B.branch_code = A.branch_code
JOIN TRANSACTION C ON C.acct_no = B.acct_no
)
WHERE TRANS_AMOUNT = lowest