请教大牛MySQL 动态行转列，客户变态需求。。。。。。

gaoge11 2012-11-09 08:22:37

表中数据
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' .........

...全文

1558 12 打赏收藏转发到动态举报

写回复

用AI写文章

12 条回复

切换为时间正序

请发表友善的回复…

发表回复

gaoge11 2012-11-09

打赏
举报



-- --------------------------------------------------------------------------------
-- 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 ，准备结贴

wwwwb 2012-11-09

打赏
举报

SELECT DISTINCT RQ, @asql:=CONCAT(@asql,CONCAT('MAX(CASE DATE(RQ) WHEN \'',DATE(rq),'\' THEN SXBSJ ELSE NULL END) AS `',DATE(rq)),'`,') FROM 考勤表;就行了

gaoge11 2012-11-09

打赏
举报

引用 9 楼 wwwwb 的回复:

没有，因为你要得到所有的日期，7楼的代码参考一下，如果不需要每月所有日期，已经满足需要了，否则，要生成每月所有日期，比较麻烦。

这是要生成一个考勤的报表，一次就生成一个月的数据，所以会是一个月的所有日期，可以把基础表处理一下像你那样写中间的那块，因为考勤信息每一天都会存在。。。

wwwwb 2012-11-09

打赏
举报

没有，因为你要得到所有的日期，7楼的代码参考一下，如果不需要每月所有日期，已经满足需要了，否则，要生成每月所有日期，比较麻烦。

gaoge11 2012-11-09

打赏
举报



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

wwwwb 2012-11-09

打赏
举报

示例： 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;

gaoge11 2012-11-09

打赏
举报


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

自己写的，中间部分不能动态

gaoge11 2012-11-09

打赏
举报

引用 4 楼 wwwwb 的回复:

对的，很麻烦

谢谢你，

wwwwb 2012-11-09

打赏
举报

对的，很麻烦

gaoge11 2012-11-09

打赏
举报

引用 2 楼 WWWWA 的回复:

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=1,SXBSJ,'')) AS `2012-10-08` ……

你好，日期不固定，这样的话不是有多少日期得写多少遍吗？

WWWWA 2012-11-09

打赏
举报

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=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;

gaoge11 2012-11-09