求解一面试题，遇到两次了！！

[hjx_gb2000]

有表结构如下：Sale

id NAME Quarter(季度) count

1 Ran 1 10

2 Ran 2 11

3 Ran 3 12

4 Juan 1 16

5 Juan 2 18

6 Juan 3 16

7 Ping 1 11

8 Ping 2 11

9 Ping 3 12



根据上面的表结格生成如下的输出格式：

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

Name 一季度 二季度 三季度

Ran 10 11 12

Juan 16 18 16

............

写出sql:

[luoyoumou]

case when 或 decode() 函数

[iqlife]

行转列

SELECT NAME,
sum(DECODE(Quarter,1,count,0)) AS '一季度',
sum(DECODE(Quarter,2,count,0)) AS '二季度',
sum(DECODE(Quarter,3,count,0)) AS '三季度'
from Sale
group by name

[cyousor]

select name,
sum(decode(Quarter,1,ct,0)) "一季度",
sum(decode(Quarter,2,ct,0)) "二季度",
sum(decode(Quarter,3,ct,0)) "三季度"
from sale
group by name
order by name


蹭分了

[zjhiphop2006]

来一个case when的

WITH SALE AS
 (SELECT 1 ID, 'Ran' NAME, 1 QUARTER, 10 COUNT
    FROM DUAL
  UNION ALL
  SELECT 2, 'Ran', 2, 11
    FROM DUAL
  UNION ALL
  SELECT 3, 'Ran', 3, 12
    FROM DUAL
  UNION ALL
  SELECT 4, 'Juan', 1, 16
    FROM DUAL
  UNION ALL
  SELECT 5, 'Juan', 2, 18
    FROM DUAL
  UNION ALL
  SELECT 6, 'Juan', 3, 16
    FROM DUAL
  UNION ALL
  SELECT 7, 'Ping', 1, 11
    FROM DUAL
  UNION ALL
  SELECT 8, 'Ping', 2, 11
    FROM DUAL
  UNION ALL
  SELECT 9, 'Ping', 3, 12 FROM DUAL)

SELECT NAME,
       sum(CASE QUARTER
         WHEN 1 THEN
          COUNT
       END) 一季度,
       sum(CASE QUARTER
         WHEN 2 THEN
          COUNT
       END) 二季度,
       sum(CASE QUARTER
         WHEN 3 THEN
          COUNT
       END) 三季度
  FROM SALE
 GROUP BY NAME

[hjx_gb2000]

[Quote=引用 4 楼 zjhiphop2006 的回复:]
来一个case when的

SQL code
WITH SALE AS
(SELECT 1 ID, 'Ran' NAME, 1 QUARTER, 10 COUNT
FROM DUAL
UNION ALL
SELECT 2, 'Ran', 2, 11
FROM DUAL
UNION ALL
SELECT 3, 'Ran', 3, 12
……
[/Quote]

Good!学习了哦！

[minitoy]

呵呵，楼上的都可以

[beita258]

行列转换，这两天碰上几个类似提问了....

[czfxwpy]

蹭分 ，呵呵。。。。

[csuxp2008]

行转列的问题一直萦绕在oracle和sql server版本

[心中的彩虹]

[Quote=引用楼主 hjx_gb2000 的回复:]
有表结构如下：Sale

id NAME Quarter(季度) count

1 Ran 1 10

2 Ran 2 11

3 Ran 3 12

4 Juan 1 16

5 Juan 2 18

6 Juan 3 16

7 Ping 1 11

8 Ping 2 11

9 Ping 3 12



根据上面的表结格生成……
一季度 二季度 三季度
[/Quote]

--你这个数量的字段 count 最好不要使用关键字,case when  与decode
select name,sum(decode(Quarter),1,count)) 一季度,
sum(decode(Quarter),2,count)) 二季度,
sum(decode(Quarter),3,count)) 三季度,
sum(decode(Quarter),4,count)) 四季度
from Sale
group by name

select name,sum(case when  Quarter=1 then count end) 一季度,
sum(case when  Quarter=2 then count end) 二季度,
sum(case when  Quarter=3 then count end) 三季度,
sum(case when  Quarter=4 then count end) 四季度
from sale
group by name

[hjx_gb2000]

[Quote=引用 10 楼 wkc168 的回复:]
--你这个数量的字段 count 最好不要使用关键字,case when 与decode
[/Quote]

嗯，面试题是这样写的。一般自己不会用关键字作为字段名的

[huangyunzeng2008]

行列转换问题！网上很多！建议多参考！