1樓:匿名使用者
用標準sql的with實現遞迴查詢(sql2005以上肯定支援,sql2000不清楚是否支援):
with subqry(id,name,pid) as (select id,name,pid from test1 where id = 5
union all
select test1.id,test1.name,test1.pid from test1,subqry
where test1.pid = subqry.id)select * from subqry;
2樓:匿名使用者
select * from tablename where pid=@pid
3樓:
1 set ansi_nulls on
2 go
3 set quoted_identifier on
4 go
5 6 create function [dbo].[fn_getsubcasnotree](@cas_no varchar(20))
7 returns @cas table
8 (9 cas_no varchar(20) collate database_default,這個不加sql2000會報錯。
10 level int
11 )
1213 as
14 begin
15 declare @level int
16 set @level = 1
17 insert into @cas select @cas_no , @level
18 while @@rowcount > 0
19 begin
20 set @level = @level + 1
21 insert into @cas select a.cas_no , @level
22 from casn a , @cas b
23 where a.up = b.cas_no and b.level = @level - 1
24 end
25 return
26 end
sqlserver查詢各系各科成績最高分的學生的學號,姓名
select a.sno 學號,a.sname 姓名,a.sdept 系名,c.cname 課程名稱,b.maxgrade 成績 from student a inner join select cno,max sno sno,max grade maxgrade from sc group by ...
在SQL Server如何在幾個表裡面查詢資料問題如下
1 查詢 001 課程比 002 課程成績高的所有學生的學號?select c1.s from c c1 join c c2 on c1.s c2.s where c1.c 001 and c2.c 002 and c1.score c2.score 2 查詢平均成績大於60分的同學的學號和平均成績...
怎麼在sqlserver中查詢表中某個資料重複條數
select from select count a as num a from table1 group by a bb where num 1 其中a為你要統計的欄位。用什麼語言 啊那我用c 了 string strsql select count from table 1 where age ...