行转列

select product_id,product_name,product_type,area,sum(num) as total,
(max(case product_state when '1' then num else 0 end)) as a,
(max(case product_state when '2' then num else 0 end)) as b,
(max(case product_state when '3' then num else 0 end)) as c,
(max(case product_state when '4' then num else 0 end)) as d
from
(
select product_id,product_name,product_type,area,product_state,count(product_state)

as num from log_visible_product_tab
group by product_id,product_name,product_type,area,product_state
)
group by product_id,product_name,product_type,area


select product_id, count(product_id) as total,
(max(case product_state
when '1' then num
else 0
end)) as a,
(max(case product_state
when '2' then num
else 0
end)) as b,
(max(case product_state
when '3' then num
else 0
end)) as c,
(max(case product_state
when '4' then num
else 0
end)) as d
from
(
select product_id,product_state,count(product_state) as num from log_visible_product_tab
group by product_id,product_state
)
group by product_id

//行别列

create table wenjyTest(姓名 varchar(10) , 课程 varchar(10) , 分数 int)

insert into wenjyTest values('张三' , '语文' , 74);
insert into wenjyTest values('张三' , '数学' , 83);
insert into wenjyTest values('张三' , '物理' , 93);
insert into wenjyTest values('李四' , '语文' , 74);
insert into wenjyTest values('李四' , '数学' , 84);
insert into wenjyTest values('李四' , '物理' , 94);


select 姓名 as 姓名 ,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理
from wenjyTest
group by 姓名