SQLÊý¾Ý¿âCTEµÄÓ÷¨
Ôںܶà±à³ÌÓïÑÔÖж¼ÓÐ forÑ»·ÕâÑùµÄ¶«Î÷¡£ÔÚÊý¾Ý¿âÀïÃæ Ìæ´úËûÊÇ Óαê
µ«ÊÇÓαêʹÓÃÆðÀ´ÊÇÏ൱ºÄ·Ñ×ÊÔ´µÄ£¬½ñÌì¿´¼ûÒ»¸öCTE³¢ÊÔÁËÏÂËûµÄÓ÷¨
create table employewhere
(
id int identity(1,1),
[name] varchar(10),
[value] varchar(10),
[ttime] int
)
insert employewhere
select 'ÕÅÈý',2,1
union all
select 'ÕÅÈý',2,2
union all
select 'ÕÅÈý',2,3
union all
select 'ÕÅÈý',2,4
union all
select 'ÀîËÄ',2,1
union all
select 'ÀîËÄ',2,2
union all
select 'ÀîËÄ',2,3
union all
select 'ÀîËÄ',2,4
union all
select 'ÀîËÄ',2,1
insert employewhere
select 'ÍõÎå',2,1
union all
select 'ÍõÎå',2,3
union all
select 'ÍõÎå',2,4
ÎÒÏëµÃµ½ttimeΪÁ¬ÐøÊý×ÖµÄname
ÕÅÈý
ÀîËÄ
select * from employewhere
1 ÕÅÈý 2 1
2 ÕÅÈý 2 2
3 ÕÅÈý 2 3
4 ÕÅÈý 2 4
5 ÀîËÄ 2 1
6 ÀîËÄ 2 2
7 ÀîËÄ 2 3
8 ÀîËÄ 2 4
9 ÍõÎå 2 1
10 ÍõÎå 2 3
11 ÍõÎå 2 4
12 ÍõÎå 2 1
13 ÍõÎå 2 3
14 ÍõÎå 2 4
15 ÍõÎå 2 1
16 ÍõÎå 2 3
17 ÍõÎå 2 4
-----------------------------
with myCTE as
(
select id,[name],value,ttime ,1 as number from employewhere where value=2
union all
select tt.id,tt.name,tt.value,tt.ttime ,number+1 from employewhere as tt
inner join myCTE on myCTE.[name]=tt.[name] and tt.ttime=myCTE.ttime+1--Á¬½ÓÆðÀ´µÄÌõ¼þ
where tt.value=2
)
select * from myCTE where number>3
8 ÀîËÄ 2 4 4
4 ÕÅÈý 2 4 4
µ«ÊÇΪʲôҪÕâôдÄØ
ÎÒÃÇ¿ÉÒÔÕâôִÐвéѯÀïÃæµÄÊý¾Ý
with myCTE as
(
select id,[name],value,ttime ,1 as number from employewhere where value=2
union all
select tt.id,tt.name,tt.value,tt.ttime ,number+1 from employewhere as tt
inner join myCTE on myCTE.[name]=tt.[name] and tt.ttime=myCTE
Ïà¹ØÎĵµ£º
1£ºexec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '192.168.*.12'
exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, 'sa ', 'F00000'
2£º
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', ......
½ñÌì¸Õ¿ªÍ¨Õâ¸öС²©£¬×÷ΪÇì×££¬°ÑÎÒÒÔÇ°¿Õ¼äÖеÄһƪÎÄÕÂת¹ýÀ´¡£
Ï£Íû¶Ô
ÎÊÌ⣺ÓÉÓÚ°²×°sql server 2000 sp4ÒÔºóÐÞ¸ÄÁË·þÎñÆ÷µÄÃû³Æµ¼ÖÂSQLÖб£ÁôµÄ·þÎñÆ÷Ãû²»ÕýÈ·¡£
½â¾ö·½·¨£º
1.select @@servername
²é¿´·µ»ØÖµÊÇ·ñÓëÏÖÔÚ»úÆ÷Ãû²»Ò»Ö£¨Èç¹ûÒ»Ö¾ÍÊDZðµÄÎÊÌâÁË£©
2.exec sp_helpserver
²é¿´Ó¦¸ÃÓÐNAMEÓënetwork ......
1>ͨ¹ýÆóÒµ¹ÜÀíÆ÷½øÈë²éѯ·ÖÎöÖ´ÐУº
EXEC sp_password NULL, 'ÄãµÄÐÂÃÜÂë', 'sa'
Èç¹ûÄãÔÚNTÏÂ(°üÀ¨2000)×°µÄSQL Server£¬Ôò¿ÉÒÔÕâÑù×ö£º
Ö±½Ó´ò¿ª"²éѯ·ÖÎöÆ÷"(×¢Òâ²»ÊÇ´Ó"ÆóÒµ¹ÜÀíÆ÷"ÖнøÈë,¿ÉÒÔ´Ó¿ªÊ¼²Ëµ¥µÄ³ÌÐò×é½øÈ¥,Èç¹ûÕÒ²»µ½µÄ»°,Ö±½ÓÔÚ"ÔËÐÐ"ÖÐÊäÈë"isqlw.exe"Ò²¿É);
Ñ¡Ôñ"WindowsÉí·ÝÑéÖ¤"½øÈë,²»ÐèÒªÊäÈ ......
1¡¢ÆôÓÃÔ¶³ÌÁ¬½Ó
¿ªÊ¼——ËùÓгÌÐò——Microsoft SQL Server 2008——ÅäÖù¤¾ß——SQL ServerÅäÖùÜÀíÆ÷
SQL Server NetWork ConfigurationÖÐÑ¡ÔñÐÒ飬"Named pipes"ºÍ"TCP/IP"¶¼Ñ¡ÔñEnable£¬È»ºóµã»÷"TCP/IP"Ñ¡ÊôÐÔ£¬IP Address Ñ¡Ï£¬½«ËùÓÐµÄ TCP¶¯Ì¬¶Ë¿Ú µÄ0È«²¿È¥µô£¬ÔÚ ......