技术性散分：不用游标做一个汇总的逆过程
比如：表a：
3 beijin
2 上海
变成：
beijin
beijin
beijin
上海
上海
[解决办法]
create table T(id int, name nvarchar(10))
insert T select 3, 'beijin '
union all select 2, '上海 '
declare @i int
select @i=max(id) from T
set rowcount @i
select ID=identity(int, 1, 1) into #T from syscolumns, sysobjects
set rowcount 0
select A.name from T as A, #T as B
where A.id> =B.id order by name
--result
name
----------
beijin
beijin
beijin
上海
上海
(5 row(s) affected)
[解决办法]
--删除环境
drop table T, #T
[解决办法]
create table ta(id int, name varchar(10))
insert ta
select 3 , 'beijin '
union all select 2 , '上海 '
declare @ta table(id int)
declare @i int,@j int
select @i=1,@j=10
while @i!> @j
begin
insert @ta select @i
select @i=@i+1
end
select name from @ta a ,ta b
where a.id!> b.id
name
----------
beijin
beijin
beijin
上海
上海
（所影响的行数为 5 行）
[解决办法]
--试试
create table #a(i int,a varchar(10))
insert into #a
select 3, 'beijing ' union all
select 2, 'shanghai 'union all
select 4, 'qingdao '
declare @i int,@a varchar(10)
select top 1 @i = i,@a = a from #a
where i > 1
while @@ROWCOUNT> 0
begin
update #a
set i = 1
where a = @a
while(@i > 1)
begin
insert into #a
select 1,@a
set @i = @i -1
end
select top 1 @i = i,@a = a from #a
where i > 1
end
select * from #a
order by 1,2
drop table #a
i a
----------- ----------
1 beijing
1 beijing
1 beijing
1 qingdao
1 qingdao
1 qingdao
1 qingdao
1 shanghai
1 shanghai
（所影响的行数为 9 行）
[解决办法]
我的方法好像是最笨的。
[解决办法]
哈哈
学习下
顺便接点分
[解决办法]
接分........
[解决办法]
这一段是生成一个递增的表变量从1-10,表变量和表连接
a.id!> b.id--如果表的id为3,不大于变量的id,对应的为1,2,3有三次.
与楼上的生成临时表是相同的
declare @ta table(id int)
declare @i int,@j int
select @i=1,@j=10
while @i!> @j
begin
insert @ta select @i
select @i=@i+1
end
[解决办法]
哦，跟楼上差不多。
[解决办法]
好方法都被抢光了。。。
------解决方案--------------------
create table #T(id int, name nvarchar(10))
GO
insert #T select 3, 'beijin '
union all select 2, '上海 '
GO
DECLARE @str varchar(4000)
select @str= ' '
select @str=@str+REPLICATE(name+ ', ',id) from #T
create table #T2(name nvarchar(10))
while @str <> ', ' AND @str <> ' '
BEGIN
insert into #T2
select LEFT(@str,CHARINDEX( ', ',@str)-1)
select @str=RTRIM(RIGHT(@str,len(@str)-CHARINDEX( ', ',@str)))
END
select * from #T2
用一个比较笨的方法
[解决办法]
name
----------
beijin
beijin
beijin
上海
上海
(5 行受影响)
[解决办法]
--创建测试数据
CREATE TABLE dbo.#
(cnt Int,
dIS NVARCHAR(20) )
INSERT INTO #
SELECT 3,N 'beijin '
UNION all
SELECT 2,N '上海 '
--得到结果
SELECT a.dis
FROM # a INNER JOIN syscolumns c
ON c.id=14
AND c.colid <=a.cnt
dis
--------------------
beijin
beijin
beijin
上海
上海
(5 row(s) affected)
[解决办法]
create table #T(id int, name nvarchar(10))
GO
insert #T select 3, 'beijin '
union all select 2, '上海 '
GO
DECLARE @str varchar(4000)
select @str= ' '
select @str=@str+REPLICATE(name+CHAR(13)+CHAR(10),id) from #T
SELECT @str
(以文本的方式显示结果)
[解决办法]
CREATE TABLE #tbA(id int, city varchar(10))
INSERT INTO #tbA
SELECT 3, 'beijing ' UNION
SELECT 2, '上海 '
CREATE TABLE #tbB(id int)
DECLARE @CNT INT
SELECT @CNT = MAX(ID) FROM #tbA
WHILE @CNT> 0
BEGIN
INSERT INTO #tbB SELECT @CNT
SET @CNT = @CNT -1
END
SELECT A.CITY
FROM
#tbAAJOIN
#tbBBON B.ID <= A.ID
[解决办法]
--生成测试数据
declare @a table(id int,name varchar(10))
insert into @a select 3, 'beijin '
insert into @a select 2, '上海 '
--执行查询
select
a.name
from
@a a,
(select (select count(*) from sysobjects where id> =t.id) as id from sysobjects t) b
where
a.id> =b.id
order by
a.name
--输出结果
/*
name
----------
beijin
beijin
beijin
上海
上海
*/
[解决办法]
create table #T(id int, name nvarchar(10))
GO
insert #T select 3, 'beijin '
union all select 2, '上海 '
GO
DECLARE @str varchar(4000)
select @str= ' '
select @str=@str+REPLICATE(name+CHAR(13)+CHAR(10),id) from #T
SELECT @str
(以文本的方式显示结果)
--精彩，
declare @t table (cnt int , [name] nvarchar(100))
insert into @t values (3, 'beijign ')
insert into @t values (3, '上海 ')
DECLARE @str varchar(4000)
select @str= ' '
select @str=@str+REPLICATE( 'select ' ' '+ name + ' ' ' union all ' ,cnt) from @t
SELECT @str =replace(@str + '@@ ', 'union all @@ ', ' ')
exec (@str)
--表格显示
[解决办法]
學習+接分
