我现在有个问题:
比如: 一张表中结构如下:
ID 数量
1 2
2 2
现在要通过SQL得到如下表:
ID 数量(新)
1 1
1 1
2 1
2 1
请问这个SQL语句如何编写.
比如: 一张表中结构如下:
ID 数量
1 2
2 2
现在要通过SQL得到如下表:
ID 数量(新)
1 1
1 1
2 1
2 1
请问这个SQL语句如何编写.
/*------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-12 09:30:24
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)------------------------------------------------------------------*/
--> 生成测试数据表:tbIF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([ID] INT,[数量] INT)
INSERT [tb]
SELECT 1,2 UNION ALL
SELECT 2,2
GO
--SELECT * FROM [tb]-->SQL查询如下:
select A.ID,1 AS 数量
from tb a
join master..spt_values b
on b.type='p' and b.number between 1 and a.数量
/*
ID 数量
----------- -----------
1 1
1 1
2 1
2 1(4 行受影响)
*/
if object_id('[TB]') is not null drop table [TB]
create table [TB]([ID] int,[数量] int)
insert [TB]
select 1,2 union all
select 2,2 UNION ALL
SELECT 3,3select * from [TB]SELECT id ,
1
FROM ( SELECT TOP 100 A.id ,A.数量,
[Num] = ROW_NUMBER() OVER ( PARTITION BY A.id ORDER BY A.id ASC )
FROM tb A
CROSS JOIN tb B
ORDER BY A.id,A.数量
) T
WHERE T.num <= T.数量
/*id (无列名)
1 1
1 1
2 1
2 1
3 1
3 1
3 1*/笨办法,参考哈!
还是tony哥快,很快想到spt_values表,学习学习...