行转列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
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 蹭分了
来一个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
行转列的问题一直萦绕在oracle和sql server版本
--你这个数量的字段 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 nameselect 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
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
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
蹭分了
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
--你这个数量的字段 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 nameselect 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