求一SQL查询语句

Question

product表name             price                 address
pro1             200                   beijing
pro2             100                   wuhan
pro3             300                   tianjingpro1             100                   wuhan
pro2             300                   tianjing
pro3             200                   beijingpro1             400                   tianjing
pro3             200                   wuhan查询结果name             beijing.price   wuhan.price    tianjing.price   ...
pro1             200             100            400              ...
pro2             null            100            300              ...
pro3             200             200            300              ...

Answer 1

有多少个城市就加多少个类似的SUM(DECODE(ADDRESS, 'beijing', PRICE)) BEIJING；
SELECT NAME,
       SUM(DECODE(ADDRESS, 'beijing', PRICE)) BEIJING,
       SUM(DECODE(ADDRESS, 'wuhan', PRICE)) WUHAN,
       SUM(DECODE(ADDRESS, 'tianjing', PRICE)) TIANJING
  FROM PRODUCT
 GROUP BY NAME;

Answer 2

这是SQL SERVER的代码,应该差不多,Select Name,Sum(beijing) as beijing,Sum(wuhan) as wuhan,Sum(tianjing)as tianjing
From
(
Select name,
Case address when 'beijing' then price end as beijing,
Case address when 'wuhan' then price end as wuhan,
Case address when 'tianjing' then price end as tianjing
from test1
)B Group by nameName                 beijing     wuhan       tianjing    
-------------------- ----------- ----------- ----------- 
pro1                 200         100         400
pro2                 NULL        100         300
pro3                 200         200         300（所影响的行数为 3 行）

Answer 3

如果数据少的话用一楼的.
数据多用存储过程拼sql.