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'
);
|
以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>