with a as (
select 1 id, 1 ci_shu, 10 shang_pin from dual union all
select 1 id, 2 ci_shu, 10 from dual union all
select 1 id, 2 ci_shu, 10 from dual union all
select 1 id, 2 ci_shu, 10 from dual union all
select 1 id, 2 ci_shu, 10 from dual union all
select 1 id, 1 ci_shu, 20 from dual union all
select 1 id, 1 ci_shu, 30 from dual union all
select 1 id, 1 ci_shu, 40 from dual union all
select 1 id, 1 ci_shu, 20 from dual union all
select 1 id, 3 ci_shu, 30 from dual union all
select 2 id, 3 ci_shu, 10 from dual
),
b as (
select 10 shang_pin, 1 state from dual union all
select 20 shang_pin, 1 from dual union all
select 30 shang_pin, 1 from dual union all
select 40 shang_pin, 1 from dual union all
select 50 shang_pin, 1 from dual
)
select*from (
select a.*,
count(distinct a.shang_pin) over(partition by a.id, a.ci_shu) ct
from a, b
where 1 = 1
and a.shang_pin = b.shang_pin
and b.state = 1
and 'time' = 'time') t1
where t1.ct > 3
;
刚刚内个有点问题,
SELECT *
FROM A t
WHERE (SELECT COUNT(1)
FROM A k
WHERE t. 客户编号 = k.客户编号
AND t.次数 = k.次数 AND EXISTS (SELECT 1
FROM B
WHERE B.商品 = K.商品
AND B.标识 = 0)
AND K.时间 BETWEEN 开始时间 AND 结束时间
GROUP BY 客户编号,次数) > 3
AND EXISTS (SELECT 1
FROM B
WHERE B.商品 = T.商品
AND B.标识 = 0)
AND T.时间 BETWEEN 开始时间 AND 结束时间
给你个蠢方法 你可以先用着 等大神有答案一起学习吧..
SELECT *
FROM A t
WHERE (SELECT COUNT(1)
FROM A k
WHERE t. 客户编号 = k.客户编号
AND t.次数 = k.次数 EXISTS (SELECT 1
FROM B
WHERE B.商品 = A.商品
AND B.标识 = 0)
AND A.时间 BETWEEN 开始时间 AND 结束时间
GROUP BY 客户编号,次数) > 3
AND EXISTS (SELECT 1
FROM B
WHERE B.商品 = A.商品
AND B.标识 = 0)
AND A.时间 BETWEEN 开始时间 AND 结束时间