表结构是
testtb
ID name
1 3
1 4
1 1
1 5
2 1
2 4
3 1
4 3
5 1
6 2
;统计要求是
查询出 name 小于2的客户,结果是
3 1
5 1
(在ID 相同的所有列里面,只要有一个列的name >=2, 就排除这一个ID)
testtb
ID name
1 3
1 4
1 1
1 5
2 1
2 4
3 1
4 3
5 1
6 2
;统计要求是
查询出 name 小于2的客户,结果是
3 1
5 1
(在ID 相同的所有列里面,只要有一个列的name >=2, 就排除这一个ID)
from testTB t1
where exists (select id, max(name)
from testTB t2
where t1.id = t2.id and t1.name=t2.name
group by id
having max(name) < 2)
WITH t AS (
SELECT '1' tid,3 tname FROM DUAL UNION ALL
SELECT '1' tid,4 tname FROM DUAL UNION ALL
SELECT '1' tid,1 tname FROM DUAL UNION ALL
SELECT '1' tid,5 tname FROM DUAL UNION ALL
SELECT '2' tid,1 tname FROM DUAL UNION ALL
SELECT '2' tid,4 tname FROM DUAL UNION ALL
SELECT '3' tid,1 tname FROM DUAL UNION ALL
SELECT '4' tid,3 tname FROM DUAL UNION ALL
SELECT '5' tid,1 tname FROM DUAL UNION ALL
SELECT '6' tid,2 tname FROM DUAL
)
-- 方法1:
SELECT t.tid,
MAX(t.tname)
FROM t
GROUP BY t.tid
HAVING SUM(CASE WHEN t.tname < 2 THEN 0 ELSE 1 END) = 0--方法2:
SELECT m.tid,
m.tname
FROM (
SELECT t.tid,
t.tname,
SUM(CASE
WHEN t.tname < 2 THEN
0
ELSE
1
END) OVER(PARTITION BY t.tid) rn
FROM t
) m
WHERE m.rn = 0
from testTB where id not in
(select distinct id from testTB where name >=2)
select id,name from
(
select id,max(name) name from testtb group by id
)
where name < 2