表T1
字段C1 , C2 , C3C1 C2 C3
1 a b
2 c d
3 e f有什么可以可以实现
C1 C2 C3
1 a b
1 c d
1 c d
1 e f
1 e f
1 e f就是把字段C1的数字都分解为1,然后跟后面的值?
字段C1 , C2 , C3C1 C2 C3
1 a b
2 c d
3 e f有什么可以可以实现
C1 C2 C3
1 a b
1 c d
1 c d
1 e f
1 e f
1 e f就是把字段C1的数字都分解为1,然后跟后面的值?
调试欢乐多
(
select 1 as 'c1','a' as 'c2','b' as 'c3' union all
select 2,'c','d' union all
select 3,'e','f'
)
select c1.*
from c1 cross join master..spt_values t
where t.number between 1 and c1.c1 and t.[type] = 'p'c1 c2 c3
----------- ---- ----
1 a b
2 c d
2 c d
3 e f
3 e f
3 e f(6 row(s) affected)
from c1 cross join master..spt_values t
where t.number between 1 and c1.c1 and t.[type] = 'p'2000的话,执行这语句,把from那个c1改成你的表就可以了
from c1 cross join master..spt_values t
where t.number between 1 and c1.c1 and t.[type] = 'p'查询分析提示:
命令已成功完成。执行分析提示:
服务器: 消息 208,级别 16,状态 1,行 1
对象名 'c1' 无效。
INSERT INTO c1
select 1 as 'c1','a' as 'c2','b' as 'c3' union all
select 2,'c','d' union all
select 3,'e','f'
select c1.*
from c1 c1 cross join master..spt_values t
where t.number between 1 and c1.a and t.[type] = 'p'
/*
a b c
----------- ---------- ----------
1 a b
2 c d
2 c d
3 e f
3 e f
3 e f
(6 行受影响)
*/
select t1.*
from t1 cross join master..spt_values t
where t.number between 1 and t1.c1 and t.[type] = 'p'结果集:
C1 C2 C3
1.0000 a b
2.0000 c d
2.0000 c d
3.0000 e f
3.0000 e f
3.0000 e f
能不能可以实现这样子
C1 C2 C3
1.0000 a b
1.0000 c d
1.0000 c d
1.0000 e f
1.0000 e f
1.0000 e f
INSERT INTO c1
select 1 as 'c1','a' as 'c2','b' as 'c3' union all
select 2,'c','d' union all
select 3,'e','f'
select 1 a,c1.b,c1.c
from c1 c1 cross join master..spt_values t
where t.number between 1 and c1.a and t.[type] = 'p'
/*
a b c
----------- ---------- ----------
1 a b
1 c d
1 c d
1 e f
1 e f
1 e f
(6 行受影响)
*/
INSERT INTO c1
select 1 as 'c1','a' as 'c2','b' as 'c3' union all
select 2,'c','d' union all
select 3,'e','f'
select 1 a,c1.b,c1.c
from c1 c1 cross join master..spt_values t
where t.number between 1 and c1.a and t.[type] = 'p'
/*
a b c
----------- ---------- ----------
1 a b
1 c d
1 c d
1 e f
1 e f
1 e f
(6 行受影响)
*/能不能运行结果多一个列
a b c d
----------- ---------- ---------- -----------
1 a b 1-6
1 c d 2-6
1 c d 3-6
1 e f 4-6
1 e f 5-6
1 e f 6-6d字段,前面的数字代表第几个,后面数字代表总数