啊,少打了一个回车键,抱歉
想要的结果更正如下:
----------------------------------------
所以,现在需要一个sql句,已获得如下结果: ZhangSan
ZhangSan
Lisi
Lisi
Lisi
Wangwu
-------------------------------------
想要的结果更正如下:
----------------------------------------
所以,现在需要一个sql句,已获得如下结果: ZhangSan
ZhangSan
Lisi
Lisi
Lisi
Wangwu
-------------------------------------
if object_id('tempdb.dbo.#tbl_user') is not null drop table #tbl_user
create table #tbl_user (userName varchar(8),userQuantity int)
insert into #tbl_user
select 'ZhangSan',2 union all
select 'Lisi',3 union all
select 'Wangwu',1if object_id('tempdb.dbo.#') is not null drop table #
select top 100 id=identity(int,1,1) into # from sysobjectsselect a.* from #tbl_user a, # b where b.id<=a.userQuantity
/*
userName userQuantity
-------- ------------
ZhangSan 2
ZhangSan 2
Lisi 3
Lisi 3
Lisi 3
Wangwu 1
*/
set nocount on
create table test(userName varchar(20),userQuantity varchar(20))
insert into test select 'ZhangSan','2'
insert into test select 'Lisi','3'
insert into test select 'Wangwu','1'
go
--测试declare @t table(userName varchar(100))
while exists(select 1 from test where userQuantity>0)
begin
insert into @t
select userName from test where userQuantity>0
update test set userQuantity=userQuantity-1
end
select * from @t order by username
--删除测试环境
drop table test
set nocount off
set nocount on
create table test(userName varchar(20),userQuantity varchar(20))
insert into test select 'ZhangSan','2'
insert into test select 'Lisi','3'
insert into test select 'Wangwu','1'
go
--测试select a.username from test a,test b where a.userQuantity>=b.userQuantity
--删除测试环境
drop table test
set nocount off
将一楼的改一下就成了.
=================================declare @tmp_table table (userName varchar(8),userQuantity int)insert into @tmp_table
select 'ZhangSan',2 union all
select 'Lisi',3 union all
select 'Wangwu',1
declare @tmp_id table ([id] int identity(1,1),f int )
insert into @tmp_id(f) select top 100 [id] from sysobjectsselect a.* from @tmp_table a, @tmp_id b where b.id<=a.userQuantity
哈哈 , SQL SERVER 版的人有点"变态"...
create table tbl_user (userName varchar(8),userQuantity int)
insert into tbl_user select 'ZhangSan',2
union all select 'Lisi',3
union all select 'Wangwu',1;with Users(userName, Nums) as
(
select userName, 1 as Nums from tbl_user
union all
select U.userName, U.Nums+1 from Users U join tbl_user T on U.userName=T.userName and U.Nums<T.userQuantity
)
select userName from Users order by userName
/*
userName
--------
Lisi
Lisi
Lisi
Wangwu
ZhangSan
ZhangSan(6 row(s) affected)
*/drop table tbl_user
--用临时表解决!create table tbl_user(username varchar(20),Userquantity int)
insert into tbl_user select 'ZhangSan', 2
insert into tbl_user select 'Lisi' , 3
insert into tbl_user select 'Wangwu' , 1 select top 1000 Id=identity(int,1,1) into # from sysobjects a,sysobjects b
select a.username from tbl_user a,# b
where a.Userquantity>=b.idusername
--------------------
ZhangSan
ZhangSan
Lisi
Lisi
Lisi
Wangwu(6 行受影响)
if object_id('tempdb.dbo.#tbl_user') is not null drop table #tbl_user
create table #tbl_user (userName varchar(8),userQuantity int)
insert into #tbl_user
select 'ZhangSan',2 union all
select 'Lisi',3 union all
select 'Wangwu',1-->不用临时表
select a.* from #tbl_user a full join #tbl_user b on a.userQuantity>=b.userQuantity/*
userName userQuantity
-------- ------------
ZhangSan 2
ZhangSan 2
Lisi 3
Lisi 3
Lisi 3
Wangwu 1
*/
insert into tbl_user select 'ZhangSan', 2
insert into tbl_user select 'Lisi' , 3
insert into tbl_user select 'Wangwu' , 1 --用2005中cet递归with cet
as
(
select Username,1 as num from tbl_user
union all
select a.username, b.num+1 from tbl_user a,cet b
where a.userquantity>b.num AND A.USERNAME=B.USERNAME)
select USERNAME from cet
ORDER BY USERNAMEdrop table tbl_userUSERNAME
--------------------
Lisi
Lisi
Lisi
Wangwu
ZhangSan
ZhangSan(6 行受影响)
13楼的只对特定数据有效,(Userquantity必须从1开始并且连续出现,如1,2,3,...)
因为我是做adp程序,它对临时表和虚拟表都不支持(或者我不会),呵呵
(
select username,Userquantity-1 as Userquantity from #tbl_user
union all
select username,Userquantity-1 from t
where Userquantity-1>=0
)
select username from t一句话视图
用CTE,还不如临时表,只要临时表的ID=max(userQuantity),笛卡儿积不至于太大,比CTE要快多了。
你放心,我绝对没有抄袭你的code