sql行专列问题

selg1984 2011-10-17 06:09:01

select distinct siteid,site_name,id,goods_name,goods_same_siteid,goods_pku,catname,cname,goods_price,goods_url,goods_ppid,goods_site_catid,
max(case goods_same_siteid when 1 then goods_same_price else 0 end) gome,
max(case goods_same_siteid when 2 then goods_same_price else 0 end) suning,
max(case goods_same_siteid when 3 then goods_same_price else 0 end) jingdong,
max(case goods_same_siteid when 4 then goods_same_price else 0 end) coo8,
max(case goods_same_siteid when 5 then goods_same_price else 0 end) newegg,SUM(id)
from goods_mapping_app group by siteid,goods_ppid,site_name,goods_site_catid,id,goods_name,goods_same_siteid,goods_pku,catname,cname,goods_price,
goods_url,system_catid having siteid=1 and ( max(case goods_same_siteid when 2 then goods_same_price else 0 end)>0
or max(case goods_same_siteid when 3 then goods_same_price else 0 end)>0) order by id asc

结果
id goods_name goods_same_siteid catname cname goods_price goods_url gome suning jingdong coo8
1 海尔（Haier）具（12T） 2 海尔烟机/灶具 1173 0 0 999 0
1 海尔（Haier）具（12T） 3 海尔燃气灶 1173 0 1221 0 0
如何把他弄成一行
这样的
1 海尔（Haier）具（12T 海尔 1173 0 1221 999 0

...全文

64 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

--小F-- 2011-10-17

打赏
举报

select

  siteid,site_name,id,goods_name,goods_same_siteid,

  goods_pku,catname,cname,goods_price,goods_url, 

  goods_ppid,goods_site_catid,---这里不需要显示的列自己去掉

  sum(case goods_same_siteid when 1 then goods_same_price else 0 end) gome,

  sum(case goods_same_siteid when 2 then goods_same_price else 0 end) suning,

  sum(case goods_same_siteid when 3 then goods_same_price else 0 end) jingdong,

  sum(case goods_same_siteid when 4 then goods_same_price else 0 end) coo8,

  sum(case goods_same_siteid when 5 then goods_same_price else 0 end) newegg,SUM(id)

from

 goods_mapping_app 

group by 

  siteid,goods_ppid,site_name,goods_site_catid,id,

  goods_name,goods_same_siteid,goods_pku,catname,cname,goods_price,

  goods_url,system_catid 

having

  siteid=1 

and

 ( max(case goods_same_siteid when 2 then goods_same_price else 0 end)>0  

or

  max(case goods_same_siteid when 3 then goods_same_price else 0 end)>0) order by id asc

geniuswjt 2011-10-17

打赏
举报

其实我不想糊弄你的，不过没办法，你写成那样子。
放结果丢进临时表然后按上面的做吧，愁啊

geniuswjt 2011-10-17

打赏
举报



--> 测试数据: [tb]

if object_id('[tb]') is not null drop table [tb]

create table [tb] (id int,goods_name varchar(18),goods_same_siteid int,catname varchar(4),cname varchar(9),goods_price int,goods_url int,gome int,suning int,jingdong int)

insert into [tb]

select 1,'海尔(Haier)具(12T)',2,'海尔','烟机/灶具',1173,0,0,999,0 union all

select 1,'海尔(Haier)具(12T)',3,'海尔','燃气灶',1173,0,1221,0,0



--开始查询

select a.id,a.goods_name,a.goods_same_siteid,a.catname,a.cname,a.goods_price,a.goods_url,

gome=a.gome+b.gome,suning=a.suning+b.suning,a.jingdong

from tb a join tb b on (a.goods_name=b.goods_name and a.goods_same_siteid=b.goods_same_siteid-1)



--结束查询

drop table [tb]



/*

id          goods_name         goods_same_siteid catname cname     goods_price goods_url   gome        suning      jingdong

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

1           海尔(Haier)具(12T)    2                 海尔      烟机/灶具     1173        0           1221        999         0



(1 行受影响)