sql语句，由四位数组成的组合排列，每一位数里面都是从0.1.2.3.4.5.6.7.8.9.A.B.C.D.E.F中16个数取一位

[yy_zan004]

用sql语句写，由四位数组成的组合排列，每一位数里面都是从0.1.2.3.4.5.6.7.8.9.A.B.C.D.E.F中16个数取一位按顺序排序
如：
0000
0001
0002
0003
....
0009
000A
000B
....
000F
0010
....
FFFF

[开着拖拉机泡妞]

--> 测试数据：[test]
if object_id('[test]') is not null 
drop table [test]
create table [test](
[id] int,
[name] varchar(5),
[recorddate] date,
[workhours] numeric(3,2)
)
go
insert [test]
select 1,'sa','2012-06-10',8.00 union all
select 2,'sa','2012-06-11',8.00 union all
select 3,'root','2012-06-11',9.00 union all
select 4,'root','2012-06-14',7.00 union all
select 5,'admin','2012-06-15',6.00 union all
select 6,'sa','2012-06-10',3.00
go

declare @StartTime date
set @StartTime='2012-06-10'
;with t 
as(
select distinct b.name,DATEADD(DD,number,@StartTime) as [recorddate]
from master..spt_values a
cross join test b
where number between 0 and 5 and type='p'
)
select t.name,t.recorddate,sum(isnull(m.workhours,0.00)) as workhours 
from t 
left join test m
on t.recorddate=m.recorddate and t.name=m.name
group by t.name,t.recorddate
order by t.name,t.recorddate

/*
name	recorddate	workhours
----------------------------------------------
admin	2012-06-10	0.00
admin	2012-06-11	0.00
admin	2012-06-12	0.00
admin	2012-06-13	0.00
admin	2012-06-14	0.00
admin	2012-06-15	6.00
root	2012-06-10	0.00
root	2012-06-11	9.00
root	2012-06-12	0.00
root	2012-06-13	0.00
root	2012-06-14	7.00
root	2012-06-15	0.00
sa	2012-06-10	11.00
sa	2012-06-11	8.00
sa	2012-06-12	0.00
sa	2012-06-13	0.00
sa	2012-06-14	0.00
sa	2012-06-15	0.00
*/

create table #t(
value varchar(5)
)
go
insert #t
select '0001' union all
select '0004' union all
select '0002' union all
select '0023' union all
select '0016' union all
select '0005' union all
select '0008' union all
select '0009' union all
select '0007'

select * from #t
order by CONVERT(binary,'0x'+value)

/*
value
--------
0001
0002
0004
0005
0007
0008
0009
0016
0023
*/

[yy_zan004]

declare @s varchar(10),@s1 varchar,@a varchar(20),@n int
set @n = 1
if object_id('tempdb..#2')>0 drop table #2
create table #2(c char)
while @n<=16
begin
set @a = '0123456789abcdef'
select @s = substring(@a,@n,1)
set @n =@n+1
insert into #2(c) values(@s)
end
--select * from #2

if object_id('tempdb..#1')>0 drop table #1
create table #1(c1 char)
insert into #1(c1)
select c from #2
select t.c3+t1.c3 as c4 from (select c+c1 as c3 from #1 cross join #2 ) t
cross join (select c+c1 as c3 from #1 cross join #2 ) t1
order by c4

[haitao]

提供一个新思路：
循环整数 0-0xffff
把整数转为16进制字符串输出

[中国风]

用二进制类型省事

[中国风]

USE tempdb 
GO ;
WITH a AS (SELECT TOP 32 ROW_NUMBER()OVER(ORDER BY RAND()) AS Col FROM sysobjects) SELECT CAST(col  AS BINARY(2)) AS [二进制] FROM a 
/* 0x0001 0x0002 0x0003 0x0004 0x0005 0x0006 0x0007 0x0008 0x0009 0x000A 0x000B 0x000C 0x000D 0x000E 0x000F 0x0010 0x0011 0x0012 0x0013 0x0014 0x0015 0x0016 0x0017 0x0018 0x0019 0x001A 0x001B 0x001C 0x001D 0x001E 0x001F 0x0020 */

[gogodiy]

CREATE TABLE t1
(
	col VARCHAR(1)
)
INSERT INTO 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' UNION ALL
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D' UNION ALL
SELECT 'E' UNION ALL
SELECT 'F'
SELECT * FROM t1

;WITH aaa AS
(SELECT a.col AS col1,b.col AS col2,c.col AS col3,d.col AS col4 FROM t1 AS a 
CROSS JOIN t1 AS b CROSS JOIN t1 AS c CROSS JOIN t1 AS d)
,bbb AS
(SELECT col1+col2+col3+col4 AS col FROM aaa )
SELECT * FROM bbb ORDER BY col

-------------------------
col
0000
0001
0002
0003
0004
0005
0006
0007
...
FFF7
FFF8
FFF9
FFFA
FFFB
FFFC
FFFD
FFFE
FFFF

16的4次方，一共65536条记录

[中国风]

如果只是排序没有什么意义，你用字符串排序就行了，何必转换，用于显示另当别论

把二进制直接转为字符串就行了

[中国风]

[Quote=引用 5 楼 的回复:]

SQL code

CREATE TABLE t1
(
col VARCHAR(1)
)
INSERT INTO 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 '……
[/Quote]
--生成这类组合数字直接调用就行了
SELECT a.COl+b.Col+c.Col+d.Col AS Col FROM t1 AS a,t1 AS b,t1 AS c,t1 AS d ORDER BY Col

[孤独加百列]

WITH L AS
(
	SELECT '0' AS Col 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' UNION
	SELECT 'A' UNION
	SELECT 'B' UNION
	SELECT 'C' UNION
	SELECT 'D' UNION
	SELECT 'E' UNION
	SELECT 'F' 
)

SELECT A.Col + B.Col +  C.Col + D.Col
FROM L AS A,L AS B,L AS C,L AS D