---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2010-04-15 15:08:36 -- 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) -- ---------------------------------------------------------------- --> 测试数据:[A] if object_id('[A]') is not null drop table [A] go create table [A]([code] int) insert [A] select 1 union all select 2 union all select 3 --> 测试数据:[B] if object_id('[B]') is not null drop table [B] go create table [B]([ID] int,[name] varchar(4),[pid] int) insert [B] select 1,'haha',0 union all select 2,'hoho',1 union all select 3,'hehe',1 --------------开始查询-------------------------- ;with f as ( select * from b where id=(select code from a where code=3) union all select a.* from b a, f b where a.id=b.pid ) select * from f order by 1 ----------------结果---------------------------- /* ID name pid ----------- ---- ----------- 1 haha 0 3 hehe 1(2 行受影响) */
select * from b where id=(select code from a where code=3) union all select a.* from b a, f b where a.id=b.pid额。。谢谢楼上的回帖,但是我执行这个语句,这个f是干么的呢?
2005 CTE里面的语法 f 是CTE的名字 用于递归
能否写通俗一点的。。能执行出结果的SQL语句就行
ding ding ding ding ....
用CTE是正解,表不用重新设了,直接放在一个表中就行。
CTE改如何用呢?新潮玩意不懂求解。。
我是在程序中写查询语句CTE也能用吗?
程序中执行的是sql字符串。CET不了解可以去MSDN上看看例子
我现在就是要在程序中用代码执行SQL语句
begin declare @t table (k int identity, a varchar(500)) insert into @t(a) select reMark from Detect2 where Detectid=@ID and ProductNo=@ProductNo update @t set a=a+' ' with cte(a, k) as ( select cast(t.a as varchar(max)), t.k from @t t where k = 1 union all select c.a + cast(t.a as varchar(max)), t.k from @t t join cte c on t.k = c.k+1 ) select top 1 a from cte order by k desc end
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-15 15:08:36
-- 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)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([code] int)
insert [A]
select 1 union all
select 2 union all
select 3
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([ID] int,[name] varchar(4),[pid] int)
insert [B]
select 1,'haha',0 union all
select 2,'hoho',1 union all
select 3,'hehe',1
--------------开始查询--------------------------
;with f as
(
select * from b where id=(select code from a where code=3)
union all
select a.* from b a, f b where a.id=b.pid
)
select * from f order by 1
----------------结果----------------------------
/* ID name pid
----------- ---- -----------
1 haha 0
3 hehe 1(2 行受影响)
*/
union all
select a.* from b a, f b where a.id=b.pid额。。谢谢楼上的回帖,但是我执行这个语句,这个f是干么的呢?
2005 CTE里面的语法 f 是CTE的名字 用于递归
declare @t table (k int identity, a varchar(500))
insert into @t(a)
select reMark from Detect2 where Detectid=@ID and ProductNo=@ProductNo
update @t set a=a+' '
with cte(a, k)
as
( select cast(t.a as varchar(max)), t.k from @t t
where k = 1
union all
select c.a + cast(t.a as varchar(max)), t.k
from @t t
join cte c on t.k = c.k+1 )
select top 1 a from cte order by k desc
end