3级的话,进行3次join即可。看看无限级。
解决方案 »
- 请对存储过程比较熟悉的进来帮帮忙
- datepart 获取 year
- 挑战一下这个麻烦的sql (难度加大)
- 求达人帮忙。。。
- 简单的类型转换
- 再开一贴:收藏 不显示删除回复显示所有回复显示星级回复显示得分回复 急急!!单位的系统经常突然性死机,重启服务器后,系统正常
- 我用powerdesign设计表,但我选字段数据类型时没找到autoincrement一项,为什么,我该如何完成
- 问两个平常少遇到的问题,有兴趣的人进
- 能不能选择性插入
- 我有一个程序,后台是SQL7,用Ttable,把cachedupdates设置为True,然后用Applyupdate,在一个用户时正常,但如果两人同时提交,两台机的程序都死掉了。急!!!!!
- 如何在SQLSERVER2000的一个函数中定义一个常量啊?
- 怎么样编写下像这种交插表的代码
if exists (select * from dbo.sysobjects where id = object_id(N'[tb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tb]
GO-- 示例数据
create table [tb]([id] int PRIMARY KEY,[pid] int,name nvarchar(20))
INSERT [tb] SELECT 1,0,N'中国'
UNION ALL SELECT 2,0,N'美国'
UNION ALL SELECT 3,0,N'加拿大'
UNION ALL SELECT 4,1,N'北京'
UNION ALL SELECT 5,1,N'上海'
UNION ALL SELECT 6,1,N'江苏'
UNION ALL SELECT 7,6,N'苏州'
UNION ALL SELECT 8,7,N'常熟'
UNION ALL SELECT 9,6,N'南京'
UNION ALL SELECT 10,6,N'无锡'
UNION ALL SELECT 11,2,N'纽约'
UNION ALL SELECT 12,2,N'旧金山'
GO -- 查询指定id的所有子
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_cid]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_cid]
GO
-- =====================================================
-- 查询指定id的所有子
-- 邹建 2005-07(引用请保留此信息)-- 调用示例/*--调用(查询所有的子)
SELECT A.*,层次=B.[level]
FROM [tb] A,f_cid(2)B
WHERE A.[id]=B.[id]
--*/
-- =====================================================
CREATE FUNCTION f_cid(@id int)
RETURNS TABLE
AS
RETURN(
WITH ctb([id],[level])
AS(
SELECT [id],1 FROM [tb]
WHERE [pid]=@id
UNION ALL
SELECT A.[id],B.[level]+1
FROM [tb] A,ctb B
WHERE A.[pid]=B.[id])
SELECT * FROM ctb
--如果只显示最明细的子(下面没有子),则将上面这句改为下面的
-- SELECT * FROM ctb A
-- WHERE NOT EXISTS(
-- SELECT 1 FROM [tb] WHERE [pid]=A.[id])
)
GO--调用(查询所有的子)
SELECT A.*,层次=B.[level]
FROM [tb] A,f_cid(2)B
WHERE A.[id]=B.[id]
GO -- 查询指定id的所有父
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_pid]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_pid]
GO
-- =====================================================
-- 查询指定id的所有父
-- 邹建 2005-07(引用请保留此信息)-- 调用示例/*--调用(查询所有的父)
SELECT A.*,层次=B.[level]
FROM [tb] A,[f_pid](2)B
WHERE A.[id]=B.[id]
--*/
-- =====================================================
CREATE FUNCTION [f_pid](@id int)
RETURNS TABLE
AS
RETURN(
WITH ptb([id],[level])
AS(
SELECT [pid],1 FROM [tb]
WHERE [id]=@id
AND [pid]<>0
UNION ALL
SELECT A.[pid],B.[level]+1
FROM [tb] A,ptb B
WHERE A.[id]=B.[id]
AND [pid]<>0)
SELECT * FROM ptb
)
GO--调用(查询所有的父)
SELECT A.*,层次=B.[level]
FROM [tb] A,[f_pid](7)B
WHERE A.[id]=B.[id]
GO -- 树形分级显示 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_id]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_id]
GO
-- =====================================================
-- 级别及排序字段(树形分级显示)
-- 邹建 2005-07(引用请保留此信息)-- 调用示例/*--调用实现树形显示 --调用函数实现分级显示
SELECT N'|'+REPLICATE('-',B.[level]*4)+A.name
FROM [tb] A,f_id()B
WHERE a.[id]=b.[id]
ORDER BY b.sid --当然,这个也可以根本不用写函数,直接排序即可
WITH stb([id],[level],[sid])
AS(
SELECT [id],1,CAST(RIGHT(10000+[id],4) as varchar(8000))
FROM [tb]
WHERE [pid]=0
UNION ALL
SELECT A.[id],B.[level]+1,B.sid+RIGHT(10000+A.[id],4)
FROM [tb] A,stb B
WHERE A.[pid]=B.[id])
SELECT N'|'+REPLICATE('-',B.[level]*4)+A.name
FROM [tb] A,stb B
WHERE a.[id]=b.[id]
ORDER BY b.sid
--*/
-- =====================================================
CREATE FUNCTION f_id()
RETURNS TABLE
AS
RETURN(
WITH stb([id],[level],[sid])
AS(
SELECT [id],1,CAST(RIGHT(10000+[id],4) as varchar(8000))
FROM [tb]
WHERE [pid]=0
UNION ALL
SELECT A.[id],B.[level]+1,B.sid+RIGHT(10000+A.[id],4)
FROM [tb] A,stb B
WHERE A.[pid]=B.[id])
SELECT * FROM stb
)
GO--调用函数实现分级显示
SELECT N'|'+REPLICATE('-',B.[level]*4)+A.name
FROM [tb] A,f_id()B
WHERE a.[id]=b.[id]
ORDER BY b.sid
GO
-- 直接查询的应用实例
-- ===================================================== -- 1. 每个叶子结点的 FullName
WITH stb([id],[FullName],[pid],[flag])
AS(
SELECT [id],CAST(RTRIM([name]) as nvarchar(4000)),[pid],1
FROM [tb] A
WHERE NOT EXISTS(
SELECT 1 FROM [tb]
WHERE [pid]=A.[id])
UNION ALL
SELECT A.[id],RTRIM(B.[name])+'/'+A.[FullName],B.[pid],A.flag+1
FROM stb A,[tb] B
WHERE A.[pid]=B.[id])
SELECT [id],[FullName] FROM stb A
WHERE NOT EXISTS(
SELECT * FROM stb
WHERE [id]=A.[id]
AND flag>A.flag)
ORDER BY [id]
GO -- 2. 每个结点的 FullName
WITH stb([id],[FullName],[pid],[flag])
AS(
SELECT [id],CAST(RTRIM([name]) as nvarchar(4000)),[pid],1
FROM [tb]
UNION ALL
SELECT A.[id],RTRIM(B.[name])+'/'+A.[FullName],B.[pid],A.flag+1
FROM stb A,[tb] B
WHERE A.[pid]=B.[id])
SELECT [id],[FullName] FROM stb A
WHERE NOT EXISTS(
SELECT * FROM stb
WHERE [id]=A.[id]
AND flag>A.flag)
ORDER BY [id]
GO -- 3. 树形显示数据
WITH stb([id],[level],[sid])
AS(
SELECT [id],1,CAST(RIGHT(10000+[id],4) as varchar(8000))
FROM [tb]
WHERE [pid]=0
UNION ALL
SELECT A.[id],B.[level]+1,B.sid+RIGHT(10000+A.[id],4)
FROM [tb] A,stb B
WHERE A.[pid]=B.[id])
SELECT N'|'+REPLICATE('-',B.[level]*4)+A.name
FROM [tb] A,stb B
WHERE a.[id]=b.[id]
ORDER BY b.sid
GO -- 4. 检查不规范的数据
WITH chktb([id],[pid],[level],[Path],[Flag])
AS(
SELECT [id],[pid],1,
CAST([id] as varchar(8000)),
CASE WHEN [id]=[pid] THEN 1 ELSE 0 END
FROM [tb]
UNION ALL
SELECT A.[id],B.[pid],B.[level]+1,
CAST(B.[Path]+' > '+RTRIM(A.[id]) as varchar(8000)),
CASE WHEN A.[id]=B.[pid] THEN 1 ELSE 0 END
FROM [tb] A,chktb B
WHERE A.[pid]=B.[id]
AND B.[Flag]=0)
SELECT * FROM chktb
WHERE [Flag]=1
ORDER BY [Path]
GO -- 5. 查询结点的所有子结点数
WITH sumtb([id],[level])
AS(
SELECT [pid],1
FROM [tb] A
WHERE [pid]<>0
UNION ALL
SELECT A.[pid],B.[level]+1
FROM [tb] A,sumtb B
WHERE A.[id]=B.[id]
AND A.[pid]<>0)
SELECT A.[id],ChildCounts=COUNT(b.[id])
FROM [tb] A
LEFT JOIN sumtb B
ON A.[id]=B.[id]
GROUP BY A.[id]
GO -- 6. 查询结点的所有父结点数
WITH sumtb([id],[level],[ParentCounts])
AS(
SELECT [id],1,0
FROM [tb] A
WHERE [pid]=0
UNION ALL
SELECT A.[id],B.[level]+1,B.[ParentCounts]+1
FROM [tb] A,sumtb B
WHERE A.[pid]=B.[id])
SELECT * FROM sumtb
order by [ID]
GO
--建立測試環境
Create Table Tree(
Id Int Identity(1,1),
Name Varchar(10),
ParentId Int)
--插入數據
Insert Tree Select 'N_1', 0
Union All Select 'N_2', 1
Union All Select 'N_3', 1
Union All Select 'N_4', 3
Union All Select 'N_5', 2
Union All Select 'N_6', 2
Union All Select 'N_7', 4
Union All Select 'N_8', 7
Union All Select 'N_9', 8
Union All Select 'N_10', 8
Go
--建立函數
Create Function GetChild(@ParentId Int,@Rank Int)
Returns @Rout Table (Id Int,Name Varchar(10),ParentId Int,Rank Int)
As
Begin
Declare @I Int
Set @I=0
Insert @Rout Select *,0 From Tree Where Id=@ParentId
While @@RowCount>0 And @I+1<=@Rank
Begin
Select @I=@I+1
Insert @Rout Select A.*, @I From Tree A Inner Join @Rout B On A.ParentId=B.Id Where A.ParentId Not In (Select Distinct ParentId From @Rout)
End
Return
End
GO
--測試
Select *From dbo.GetChild(1,3)
Select *From dbo.GetChild(3,3)
GO
--刪除測試環境
Drop Table Tree
Drop Function GetChild
GO
--結果
/*
Id Name ParentId Rank
1 N_1 0 0
2 N_2 1 1
3 N_3 1 1
5 N_5 2 2
6 N_6 2 2
4 N_4 3 2
7 N_7 4 3Id Name ParentId Rank
3 N_3 1 0
4 N_4 3 1
7 N_7 4 2
8 N_8 7 3
*/
RETURNS TABLE
AS
RETURN(
WITH ctb([id],[level])
AS(
SELECT [id],1 FROM [tb]
WHERE [pid]=@id
UNION ALL
SELECT A.[id],B.[level]+1
FROM [tb] A,ctb B
WHERE A.[pid]=B.[id])
SELECT * FROM ctb
)
GO为什么执行不过,提示"在关键字 'WITH' 附近有语法错误"