17,140
社区成员




--练习一下
--1.
with tt as (
select date'2010-03-02' sale_date, '001' employee_id, 'C1' client_id, 'P1' product_id from dual union all
select date'2010-03-02' sale_date, '001' employee_id, 'C1' client_id, 'P2' product_id from dual union all
select date'2010-03-02' sale_date, '002' employee_id, 'C1' client_id, 'P1' product_id from dual union all
select date'2010-03-02' sale_date, '002' employee_id, 'C2' client_id, 'P1' product_id from dual union all
select date'2010-03-02' sale_date, '002' employee_id, 'C2' client_id, 'P2' product_id from dual union all
select date'2010-03-02' sale_date, '002' employee_id, 'C1' client_id, 'P1' product_id from dual
)
SELECT sale_date, employee_id, client_id, product_id
FROM (SELECT a.*, COUNT(1) over(PARTITION BY sale_date, employee_id, client_id) cn
FROM (SELECT DISTINCT sale_date, employee_id, client_id, product_id FROM tt) a)
WHERE cn > 1;
--2.
with tt as (
select date'2010-03-02' sale_date, '001' employee_id, 'C1' client_id, 'P1' product_id from dual union all
select date'2010-03-02' sale_date, '001' employee_id, 'C1' client_id, 'P2' product_id from dual union all
select date'2010-03-02' sale_date, '002' employee_id, 'C1' client_id, 'P1' product_id from dual union all
select date'2010-03-02' sale_date, '002' employee_id, 'C2' client_id, 'P1' product_id from dual union all
select date'2010-03-02' sale_date, '002' employee_id, 'C2' client_id, 'P2' product_id from dual union all
select date'2010-03-02' sale_date, '002' employee_id, 'C1' client_id, 'P1' product_id from dual
)
SELECT a.*
FROM tt a,
(SELECT sale_date, employee_id, client_id
FROM (SELECT DISTINCT sale_date, employee_id, client_id, product_id FROM tt)
GROUP BY sale_date, employee_id, client_id
HAVING COUNT(1) > 1) b
WHERE a.sale_date = b.sale_date AND
a.employee_id = b.employee_id AND
a.client_id = b.client_id;
select sale_date, employee_id,client_id,product_id,
from tb a
where exists
(select 1 from tb b where b.employee_id = a.employee_id
and a.client_id = b.client_id
group by sale_date,employee_id
having count(1)>=2);
CREATE TABLE a(
sale_date DATE,
employee_id CHAR(4),
client_id CHAR(4),
product_id CHAR(4)
);
INSERT INTO a(sale_date,employee_id,client_id, product_id) VALUES(to_date('20100302','yyyymmdd'), '001', 'C1', 'P1');
INSERT INTO a(sale_date,employee_id,client_id, product_id) VALUES(to_date('20100302','yyyymmdd'), '001', 'C1', 'P2');
INSERT INTO a(sale_date,employee_id,client_id, product_id) VALUES(to_date('20100302','yyyymmdd'), '002', 'C1', 'P1');
INSERT INTO a(sale_date,employee_id,client_id, product_id) VALUES(to_date('20100302','yyyymmdd'), '002', 'C2', 'P1');
INSERT INTO a(sale_date,employee_id,client_id, product_id) VALUES(to_date('20100302','yyyymmdd'), '002', 'C2', 'P2');
INSERT INTO a(sale_date,employee_id,client_id, product_id) VALUES(to_date('20100302','yyyymmdd'), '002', 'C1', 'P1');
SELECT to_char(a1.sale_date,'yyyymmdd'), a1.employee_id, a1.client_id, a1.product_id
FROM a a1
WHERE EXISTS (SELECT 1
FROM a a2
WHERE to_char(a2.sale_date,'yyyymmdd')=to_char(a1.sale_date,'yyyymmdd')
AND a2.employee_id= a1.employee_id
AND a2.client_id=a1.client_id
GROUP BY to_char(a2.sale_date,'yyyymmdd'), a2.employee_id, a2.client_id
HAVING COUNT(DISTINCT a2.product_id)>=2 );
CREATE TABLE a(
sale_date DATE,
employee_id CHAR(4),
client_id CHAR(4),
product_id CHAR(4)
);
INSERT INTO a(sale_date,employee_id,client_id, product_id) VALUES(to_date('20100302','yyyymmdd'), '001', 'C1', 'P1');
INSERT INTO a(sale_date,employee_id,client_id, product_id) VALUES(to_date('20100302','yyyymmdd'), '001', 'C1', 'P2');
INSERT INTO a(sale_date,employee_id,client_id, product_id) VALUES(to_date('20100302','yyyymmdd'), '002', 'C1', 'P1');
INSERT INTO a(sale_date,employee_id,client_id, product_id) VALUES(to_date('20100302','yyyymmdd'), '002', 'C2', 'P1');
INSERT INTO a(sale_date,employee_id,client_id, product_id) VALUES(to_date('20100302','yyyymmdd'), '002', 'C2', 'P2');
INSERT INTO a(sale_date,employee_id,client_id, product_id) VALUES(to_date('20100302','yyyymmdd'), '002', 'C1', 'P1');
SELECT to_char(a1.sale_date,'yyyymmdd'), a1.employee_id, a1.client_id, a1.product_id
FROM a a1
WHERE EXISTS (SELECT to_char(a2.sale_date,'yyyymmdd'), a2.employee_id, a2.client_id
FROM a a2
WHERE to_char(a2.sale_date,'yyyymmdd')=to_char(a1.sale_date,'yyyymmdd')
AND a2.employee_id= a1.employee_id
AND a2.client_id=a1.client_id
GROUP BY to_char(a2.sale_date,'yyyymmdd'), a2.employee_id, a2.client_id
HAVING COUNT(DISTINCT a2.product_id)>=2 );
SQL> edi
已写入 file afiedt.buf
1 select to_char(sale_date,'yyyymmdd') sale_date,employee_id,client_id,product_id from
2 tb a where exists(select 1 from tb where employee_id=a.employee_id and client_id=a.client_id
3 group by employee_id,client_id
4* having count(1)>=2)
SQL> /
SALE_DAT EMPLO CLIEN PRODU
-------- ----- ----- -----
20100302 001 c1 p1
20100302 001 c1 p2
20100302 002 c2 p1
20100302 002 c2 p2
create table a(
sale_date date,
employee_id char(4),
client_id char(4),
product_id char(4)
);
insert into a(sale_date,employee_id,client_id, product_id) values(to_date('20100302','yyyymmdd'), '001', 'C1', 'P1');
insert into a(sale_date,employee_id,client_id, product_id) values(to_date('20100302','yyyymmdd'), '001', 'C1', 'P2');
insert into a(sale_date,employee_id,client_id, product_id) values(to_date('20100302','yyyymmdd'), '002', 'C1', 'P1');
insert into a(sale_date,employee_id,client_id, product_id) values(to_date('20100302','yyyymmdd'), '002', 'C2', 'P1');
insert into a(sale_date,employee_id,client_id, product_id) values(to_date('20100302','yyyymmdd'), '002', 'C2', 'P2');
insert into a(sale_date,employee_id,client_id, product_id) values(to_date('20100302','yyyymmdd'), '002', 'C1', 'P1');
SELECT to_char(a1.sale_date,'yyyymmdd'), a1.employee_id, a1.client_id, a1.product_id, count(a1.product_id) product_count
FROM a a1
GROUP BY to_char(a1.sale_date,'yyyymmdd'), a1.employee_id, a1.client_id, a1.product_id
HAVING COUNT(a1.product_id)>=2;
with tmp as (
select to_date('20100302','yyyymmdd') sale_date, '001' employee_id, 'C1' client_id, 'P1' product_id from dual
union all
select to_date('20100302','yyyymmdd') sale_date, '001' employee_id, 'C1' client_id, 'P2' product_id from dual
union all
select to_date('20100302','yyyymmdd') sale_date, '002' employee_id, 'C1' client_id, 'P1' product_id from dual
union all
select to_date('20100302','yyyymmdd') sale_date, '002' employee_id, 'C2' client_id, 'P1' product_id from dual
union all
select to_date('20100302','yyyymmdd') sale_date, '002' employee_id, 'C2' client_id, 'P2' product_id from dual
union all
select to_date('20100302','yyyymmdd') sale_date, '002' employee_id, 'C1' client_id, 'P1' product_id from dual
)
select mt.sale_date,mt.employee_id,mt.client_id,mt.product_id
from tmp mt,(
select sale_date,employee_id,client_id,count(distinct product_id)
from tmp
group by sale_date,employee_id,client_id
having count(distinct product_id) > 1
) st
where mt.sale_date = st.sale_date
and mt.employee_id = st.employee_id
and mt.client_id = st.client_id;