SQL（待学习）

CREATE TABLE elecct ( pid int, prop_name VARCHAR(15),vid int, NAME VARCHAR(20));

insert into elecct(pid,prop_name,vid,NAME) values(11,'手机',3232483,'IPone')

insert into elecct(pid,prop_name,vid,NAME) values(11,'手机',3232484,'联想')

insert into elecct(pid,prop_name,vid,NAME) values(12,'电脑',3232486,'DELL

insert into elecct(pid,prop_name,vid,NAME) values(12,'电脑',3232487,'宏基')

insert into elecct(pid,prop_name,vid,NAME) values(13,'游戏机',3232490,'PS2

insert into elecct(pid,prop_name,vid,NAME) values(13,'游戏机',3232489,'XBOX')

 

select PID,PROP_NAME,stuff((select ',('+cast(VID as char(7))+','+NAME+')'

                                                      from elecct where PID=t.PID and PROP_NAME=t.PROP_NAME for xml path('')),1,1,'')

           from elecct t group by PID,PROP_NAME

-------------------------------------

11 手机 (3232483,IPone),(3232484,联想)

12 电脑 (232486,DELL),(3232487,宏基')

13 游戏机 (3232490,PS2),(3232489,XBOX)

 

；；；；；；；；；；；；；；；；；；；；；；；；；

 

MYSQL实现

 

CREATE TABLE elecct ( pid int, prop_name VARCHAR(15),vid int, NAME VARCHAR(20));

insert into elecct(pid,prop_name,vid,NAME) values(11,'手机',3232483,'IPone')

insert into elecct(pid,prop_name,vid,NAME) values(11,'手机',3232484,'联想')

insert into elecct(pid,prop_name,vid,NAME) values(12,'电脑',3232486,'DELL

insert into elecct(pid,prop_name,vid,NAME) values(12,'电脑',3232487,'宏基')

insert into elecct(pid,prop_name,vid,NAME) values(13,'游戏机',3232490,'PS2

insert into elecct(pid,prop_name,vid,NAME) values(13,'游戏机',3232489,'XBOX')

 

select pid,prop_name,group_concat(concat('(',vid,NAME,')')) from elecct group by pid,prop_name --------------------------------------

11 手机 (3232483,IPone),(3232484,联想)

12 电脑 (232486,DELL),(3232487,宏基')

13 游戏机 (3232490,PS2),(3232489,XBOX)