Skip to content

Instantly share code, notes, and snippets.

@SONIC3D
Last active December 8, 2019 19:26
Show Gist options
  • Save SONIC3D/7e62ec072ace6e0aab69b9b2e364800e to your computer and use it in GitHub Desktop.
Save SONIC3D/7e62ec072ace6e0aab69b9b2e364800e to your computer and use it in GitHub Desktop.
产生固定范围升序序号的MySQL查询结果列,不查询任何实际的表
-- 固定分钟采样数量(防止停服时遗漏采样行)的在线人数分钟采样详单
SET @TOTAL_MINUTES_OF_A_DAY := 24*60;
SET @CURR_MINUTE := 0;
SELECT
(@CURR_MINUTE := @CURR_MINUTE+1)-1 as sampleId,
@targetDateBeginTs+60*(@CURR_MINUTE-1) as sampleTimeTs,
FROM_UNIXTIME(@targetDateBeginTs+60*(@CURR_MINUTE-1)) as sampleTime
FROM
-- 以下临时表可生成10000种排列组合
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t1,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4
WHERE
@CURR_MINUTE < @TOTAL_MINUTES_OF_A_DAY
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment