• 主页
  • Oracle 基础和管理
  • Oracle 高级技术
  • Oracle 认证与考试
  • 职位交流

问一个分组的问题

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
...全文
166 点赞 收藏 16
写回复
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) 这种写法才对。
回复 点赞
Leshami 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楼好!效率要高些!
回复 点赞
Leoyang_FarAwayFrom 2010年04月23日
分区查?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
前提是一个人卖一个客户一个产品两件的话,在库里存储要有数量
回复 点赞
发动态
发帖子
Oracle
创建于2007-09-28

6422

社区成员

5.4w+

社区内容

Oracle开发相关技术讨论
社区公告
暂无公告