现有表 test
字段 如下
prd qty
test1 2
test2 3要求拆分的结果如下
prd xuhao
test1 1
test1 2
test2 1
test2 2
test2 3请问我如何修改下面的语句实现这样的功能
select * from test t
cross join master..spt_values d
where
d.type = 'P' and d.number between 1 and t.qty(注:其他方法也可)
字段 如下
prd qty
test1 2
test2 3要求拆分的结果如下
prd xuhao
test1 1
test1 2
test2 1
test2 2
test2 3请问我如何修改下面的语句实现这样的功能
select * from test t
cross join master..spt_values d
where
d.type = 'P' and d.number between 1 and t.qty(注:其他方法也可)
from test t
cross join master..spt_values d
where d.type = 'P' and d.number between 1 and t.qty
from test t
cross join master..spt_values d
where d.type = 'P' and d.number<=(select max(qty) from test where test.prd=t.prd)
CREATE TABLE test
(prd VARCHAR(10),qty int)
INSERT INTO test
SELECT 'test1',2 UNION ALL
SELECT 'test2',3 SELECT prd,number AS xuhao FROM test a
CROSS JOIN master..spt_values b
WHERE qty>=number AND number >0
AND type='p'prd xuhao
---------- -----------
test1 1
test1 2
test2 1
test2 2
test2 3DROP TABLE test
code]
改成 select prd , number as xuhao