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;