mysql数据库中列转行

今天突然想到了数据库中的行专列与列转行，还不熟悉，在上网看了一下然后就自己写了个例子。

数据库表示这样滴！

//全部查询出来
SELECT  
(case type  when 'MySql数据库' then id else NULL END) as  'MySql数据库',
(case type  when 'SqlServer数据库' then id  else NULL END) as  'SqlServer数据库',
(case type  when 'CSharp' then id else NULL END) as  'CSharp',
(case type  when 'Html' then id else NULL END) as  'Html'
from xiaoyao_blogs_essay;

//查询出他们id的和
SELECT  
sum(case type  when 'MySql数据库' then id else NULL END) as  'MySql数据库',
sum(case type  when 'SqlServer数据库' then id  else NULL END) as  'SqlServer数据库',
sum(case type  when 'CSharp' then id else NULL END) as  'CSharp',
sum(case type  when 'Html' then id else NULL END) as  'Html'
from xiaoyao_blogs_essay;

//查询出最大的id
SELECT  
max(case type  when 'MySql数据库' then id else NULL END) as  'MySql数据库',
max(case type  when 'SqlServer数据库' then id  else NULL END) as  'SqlServer数据库',
max(case type  when 'CSharp' then id else NULL END) as  'CSharp',
max(case type  when 'Html' then id else NULL END) as  'Html'
from xiaoyao_blogs_essay;

//查询出最小的id
SELECT  
min(case type  when 'MySql数据库' then id else NULL END) as  'MySql数据库',
min(case type  when 'SqlServer数据库' then id  else NULL END) as  'SqlServer数据库',
min(case type  when 'CSharp' then id else NULL END) as  'CSharp',
min(case type  when 'Html' then id else NULL END) as  'Html'
from xiaoyao_blogs_essay;

//查询出每列的总数
SELECT  
count(case type  when 'MySql数据库' then id else NULL END) as  'MySql数据库',
count(case type  when 'SqlServer数据库' then id  else NULL END) as  'SqlServer数据库',
count(case type  when 'CSharp' then id else NULL END) as  'CSharp',
count(case type  when 'Html' then id else NULL END) as  'Html'
from xiaoyao_blogs_essay;

结果

 

转载于:https://www.cnblogs.com/xiaoyaodijun/p/4762376.html