DECLARE @T TABLE(A INT,B INT) INSERT INTO @T SELECT '1','3' SELECT * FROM @T select a.a,1 as b from @t a join (select 1 as px union select 2 union select 3 union select 4) b on a.b >= b.px
A B ----------- ----------- 1 3(所影响的行数为 1 行)a b ----------- ----------- 1 1 1 1 1 1(所影响的行数为 3 行)
DECLARE @T TABLE(A INT,B INT) INSERT INTO @T SELECT '1','3' SELECT * FROM @T /* A B ----------- ----------- 1 3(所影响的行数为 1 行) */--临时表 select top 8000 identity(int,0,1) as id into tmp from syscolumns a,syscolumns bselect m.A , B = 1 from @T m , tmp n where m.A + n.id <= m.B /* A B ----------- ----------- 1 1 1 1 1 1 */drop table tmp
create TABLE tb(A varchar(10),B INT) INSERT INTO tb SELECT 'P1',3 INSERT INTO tb SELECT 'P2',1 SELECT * FROM tb /* A B ---------- ----------- P1 3 P2 1(所影响的行数为 2 行) */--临时表 select top 8000 identity(int,0,1) as id into tmp from syscolumns a,syscolumns bselect m.A , B = 1 from tb m , tmp n where 1 + n.id <= m.B order by m.A /* A B ---------- ----------- P1 1 P1 1 P1 1 P2 1(所影响的行数为 4 行) */drop table tb,tmp
from syscolumns a,syscolumns b 为什么要这样?我看不懂,请教一下
create TABLE tb(A varchar(10),B INT) INSERT INTO tb SELECT 'P1',3 INSERT INTO tb SELECT 'P2',1 SELECT * FROM tb /* A B ---------- ----------- P1 3 P2 1(所影响的行数为 2 行) */--临时表 declare @i int set @i=9000 select top (@i) identity(int,0,1) as id into tmp from syscolumns a,syscolumns bselect m.A , B = 1 from tb m , tmp n where 1 + n.id <= m.B order by m.A /* A B ---------- ----------- P1 1 P1 1 P1 1 P2 1(所影响的行数为 4 行) */drop table tb,tmptop 用变量就行了
但是这样产品的数量要是大于8000怎么办? --------------------select top 800000 identity(int,0,1) as id into tmp from syscolumns a,syscolumns b,syscolumns c,syscolumns d
SELECT '1','3' SELECT *
FROM @T
select a.a,1 as b
from @t a
join (select 1 as px union select 2 union select 3 union select 4) b on a.b >= b.px
----------- -----------
1 3(所影响的行数为 1 行)a b
----------- -----------
1 1
1 1
1 1(所影响的行数为 3 行)
INSERT INTO @T SELECT '1','3' SELECT * FROM @T
/*
A B
----------- -----------
1 3(所影响的行数为 1 行)
*/--临时表
select top 8000 identity(int,0,1) as id into tmp from syscolumns a,syscolumns bselect m.A , B = 1 from @T m , tmp n where m.A + n.id <= m.B
/*
A B
----------- -----------
1 1
1 1
1 1
*/drop table tmp
我想让
产品 数量
P1 3
P2 1结果
P1 1
P1 1
P1 1
P2 1
INSERT INTO tb SELECT 'P1',3
INSERT INTO tb SELECT 'P2',1
SELECT * FROM tb
/*
A B
---------- -----------
P1 3
P2 1(所影响的行数为 2 行)
*/--临时表
select top 8000 identity(int,0,1) as id into tmp from syscolumns a,syscolumns bselect m.A , B = 1 from tb m , tmp n where 1 + n.id <= m.B order by m.A
/*
A B
---------- -----------
P1 1
P1 1
P1 1
P2 1(所影响的行数为 4 行)
*/drop table tb,tmp
为什么要这样?我看不懂,请教一下
create TABLE tb(A varchar(10),B INT)
INSERT INTO tb SELECT 'P1',3
INSERT INTO tb SELECT 'P2',1
SELECT * FROM tb
/*
A B
---------- -----------
P1 3
P2 1(所影响的行数为 2 行)
*/--临时表
declare @i int
set @i=9000
select top (@i) identity(int,0,1) as id into tmp from syscolumns a,syscolumns bselect m.A , B = 1 from tb m , tmp n where 1 + n.id <= m.B order by m.A
/*
A B
---------- -----------
P1 1
P1 1
P1 1
P2 1(所影响的行数为 4 行)
*/drop table tb,tmptop 用变量就行了
--------------------select top 800000 identity(int,0,1) as id into tmp
from syscolumns a,syscolumns b,syscolumns c,syscolumns d