日期:2014-05-17 浏览次数:20885 次
IF OBJECT_ID('#t') IS NOT NULL
DROP TABLE #t
;WITH Digits AS (
SELECT 0 as Number
UNION SELECT 1
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
UNION SELECT 5
UNION SELECT 6
UNION SELECT 7
UNION SELECT 8
UNION SELECT 9
)
SELECT
(d5.Number * 100000)
+ (d4.Number * 10000)
+ (d3.Number * 1000)
+ (d2.Number * 100)
+ (d1.Number * 10)
+ d0.Number as Number INTO #t
FROM
Digits AS d0
, Digits AS d1
, Digits AS d2
, Digits AS d3
, Digits AS d4
, Digits AS d5
--SELECT * FROM #t ORDER BY Number
/*
IF OBJECT_ID('huang') IS NOT NULL
DROP TABLE huang
CREATE TABLE huang (id VARCHAR(7) )
INSERT INTO huang
SELECT '0000000'
UNION ALL SELECT '0000001'
UNION ALL SELECT '0000002'
UNION ALL SELECT '0000007'
UNION ALL SELECT '0000009'
*/
;WITH cte AS (
SELECT *
FROM huang a right JOIN #t b ON CAST(id AS int)=b.number
WHERE b.number<= (SELECT MAX(id) id FROM huang)
)
SELECT RIGHT('0000000'+CONVERT(VARCHAR(7),MIN(number)),7)
FROM cte
WHERE id IS NULL