---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2010-04-20 17:20:08 -- Verstion: -- Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86) -- May 26 2009 14:24:20 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([col] int) insert [tb] select 1 union all select 2 union all select 3 union all select 4 union all select 5 --------------开始查询-------------------------- DECLARE @STR VARCHAR(8000)SELECT @STR=ISNULL(@STR+',','')+ltrim(col) FROM (SELECT DISTINCT col FROM tb)AS TSELECT @STR ----------------结果---------------------------- /* ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1,2,3,4,5(1 行受影响) */
create table #(ID int) go insert into # select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8--查询结果 declare @str varchar(8000) set @str = '(' select top 5 @str = @str + ',' + cast(ID as varchar) from # set @str = stuff(@str, 2, 1, '') + ')' print @str--结果 /* (1,2,3,4,5) */
book表: id,title,bfid,zcid 现有数据 id title bfid zcid id title bfid zcid 1 a 1 3,4 1 a 1 3,4 2 b 2 2 b 2 3 c 3 2,5 我复制了结构数据 3 c 3 2,5 4 d 4 4 d 4 5 e 5 5 e 5 6 e 1 3,4 现在要把这个的值变成8,9 7 e 2 8 e 3 2,5 这个变成7,10 9 e 4 10 e 5
如字段zcid=1,2,3 我现在复制了数据结构。4,5,6 zcid要等于新数据的ID(4,5,6) 意思就是zcid=(select id from X where bfid in(1,2,3)) 怎么实现
book表: id,title,bfid,zcid 现有数据 id title bfid zcid 1 a 0 3,4 2 b 0 3 c 0 2,5 4 d 0 5 e 0 我复制了结构数据 1 a 0 3,4 2 b 0 3 c 0 2,5 4 d 0 5 e 56 a 1 3,4 7 b 2 8 c 3 2,5 9 d 4 10 e 5 现在要把新复制数据里的3,4改成8,9 把2,5改成7,10
/* 在TOP后面使用变量 (爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2008-01-02 广东深圳) */--SQL SERVER 2005 的写法 use adventureworks goDECLARE @Percentage int SET @Percentage = 1 SELECT TOP (@Percentage) PERCENT Name FROM Production.Product ORDER BY Name/* Name ---------------------- Adjustable Race All-Purpose Bike Stand AWC Logo Cap BB Ball Bearing Bearing Ball Bike Wash - Dissolver(6 行受影响) */----------------------------------- --SQL SERVER 2000 的写法 create table a([id] [int]) insert into a(id) values(1) insert into a(id) values(2) insert into a(id) values(3) insert into a(id) values(4) insert into a(id) values(5)declare @num as int declare @sql as varchar(2000) set @num = 2 set @sql = 'select top ' + cast(@num as char) + ' * from a' exec(@sql)drop table a /* id ----------- 1 2 */
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-20 17:20:08
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86)
-- May 26 2009 14:24:20
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] int)
insert [tb]
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5
--------------开始查询--------------------------
DECLARE @STR VARCHAR(8000)SELECT @STR=ISNULL(@STR+',','')+ltrim(col) FROM (SELECT DISTINCT col FROM tb)AS TSELECT @STR
----------------结果----------------------------
/* ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1,2,3,4,5(1 行受影响)
*/
create table #(ID int)
go
insert into #
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8--查询结果
declare @str varchar(8000)
set @str = '('
select top 5 @str = @str + ',' + cast(ID as varchar)
from #
set @str = stuff(@str, 2, 1, '') + ')'
print @str--结果
/*
(1,2,3,4,5)
*/
如字段zcid=1,2,3
我现在复制了数据结构。4,5,6
zcid要等于新数据的ID(4,5,6)
并简单说明运算规则
这样别人也能迅速地创建与楼主相同环境,并得出结果,提高了问题得到解答的概率
id,title,bfid,zcid
现有数据
id title bfid zcid id title bfid zcid
1 a 1 3,4 1 a 1 3,4
2 b 2 2 b 2
3 c 3 2,5 我复制了结构数据 3 c 3 2,5
4 d 4 4 d 4
5 e 5 5 e 5
6 e 1 3,4 现在要把这个的值变成8,9
7 e 2
8 e 3 2,5 这个变成7,10
9 e 4
10 e 5
我现在复制了数据结构。4,5,6
zcid要等于新数据的ID(4,5,6) 意思就是zcid=(select id from X where bfid in(1,2,3))
怎么实现
id,title,bfid,zcid
现有数据
id title bfid zcid
1 a 0 3,4
2 b 0
3 c 0 2,5
4 d 0
5 e 0
我复制了结构数据
1 a 0 3,4
2 b 0
3 c 0 2,5
4 d 0
5 e 56 a 1 3,4
7 b 2
8 c 3 2,5
9 d 4
10 e 5
现在要把新复制数据里的3,4改成8,9
把2,5改成7,10
在TOP后面使用变量
(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2008-01-02 广东深圳)
*/--SQL SERVER 2005 的写法
use adventureworks
goDECLARE @Percentage int
SET @Percentage = 1
SELECT TOP (@Percentage) PERCENT
Name
FROM Production.Product
ORDER BY Name/*
Name
----------------------
Adjustable Race
All-Purpose Bike Stand
AWC Logo Cap
BB Ball Bearing
Bearing Ball
Bike Wash - Dissolver(6 行受影响)
*/-----------------------------------
--SQL SERVER 2000 的写法
create table a([id] [int])
insert into a(id) values(1)
insert into a(id) values(2)
insert into a(id) values(3)
insert into a(id) values(4)
insert into a(id) values(5)declare @num as int
declare @sql as varchar(2000)
set @num = 2
set @sql = 'select top ' + cast(@num as char) + ' * from a'
exec(@sql)drop table a
/*
id
-----------
1
2
*/