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'
);
|
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicGcq5yYiZGOiF2Y3kTNkFWZwUWY5EGO0ADO5UTMiJGZiRDMy0SNzUDNyITMx8CX5AzMxAjMvw1N4UDOyEzLcd2bsJ2Lc12bj5ycn9Gbi52YuAzcldWYtl2Lc9CX6MHc0RHaiojIsJye.jpg)
以date为主键合并数据
|
|
select
Date
, group_concat(
NAME
,
'总量:'
,Scount)
as
b_str
from
TabName
group
by
Date
|
统计用户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>