表中数据
QF SYBH RQ SXBSJ
'1', '00001', '2012/10/08', '07:46'
'2', '00001', '2012/10/08', '16:31'
'3', '00001', '2012/10/08', '病3.0'
'1', '00001', '2012/10/09', '07:24'
'2', '00001', '2012/10/09', '16:28'
'3', '00001', '2012/10/09', '事2.0'
'1', '00001', '2012/10/10', '07:45'
'2', '00001', '2012/10/10', '16:29'
'3', '00001', '2012/10/10', '休8.0'
'1', '00001', '2012/10/11', '07:46'
'2', '00001', '2012/10/11', '16:29'
'3', '00001', '2012/10/11', '休8.0'
'1', '00001', '2012/10/12', '07:48'
'2', '00001', '2012/10/12', '16:31'
'3', '00001', '2012/10/12', '休8.0'
'1', '00001', '2012/10/13', '07:13'
'2', '00001', '2012/10/13', '16:29'
'3', '00001', '2012/10/13', '休0.0'
.
.
.期待结果:
'2012/10/08' '2012/10/09' .........
社员编号 '00001' '00001' .........
上班时间 '07:46' '07:24' .........
下班时间 '16:31' '16:28' .........
休假 '病3.0' '事2.0' .........
QF SYBH RQ SXBSJ
'1', '00001', '2012/10/08', '07:46'
'2', '00001', '2012/10/08', '16:31'
'3', '00001', '2012/10/08', '病3.0'
'1', '00001', '2012/10/09', '07:24'
'2', '00001', '2012/10/09', '16:28'
'3', '00001', '2012/10/09', '事2.0'
'1', '00001', '2012/10/10', '07:45'
'2', '00001', '2012/10/10', '16:29'
'3', '00001', '2012/10/10', '休8.0'
'1', '00001', '2012/10/11', '07:46'
'2', '00001', '2012/10/11', '16:29'
'3', '00001', '2012/10/11', '休8.0'
'1', '00001', '2012/10/12', '07:48'
'2', '00001', '2012/10/12', '16:31'
'3', '00001', '2012/10/12', '休8.0'
'1', '00001', '2012/10/13', '07:13'
'2', '00001', '2012/10/13', '16:29'
'3', '00001', '2012/10/13', '休0.0'
.
.
.期待结果:
'2012/10/08' '2012/10/09' .........
社员编号 '00001' '00001' .........
上班时间 '07:46' '07:24' .........
下班时间 '16:31' '16:28' .........
休假 '病3.0' '事2.0' .........
UNION
SELECT '上班时间',sybh,MAX(IF(rq='2012-10-08' AND qf=1,SXBSJ,'')) AS `2012-10-08` FROM ttk GROUP BY sybh
UNION
SELECT '下班时间',sybh,MAX(IF(rq='2012-10-08' AND qf=2,SXBSJ,'')) AS `2012-10-08` FROM ttk GROUP BY sybh
UNION
SELECT '休假',sybh,MAX(IF(rq='2012-10-08' AND qf=3,SXBSJ,'')) AS `2012-10-08` FROM ttk GROUP BY sybh;
SELECT * FROM (
SELECT
SYBH,
CASE WHEN ifnull(QF,'')='1' THEN '上班时间'
WHEN ifnull(QF,'')='2' THEN '下班时间'
WHEN ifnull(QF,'')='3' THEN '其它时间'
END AS QF,
MAX(CASE RQ WHEN '2012/10/10' THEN SXBSJ ELSE NULL END) AS '2012/10/10',
MAX(CASE RQ WHEN '2012/10/11' THEN SXBSJ ELSE NULL END) AS '2012/10/11',
MAX(CASE RQ WHEN '2012/10/12' THEN SXBSJ ELSE NULL END) AS '2012/10/12',
MAX(CASE RQ WHEN '2012/10/13' THEN SXBSJ ELSE NULL END) AS '2012/10/13',
MAX(CASE RQ WHEN '2012/10/14' THEN SXBSJ ELSE NULL END) AS '2012/10/14'
FROM test_KQ
GROUP BY SYBH,QF
) A ORDER BY SYBH,QF
自己写的,中间部分不能动态
SET @asql='';
SELECT DISTINCT RQ,
@asql:=CONCAT(@asql,CONCAT('MAX(CASE DATE(RQ) WHEN \'',DATE(rq),'\' THEN SXBSJ ELSE NULL END) AS `',DATE(rq)),'`,')
FROM ttk;
SET @asql=CONCAT('SELECT SYBH,CASE WHEN IFNULL(QF,99)=\'1\' THEN \'上班时间\'
WHEN IFNULL(QF,99)=\'2\' THEN \'下班时间\' WHEN IFNULL(QF,99)=\'3\' THEN \'其它时间\'
END AS QF, ',LEFT(@asql,LENGTH(@asql)-1),' FROM ttk GROUP BY SYBH,QF');
SELECT @asql;
PREPARE stml FROM @asql;
EXECUTE stml;
IN firstDay varchar(10)#当月初始日期
)
BEGIN
DECLARE STR_SQL1 VARCHAR(4000) DEFAULT '';#拼接字符串
DECLARE RQ VARCHAR(10) DEFAULT '2012/10/01';#循环初始日期
DECLARE I INT;#
SET I=1;
SET RQ=firstDay;
while I<=day_Count DO
-- insert into t1(filed) values(i);
IF I=1 THEN
SET STR_SQL1=CONCAT(STR_SQL1,'MAX(CASE RQ WHEN \'',RQ,'\' THEN SXBSJ ELSE NULL END) AS \'',RQ,'\'');
ELSE
SET STR_SQL1=CONCAT(STR_SQL1,',MAX(CASE RQ WHEN \'',RQ,'\' THEN SXBSJ ELSE NULL END) AS \'',RQ,'\' ');
END IF;
SET RQ=date_format(DATE_ADD(RQ, INTERVAL 1 DAY),'%Y/%m/%d');
SET I=I+1;
END WHILE;
SET @STR_SQL2=CONCAT(
'SELECT * FROM (
SELECT
SYBH,
CASE WHEN ifnull(QF,\'\')=\'1\' THEN \'上班时间\'
WHEN ifnull(QF,\'\')=\'2\' THEN \'下班时间\'
WHEN ifnull(QF,\'\')=\'3\' THEN \'其它时间\'
END AS QF, ',
STR_SQL1,
'FROM test_KQ
GROUP BY SYBH,QF
) A ORDER BY SYBH,QF'
);
PREPARE S from @STR_SQL2;
EXECUTE S;
END
我吧中间部分拼接起来了, 希望能找到更好的办法。
谢谢 wwwwb
@asql:=CONCAT(@asql,CONCAT('MAX(CASE DATE(RQ) WHEN \'',DATE(rq),'\' THEN SXBSJ ELSE NULL END) AS `',DATE(rq)),'`,')
FROM 考勤表;就行了
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$CREATE DEFINER=`root`@`%` PROCEDURE `getKQExcelInfo`( IN day_Count int,#当月天数
IN firstDay varchar(10)#当月初始日期
)
BEGIN
DECLARE STR_SQL1 VARCHAR(4000) DEFAULT '';#拼接字符串
DECLARE RQ VARCHAR(10) DEFAULT '2012/10/01';#循环初始日期
DECLARE I INT;#
SET I=1;
SET RQ=firstDay;
while I<=day_Count DO
-- insert into t1(filed) values(i);
SET STR_SQL1=CONCAT(STR_SQL1,'MAX(CASE RQ WHEN \'',RQ,'\' THEN SXBSJ ELSE NULL END) AS \'',RQ,'\',');
SET RQ=date_format(DATE_ADD(RQ, INTERVAL 1 DAY),'%Y/%m/%d');
SET I=I+1;
END WHILE;
SET @STR_SQL2=CONCAT(
'SELECT * FROM (
SELECT
SYBH,
CASE WHEN ifnull(QF,\'\')=\'1\' THEN \'上班时间\'
WHEN ifnull(QF,\'\')=\'2\' THEN \'下班时间\'
WHEN ifnull(QF,\'\')=\'3\' THEN \'其它时间\'
END AS QF, ',
LEFT(STR_SQL1,LENGTH(STR_SQL1)-1),
'FROM test_KQ
GROUP BY SYBH,QF
) A ORDER BY SYBH,QF'
);
PREPARE S from @STR_SQL2;
EXECUTE S;
END就这样吧,
SELECT DISTINCT RQ,
@asql:=CONCAT(@asql,CONCAT('MAX(CASE DATE(RQ) WHEN \'',DATE(rq),'\' THEN SXBSJ ELSE NULL END) AS `',DATE(rq)),'`,')
FROM ttk;
这个试了一下,查询很慢,再次感谢WWWWb ,准备结贴