sql记录

将动态列值作为表头

SELECT CONCAT(
  'SELECT `v_sys_user`.id', GROUP_CONCAT('
     ,    `t_', REPLACE(name, '`', '``'), '`.roleName
         AS `', REPLACE(name, '`', '``'), '`'
     SEPARATOR ''),
 ' FROM `v_sys_user` ', GROUP_CONCAT('
     LEFT JOIN `v_sys_user`   AS `t_', REPLACE(name, '`', '``'), '`
            ON `v_sys_user`.id = `t_', REPLACE(name, '`', '``'), '`.id
           AND `t_', REPLACE(name, '`', '``'), '`.name = ', QUOTE(name)
     SEPARATOR ''),
 ' GROUP BY `v_sys_user`.id'
) INTO @qry FROM (SELECT DISTINCT name FROM `v_sys_user` WHERE jobState !=1 LIMIT 5) t;

PREPARE stmt FROM @qry;
EXECUTE stmt;

并列排序排名不推后

SELECT
	NAME,
	ROLE_ID,
	CASE
WHEN @prevRank = ROLE_ID THEN
	@curRank
WHEN @prevRank := ROLE_ID THEN
	@curRank := @curRank + 1
END AS rank
FROM
	t_sys_user,
(SELECT @curRank :=0, @prevRank := NULL) r
WHERE
	JOB_STATE != 1
ORDER BY
	ROLE_ID

并列排序排名推后

SELECT NAME,ROLE_ID,rank FROM(
SELECT name, ROLE_ID,
@curRank :=IF(@preRank=ROLE_ID,@curRank,@incRank) AS rank,
@incRank :=@incRank+1,
@preRank :=ROLE_ID
FROM t_sys_user,
(SELECT @curRank :=0,@preRank :=NULL,@incRank :=1) r
WHERE t_sys_user.JOB_STATE!=1
ORDER BY role_id)s