Mysql 列转行行转列

最新推荐文章于 2024-09-23 07:00:00 发布

原创最新推荐文章于 2024-09-23 07:00:00 发布 · 390 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql行转列 #mysql #mysql行列转换

mysql 专栏收录该内容

10 篇文章

订阅专栏

mysql把查询数据的行转为列。

example:

#创建表

DROP TABLE IF EXISTS `TabName`;

CREATE TABLE `TabName` (

  `Id` int(11) NOT NULL AUTO_INCREMENT,

  `Name` varchar(20) DEFAULT NULL,

  `Date` date DEFAULT NULL,

  `Scount` int(11) DEFAULT NULL,

  PRIMARY KEY (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

#插入数据

INSERT INTO `TabName` VALUES ('1', '小说', '2013-09-01', '10000');

INSERT INTO `TabName` VALUES ('2', '微信', '2013-09-01', '20000');

INSERT INTO `TabName` VALUES ('3', '小说', '2013-09-02', '30000');

INSERT INTO `TabName` VALUES ('4', '微信', '2013-09-02', '35000');

INSERT INTO `TabName` VALUES ('5', '小说', '2013-09-03', '31000');

INSERT INTO `TabName` VALUES ('6', '微信', '2013-09-03', '36000');

INSERT INTO `TabName` VALUES ('7', '小说', '2013-09-04', '35000');

INSERT INTO `TabName` VALUES ('8', '微信', '2013-09-04', '38000');

查看数据
-- ------------------------
SELECT  * from TabName ;

以date为主键合并数据

-- ------------------------
-- 列转行统计数据
-- ------------------------
SELECT Date ,
MAX(CASE NAME WHEN '小说' THEN Scount ELSE 0 END ) 小说,
MAX(CASE NAME WHEN '微信' THEN Scount ELSE 0 END ) 微信 
FROM TabName  
GROUP BY Date  <br><br>

- 行转列统计数据
-- ------------------------

select

    Date, group_concat(NAME,'总量:',Scount) as b_str from   TabName 

 group by Date

select Date,NAME, group_concat(NAME,'总量:',Scount) as b_str from   TabName 
 group by Date ,NAME

统计用户12个月出勤天数行转列使用子查询实现，对重复数据进行sum求和

#查询结果

#需要的结果

注意mysql列名避免使用数字

select tgr.user_id,IFNULL(f.one,0) '1',IFNULL(f.two,0) '2',IFNULL(f.three,0) '3',IFNULL(f.four,0) '4',IFNULL(f.five,0) '5',IFNULL(f.six,0) '6',IFNULL(f.seven,0) '7',IFNULL(f.eight,0) '8',IFNULL(f.nine,0) '9',
IFNULL(f.ten,0) '10',IFNULL(f.eleven,0) '11',IFNULL(f.twelve,0) '12'
from tb_group_relationship tgr
left join
(
SELECT w.user_id,
sum((SELECT if(1=w.months,w.count,0) ) ) as one, sum((SELECT if(2=w.months,w.count,0) ) ) as two,
sum((SELECT if(3=w.months,w.count,0) )) as three, sum((SELECT if(4=w.months,w.count,0) ))  as four,
sum((SELECT if(5=w.months,w.count,0) )) as five, sum((SELECT if(6=w.months,w.count,0) ))  as six,
sum((SELECT if(7=w.months,w.count,0) )) as seven, sum((SELECT if(8=w.months,w.count,0) ))  as eight,
sum((SELECT if(9=w.months,w.count,0) )) as nine, sum((SELECT if(10=w.months,w.count,0) ))  as ten,
sum((SELECT if(11=w.months,w.count,0) )) as eleven, sum((SELECT if(12=w.months,w.count,0) ))  as twelve
from
(
SELECT
user_id, (COUNT(CASE WHEN way=0 AND sign_out_time IS NOT NULL THEN 1 ELSE NULL END) +
COUNT(CASE  WHEN way=1 AND sign_in_time IS NOT NULL THEN 1 ELSE NULL END)) count ,month(sign_date) months
FROM tb_sign_record  where status=1
and year(sign_date)= #{whereMap.year}
and org_id=#{whereMap.engTeamId}
group by user_id, months
) w
group by user_id
) f on  tgr.user_id=f.user_id
where status=1
and eng_team_id=#{whereMap.engTeamId}
<if test="whereMap.orderColumns != null">
    order by
    <foreach collection="whereMap.orderColumns" item="orderColumn" index="index"
             open="" close="" separator=",">
        ${orderColumn} ${orderSort}
    </foreach>
</if>
<if test="startRecord != null and pageSize != null">
    limit #{startRecord,jdbcType=INTEGER},#{pageSize,jdbcType=INTEGER}
</if>