---------------------------------------------------------------- -- Author :DBA_HuangZJ(發糞塗牆) -- Date :2014-07-17 11:24:11 -- Version: -- Microsoft SQL Server 2012 - 11.0.5058.0 (X64) -- May 14 2014 18:34:29 -- Copyright (c) Microsoft Corporation -- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor) -- ---------------------------------------------------------------- --> 测试数据:[product] if object_id('[product]') is not null drop table [product] go create table [product]([pname] varchar(4),[pcount] int) insert [product] select '面包',3 union all select '牛奶',4 --------------开始查询--------------------------SELECT pname FROM ( SELECT pname , REPLICATE(CAST(pcount AS VARCHAR(100)) + ',', pcount) pcount FROM product ) a , master..spt_values WHERE number >= 1 AND number <= LEN(pcount) AND type = 'p' AND SUBSTRING(',' + pcount, number, 1) = ',' ----------------结果---------------------------- /* pname ----- 面包 面包 面包 牛奶 牛奶 牛奶 牛奶 */
WITH a1 (pname,pcount) AS ( SELECT '面包',3 UNION ALL SELECT '牛奶',4 ) SELECT a.pname FROM a1 a JOIN master.dbo.spt_values b ON b.number BETWEEN 1 AND a.pcount WHERE b.Type='P'
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-07-17 11:24:11
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[product]
if object_id('[product]') is not null drop table [product]
go
create table [product]([pname] varchar(4),[pcount] int)
insert [product]
select '面包',3 union all
select '牛奶',4
--------------开始查询--------------------------SELECT pname
FROM ( SELECT pname ,
REPLICATE(CAST(pcount AS VARCHAR(100)) + ',', pcount) pcount
FROM product
) a ,
master..spt_values
WHERE number >= 1
AND number <= LEN(pcount)
AND type = 'p'
AND SUBSTRING(',' + pcount, number, 1) = ','
----------------结果----------------------------
/*
pname
-----
面包
面包
面包
牛奶
牛奶
牛奶
牛奶
*/
WITH a1 (pname,pcount) AS
(
SELECT '面包',3 UNION ALL
SELECT '牛奶',4
)
SELECT a.pname
FROM a1 a
JOIN master.dbo.spt_values b ON b.number BETWEEN 1 AND a.pcount
WHERE b.Type='P'
哈哈,用上了~谢谢啦~真是厉害啊~学习了~
嗯嗯,知道了~我去试试
哈哈,你的也是可以的~厉害啊~高手在论坛啊~~
问题终于解决了,可以交差了~~