销售表A,有字段sale_date, employee_id,client_id,product_id,内容如下
20100302 001 C1 P1
20100302 001 C1 P2
20100302 002 C1 P1
20100302 002 C2 P1
20100302 002 C2 P2
20100302 003 C1 P1
现在需要把一天中，一个销售人员对一个客户卖出两种以上货物的纪录打印出来，如何实现？结果如下：
20100302 001 C1 P1
20100302 001 C1 P2
20100302 002 C2 P1
20100302 002 C2 P2
------解决方案--------------------
select * from (
select sale_date,
employee_id,
client_id,
product_id,
count(1) over(partition by sale_date, employee_id, client_id) as cn
from a) where cn>1
前提是一个人卖一个客户一个产品两件的话，在库里存储要有数量
------解决方案--------------------
- SQL code
with tmp as (select to_date('20100302','yyyymmdd') sale_date, '001' employee_id, 'C1' client_id, 'P1' product_id from dualunion allselect to_date('20100302','yyyymmdd') sale_date, '001' employee_id, 'C1' client_id, 'P2' product_id from dualunion allselect to_date('20100302','yyyymmdd') sale_date, '002' employee_id, 'C1' client_id, 'P1' product_id from dualunion allselect to_date('20100302','yyyymmdd') sale_date, '002' employee_id, 'C2' client_id, 'P1' product_id from dualunion allselect to_date('20100302','yyyymmdd') sale_date, '002' employee_id, 'C2' client_id, 'P2' product_id from dualunion allselect 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_idfrom tmp mt,(select sale_date,employee_id,client_id,count(distinct product_id)from tmpgroup by sale_date,employee_id,client_idhaving count(distinct product_id) > 1) stwhere mt.sale_date = st.sale_dateand mt.employee_id = st.employee_idand mt.client_id = st.client_id;
------解决方案--------------------
select *
from A
where (sale_date, employee_id,client_id) in
(select sale_date, employee_id,client_id
from A
group by sale_date, employee_id,client_id
having count(*)>1)
------解决方案--------------------
分区查？over是什么意思？
不懂，帮顶 我也想知道如何弄...
------解决方案--------------------
1.2.3楼都对！
但是1楼好！效率要高些！
------解决方案--------------------
- SQL code
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_countFROM a a1GROUP BY to_char(a1.sale_date,'yyyymmdd'), a1.employee_id, a1.client_id, a1.product_idHAVING COUNT(a1.product_id)>=2;
------解决方案--------------------
- SQL code
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 p120100302 001 c1 p220100302 002 c2 p120100302 002 c2 p2