问一个分组的问题

rottenapple 2010-04-23 02:23:36
销售表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
...全文
213 16 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
16 条回复
切换为时间正序
请发表友善的回复…
发表回复
tangren 2010-04-24
  • 打赏
  • 举报
回复
--练习一下
--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;
codearts 2010-04-24
  • 打赏
  • 举报
回复
看了下,楼主是说2种,不是2件。

所以having count(1) 这种写法是不对的,

having count(disintct product_id) 这种写法才对。
清风智语 2010-04-24
  • 打赏
  • 举报
回复

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);


wjz748305545 2010-04-23
  • 打赏
  • 举报
回复
接分ing
bbbbbb888888 2010-04-23
  • 打赏
  • 举报
回复
1L的正点
yangant 2010-04-23
  • 打赏
  • 举报
回复
select sale_date, employee_id,client_id,product_id from A where exists (
select * from A as B where B.sale_date=A.sale_date and
B.employee_id=A.employee_id and B.client_id=A.client_id and
B.product_id<>A.product_id
)
luoyoumou 2010-04-23
  • 打赏
  • 举报
回复
-- 请注意:两种以上货物

-- 是“两种”,而不是“两件”!
luoyoumou 2010-04-23
  • 打赏
  • 举报
回复
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 );


-- 我是来蹭分的!
luoyoumou 2010-04-23
  • 打赏
  • 举报
回复
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 );
心中的彩虹 2010-04-23
  • 打赏
  • 举报
回复

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

luoyoumou 2010-04-23
  • 打赏
  • 举报
回复
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;
碧水幽幽泉 2010-04-23
  • 打赏
  • 举报
回复
1.2.3楼都对!
但是1楼好!效率要高些!
  • 打赏
  • 举报
回复
分区查?over是什么意思?

不懂,帮顶 我也想知道如何弄...
iqlife 2010-04-23
  • 打赏
  • 举报
回复
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)
ngx20080110 2010-04-23
  • 打赏
  • 举报
回复

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;
ypfe23 2010-04-23
  • 打赏
  • 举报
回复
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
前提是一个人卖一个客户一个产品两件的话,在库里存储要有数量

17,140

社区成员

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧