3,491
社区成员
发帖
与我相关
我的任务
分享
with test as (
select '004' id, '张三' name, 20 amount, date'2010-05-11' pdate, 1 flag from dual union all
select '007' id, '李四' name, 50 amount, date'2010-06-23' pdate, 1 flag from dual union all
select '002' id, '王五' name, 10 amount, date'2010-06-23' pdate, 2 flag from dual union all
select '004' id, '张三' name, 100 amount, date'2010-07-20' pdate, 2 flag from dual)
SELECT newflag flag, COUNT(*) cnt, SUM(amount) amount
FROM (SELECT t.*,
CASE
WHEN (SELECT COUNT(*)
FROM test a
WHERE a.id = t.id
AND a.flag = 2) > 0 THEN
2
ELSE
1
END newflag
FROM test t)
GROUP BY newflag;