答案不对吧,11,12,13也满足吧。
WITH T AS
(SELECT 1 ID, 'c1' CARD_NO, 500 AMOUNT
FROM DUAL
UNION ALL
SELECT 2 ID, 'c2' CARD_NO, 1500 AMOUNT
FROM DUAL
UNION ALL
SELECT 3 ID, 'c2' CARD_NO, 500 AMOUNT
FROM DUAL
UNION ALL
SELECT 4 ID, 'c2' CARD_NO, 1300 AMOUNT
FROM DUAL
UNION ALL
SELECT 5 ID, 'c2' CARD_NO, 5500 AMOUNT
FROM DUAL
UNION ALL
SELECT 6 ID, 'c3' CARD_NO, 500 AMOUNT
FROM DUAL
UNION ALL
SELECT 7 ID, 'c3' CARD_NO, 700 AMOUNT
FROM DUAL
UNION ALL
SELECT 8 ID, 'c3' CARD_NO, 5500 AMOUNT
FROM DUAL
UNION ALL
SELECT 9 ID, 'c3' CARD_NO, 5500 AMOUNT
FROM DUAL
UNION ALL
SELECT 10 ID, 'c3' CARD_NO, 5500 AMOUNT
FROM DUAL
UNION ALL
SELECT 11 ID, 'c4' CARD_NO, 15500 AMOUNT
FROM DUAL
UNION ALL
SELECT 12 ID, 'c4' CARD_NO, 15500 AMOUNT
FROM DUAL
UNION ALL
SELECT 13 ID, 'c4' CARD_NO, 5500 AMOUNT
FROM DUAL)
SELECT ID
FROM (SELECT ID,
(SELECT COUNT(*) FROM T WHERE T.CARD_NO = T1.CARD_NO) C_COUNT,
T1.AMOUNT
FROM T T1) T2
WHERE T2.C_COUNT > = 3
AND T2.AMOUNT > 1000
WITH T AS
(SELECT 1 ID, 'c1' CARD_NO, 500 AMOUNT
FROM DUAL
UNION ALL
SELECT 2 ID, 'c2' CARD_NO, 1500 AMOUNT
FROM DUAL
UNION ALL
SELECT 3 ID, 'c2' CARD_NO, 500 AMOUNT
FROM DUAL
UNION ALL
SELECT 4 ID, 'c2' CARD_NO, 1300 AMOUNT
FROM DUAL
UNION ALL
SELECT 5 ID, 'c2' CARD_NO, 5500 AMOUNT
FROM DUAL
UNION ALL
SELECT 6 ID, 'c3' CARD_NO, 500 AMOUNT
FROM DUAL
UNION ALL
SELECT 7 ID, 'c3' CARD_NO, 700 AMOUNT
FROM DUAL
UNION ALL
SELECT 8 ID, 'c3' CARD_NO, 5500 AMOUNT
FROM DUAL
UNION ALL
SELECT 9 ID, 'c3' CARD_NO, 5500 AMOUNT
FROM DUAL
UNION ALL
SELECT 10 ID, 'c3' CARD_NO, 5500 AMOUNT
FROM DUAL
UNION ALL
SELECT 11 ID, 'c4' CARD_NO, 15500 AMOUNT
FROM DUAL
UNION ALL
SELECT 12 ID, 'c4' CARD_NO, 15500 AMOUNT
FROM DUAL
UNION ALL
SELECT 13 ID, 'c4' CARD_NO, 5500 AMOUNT
FROM DUAL)
SELECT ID
FROM (SELECT ID,
(SELECT COUNT(*) FROM T WHERE T.CARD_NO = T1.CARD_NO) C_COUNT,
T1.AMOUNT
FROM T T1) T2
WHERE T2.C_COUNT > = 3
AND T2.AMOUNT > 1000
11 C4 15500
12 C4 15500
13 C4 5500为什么不是三笔都大于1000??
where a.card_no in (
select t.card_no
from order t
group by t.card_no
having count(*)>=3 and count(*)=sum(case when t.amount>1000 then 1 else 0 end) )
select a.id
from order a
where a.card_no in (select t.card_no
from order t
group by t.card_no
having count(*) >= 3)
and a.amount > 1000