原表有两个字段USERID(用户id)和CHECKTIME(打卡时间),现在想查询得出类似下面的表
我写的一段sql语句,发现只能拿到一个打卡时间,有没有大神帮忙看看怎么改SET @sql = '';
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(DATE(CHECKTIME) = ''',
DATE(CHECKTIME),
''', CHECKTIME, 0)) AS ''',
DATE(CHECKTIME), ''''
)
) INTO @sql
FROM checkinout
WHERE DATE(CHECKTIME) BETWEEN '2016-06-01' AND '2016-06-05';
SET @sql = CONCAT('Select USERID, ', @sql,
' From checkinout
Group by USERID');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
我写的一段sql语句,发现只能拿到一个打卡时间,有没有大神帮忙看看怎么改SET @sql = '';
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(DATE(CHECKTIME) = ''',
DATE(CHECKTIME),
''', CHECKTIME, 0)) AS ''',
DATE(CHECKTIME), ''''
)
) INTO @sql
FROM checkinout
WHERE DATE(CHECKTIME) BETWEEN '2016-06-01' AND '2016-06-05';
SET @sql = CONCAT('Select USERID, ', @sql,
' From checkinout
Group by USERID');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
MAX(IF(DATE(CHECKTIME) = '2016-06-01', CHECKTIME, 0)) AS '2016-06-01',MAX(IF(DATE(CHECKTIME) = '2016-06-02', CHECKTIME, 0)) AS '2016-06-02',MAX(IF(DATE(CHECKTIME) = '2016-06-03', CHECKTIME, 0)) AS '2016-06-03',MAX(IF(DATE(CHECKTIME) = '2016-06-05', CHECKTIME, 0)) AS '2016-06-05',MAX(IF(DATE(CHECKTIME) = '2016-06-04', CHECKTIME, 0)) AS '2016-06-04'
-- 基于汇总数据再做交叉表
select userid, concat(max(if(日期='xxx',上班时间,null)), .....)
from
-- 先得汇总数据(可以考虑弄到临时表)
select userid, 日期, 上班时间,上班时间,打卡次数 from xxx
)xx
group by userid