## 求一条sql语句。求求解解。

kstsca 发布于 2012/07/05 09:01

shop(商品) 表

id       shop_name

1        钻戒

shop_arr(商品属性)

id    shop_id（商品id)    shop_arr（属性）    shop_value(属性值）

1      1                                1                           ex

2      1                                 2                          ex

......

10      1                                 10                        ex

shop_arr 属性=1是切工   2是净度   3是材质  。。。。等等

shop_id    shop_name    shop_cut(切工)    shop_jd(净度)  shop_cz(材质) .........

0

0

0
select a.id,s.name decode(a.shop_arr,1, a.shop_arr ,''), decode(a.shop_arr,2, a.shop_arr ,'') .....from  shop_arr a left join  shop s on  a.id = s.id
0

0

#### 引用来自“Lu.Xu”的答案

```select t1.id as 商品ID,t1.shop_name as 商品名称,
max(case when shop_arr = 1 then shop_value end) as 切工,
max(case when shop_arr = 2 then shop_value end) as 净度,
max(case when shop_arr = 3 then shop_value end) as 材质
from shop t1, shop_arr t2
where t1.id = t2.shop_id
group by t1.id,t1.shop_name;```

0

#### 引用来自“Lu.Xu”的答案

```select t1.id as 商品ID,t1.shop_name as 商品名称,
max(case when shop_arr = 1 then shop_value end) as 切工,
max(case when shop_arr = 2 then shop_value end) as 净度,
max(case when shop_arr = 3 then shop_value end) as 材质
from shop t1, shop_arr t2
where t1.id = t2.shop_id
group by t1.id,t1.shop_name;```

Databases are born to join
0

#### 引用来自“Lu.Xu”的答案

```select t1.id as 商品ID,t1.shop_name as 商品名称,
max(case when shop_arr = 1 then shop_value end) as 切工,
max(case when shop_arr = 2 then shop_value end) as 净度,
max(case when shop_arr = 3 then shop_value end) as 材质
from shop t1, shop_arr t2
where t1.id = t2.shop_id
group by t1.id,t1.shop_name;```

0