我们在SQL Server数据库中存储了数千个作业编号,我希望能够推导出它们的1000或100范围,以便在与我们的作业列表集成的第三方应用程序中进行分组。
如何在查询结果集中的一列中提取每个作业编号所属的1000组?
示例:我希望我的输出是:
JOB_NUMBER JOB_GROUP
678 0-999
679 0-999
1517 1000-1999
2011 2000-2999
2150 2000-2999
...etc.
如果job_number是一个整数,您可以使用这个:
select job_number,
convert(varchar(10), job_number / 1000 * 1000)
+ '-'
+ convert(varchar(10), job_number / 1000 * 1000 + 999) Range
from whatever
您可以使用CASE表达式:
SELECT
JOB_NUMBER,
JOB_GROUP = CASE
WHEN JOB_NUMBER < 1000 THEN '0-999'
WHEN JOB_NUMBER < 2000 THEN '1000-1999'
WHEN JOB_NUMBER < 3000 THEN '2000-2999'
END
试试这个:
WITH CTE AS(
SELECT JOB_NUMBER/1000 AS JOB_NUMBER,MIN(JOB_NUMBER) AS MIN_NUMBER,
MAX(JOB_NUMBER) AS MAX_NUMBER
FROM <TABLE>
GROUP BY NUMBER/1000)
SELECT M.JOB_NUMBER,
CAST(MIN_NUMBER AS VARCHAR(20))+'-'+CAST(MAX_NUMBER AS VARCHAR(20))
FROM <TABLE> M
JOIN CTE
ON MIN_NUMBER<= M.JOB_NUMBER
AND MAX_NUMBER >=M.JOB_NUMBER
ORDER BY M.JOB_NUMBER