商品表p,商品种类c,商品联系表ptc,
一个商品可以有很多种类
表p, pId,pName
1 商品1
2 商品2表 c , cId,cName
1 礼品
2 数码
3 用品表ptc, ptcId,pId,cId
1 1 1
2 1 2
3 1 3用表的连接查到的是:pName,cNmae
商品1, 礼品
商品1, 数码
商品1, 用品想查的结果这样显示:
pName,cName1,cName2,cName3
商品1,礼品, 数码, 用品怎么写sql呢????
一个商品可以有很多种类
表p, pId,pName
1 商品1
2 商品2表 c , cId,cName
1 礼品
2 数码
3 用品表ptc, ptcId,pId,cId
1 1 1
2 1 2
3 1 3用表的连接查到的是:pName,cNmae
商品1, 礼品
商品1, 数码
商品1, 用品想查的结果这样显示:
pName,cName1,cName2,cName3
商品1,礼品, 数码, 用品怎么写sql呢????
看来行列转换是个大众问题啊。呵呵
SELECT PNAME,
MAX(DECODE(CNAME1, '礼品', CNAME1)),
MAX(DECODE(CNAME1, '数码', CNAME1)),
MAX(DECODE(CNAME1, '用品', CNAME1))
FROM (你获得如下数据的SQL :商品1, 礼品 商品1, 数码 商品1, 用品)
GROUP BY PNAME;
From p,
c,
Ptc,
(Select c.Cname
From p, c, Ptc
Where Ptc.Pid = p.Pid
And Ptc.Cid = c.Cid
And Ptc.Ptcid = '1') Cc1,
(Select c.Cname
From p, c, Ptc
Where Ptc.Pid = p.Pid
And Ptc.Cid = c.Cid
And Ptc.Ptcid = '2') Cc2,
(Select c.Cname
From p, c, Ptc
Where Ptc.Pid = p.Pid
And Ptc.Cid = c.Cid
And Ptc.Ptcid = '3') Cc3
Where Ptc.Pid = p.Pid
And Ptc.Cid = c.Cid
Group By p.Pname, Cc1.Cname, Cc2.Cname, Cc3.Cname
(前两天正好研究这些题目)
以下是具体操作:
数据库:oracle 10G
建表及插入数据:
create table p (
pid integer,
pname varchar2(10)
);
insert into p values(1,'商品1');
insert into p values(2,'商品2');
commit;
create table c(
cid integer,
cname varchar2(10)
);
insert into c values(1,'礼品');
insert into c values(2,'数码');
insert into c values(3,'用品');
commit;
create table ptc(
ptcid integer,
pid integer,
cid integer
);
insert into ptc values(1,1,1);
insert into ptc values(2,1,2);
insert into ptc values(3,1,3);
insert into ptc values(4,2,1); ---因为给的例子看不出效果所以个人增加了两条数据
insert into ptc values(5,2,2);
commit;得出结果的sql语句:
select pp_name,substr(max(sys_connect_by_path(cc_name,',')),2)
from(
select ptc.ptcid,ptc.pid,p.pname pp_name,lead(ptc.ptcid) over (partition by ptc.pid order by ptc.pid) pri_1,ptc.cid,c.cname cc_name
from ptc,p,c
where ptc.pid=p.pid
and ptc.cid=c.cid
order by ptcid,cid
)
start with pri_1 is null
connect by pri_1 = prior ptcid
group by pp_name查询出的结果:
1 商品1 用品,数码,礼品
2 商品2 数码,礼品我认为这样语句的好处是,解决了一个表c和表p中内容不固定的问题!
大家一起探讨一下!