某公司变态面试题，召唤SQL达人。

第一题可以贴一下：
CREATE TABLE BOM(PID INT,ID INT)
INSERT INTO BOM SELECT 801,101
INSERT INTO BOM SELECT 801,102
INSERT INTO BOM SELECT 801,103
INSERT INTO BOM SELECT 801,601
INSERT INTO BOM SELECT 601,101
INSERT INTO BOM SELECT 601,105
INSERT INTO BOM SELECT 601,501
INSERT INTO BOM SELECT 501,106
INSERT INTO BOM SELECT 501,121
GOCREATE FUNCTION F_GETROOT(@PID INT)
RETURNS INT
AS
BEGIN
    DECLARE @ID INT
    WHILE EXISTS(SELECT 1 FROM BOM WHERE ID=@PID)
    BEGIN
        SET @ID=@PID
        SELECT @PID=PID FROM BOM WHERE ID=@ID
    END
    RETURN @PID
END
GOSELECT PID=DBO.F_GETROOT(PID),ID FROM BOM
GO/*
PID         ID
----------- -----------
801         101
801         102
801         103
801         601
801         101
801         105
801         501
801         106
801         121
*/
DROP FUNCTION F_GETROOT
DROP TABLE BOM
GO
--生成测试数据
create table BOM_1(Item int,bom_head varchar(20),bom_child varchar(20),number int,products_attribute  varchar(20))
insert into BOM_1 select 1 ,'A' ,'A1',1,'采购'
insert into BOM_1 select 2 ,'A' ,'A2',2,'生产'
insert into BOM_1 select 3 ,'A2','A3',3,'生产'
insert into BOM_1 select 4 ,'A2','A4',2,'采购'
insert into BOM_1 select 5 ,'A3','A5',2,'采购'
insert into BOM_1 select 6 ,'A3','A6',1,'采购'
insert into BOM_1 select 7 ,'B' ,'B1',1,'采购'
insert into BOM_1 select 8 ,'B' ,'B2',2,'生产'
insert into BOM_1 select 9 ,'B2','B3',3,'生产'
insert into BOM_1 select 10,'B2','B4',2,'采购'
insert into BOM_1 select 11,'B3','B5',2,'采购'
insert into BOM_1 select 12,'B3','B6',2,'采购'
go
   --创建用户定义函数，用于取每个父节点下子节点的采购配置信息
create function f_stock(@bom_head varchar(20))
returns @t table(bom varchar(20),number int)
as
begin
    declare @level int
    declare @a table(bom varchar(20),number int,products_attribute varchar(20),[level] int)
    set @level=1    if exists(select 1 from BOM_1 where bom_head=@bom_head)
    insert into @a
    select bom_child,number,products_attribute,@level
    from BOM_1
    where bom_head=@bom_head

    while exists(select 1 from @a where [level]=@level and products_attribute='生产')
    begin
        set @level=@level+1
        insert into @a(bom,number,products_attribute,[level])
        select a.bom_child,a.number,a.products_attribute,@level
        from BOM_1 a,@a b
        where a.bom_head=b.bom and b.[level]=@level-1
    end

    insert into @t(bom,number) select bom,number from @a where products_attribute='采购'
    return
end
go
--执行调用，取父节点'A'一个标准配置分解的采购信息及数量
select * from dbo.f_stock('A')
--生成测试数据
create table BOM(ID INT,PID INT,MSG VARCHAR(1000))
insert into BOM select 1,0,NULL
insert into BOM select 2,1,NULL
insert into BOM select 3,1,NULL
insert into BOM select 4,2,NULL
insert into BOM select 5,3,NULL
insert into BOM select 6,5,NULL
insert into BOM select 7,6,NULL
go--创建用户定义函数用于取每个父节点下子节点的采购配置信息
create function f_getChild(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)
as
begin
    declare @i int
    set @i = 1
    insert into @t select ID,PID,@i from BOM where PID = @ID

    while @@rowcount<>0
    begin
        set @i = @i + 1

        insert into @t
        select
            a.ID,a.PID,@i
        from
            BOM a,@t b
        where
            a.PID=b.ID and b.Level = @i-1
    end
    return
end
go--执行查询
select ID from dbo.f_getChild(3)
go--输出结果
/*
ID
----
5
6
7
*/--删除测试数据
drop function f_getChild
drop table BOM创建用户定义函数,每个子节点de父节点的信息
--生成测试数据
create table BOM(ID int,parentID int,sClassName varchar(10))
insert into BOM values(1,0,'1111'      )
insert into BOM values(2,1,'1111_1'    )
insert into BOM values(3,2,'1111-1-1'  )
insert into BOM values(4,3,'1111-1-1-1')
insert into BOM values(5,1,'1111-2'    )go--创建用户定义函数,每个子节点de父节点的信息
create function f_getParent(@ID int)
returns varchar(40)
as
begin
    declare @ret varchar(40)    while exists(select 1 from BOM where ID=@ID and parentID<>0)
    begin
        select @ID=b.ID,@ret=','+rtrim(b.ID)+isnull(@ret,'')
        from
            BOM a,BOM b
        where
            a.ID=@ID and b.ID=a.parentID
    end

    set @ret=stuff(@ret,1,1,'')
    return @ret
end
go--执行查询
select ID,isnull(dbo.f_getParent(ID),'') as parentID from BOM
go--输出结果
/*
ID          parentID
----------- ----------------------------------------
1
2           1
3           1,2
4           1,2,3
5           1
*/--删除测试数据
drop function f_getParent
drop table BOM
go

-- =========================================
-- -----------t_mac 小编-------------------
   --------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(node varchar(10),inherit_from  varchar(10))
go
insert tb SELECT
'A' ,'B' UNION ALL SELECT
'A' ,'C' UNION ALL SELECT
'B' ,'D' UNION ALL SELECT
'B' ,'E' UNION ALL SELECT
'C' ,'D' UNION ALL SELECT
'C' ,'F'
GO
DECLARE @NODE CHAR(1)
SET @NODE='a'
;WITH CTE AS (
SELECT inherit_from ,path=CAST (inherit_from  AS VARCHAR(8000)) FROM tb WHERE node=@NODE
UNION ALL
SELECT B.inherit_from,PATH=A.PATH+B.inherit_from
FROM CTE A JOIN tb B ON A.inherit_from=B.node
)
select * into # from CTE
declare @s varchar(1000)
SELECT @s=ISNULL(@s+',','')+inherit_from from #
select @s
go
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
B,C,D,F,D,E

检查表中的数据是否有循环编码：邹老大写的
CREATE PROC p_VerifyData
@TableName     sysname,   --要校验树形数据的表
@CodeField      sysname,  --编码字段名
@ParentCodeField sysname  --上级编码字段名
AS
SET NOCOUNT ON
--参数检查
IF ISNULL(OBJECTPROPERTY(OBJECT_ID(@TableName),N'IsUserTable'),0)=0
BEGIN
RAISERROR(N'"%s"不存在,或者不是用户表',1,16,@TableName)
RETURN
END
IF NOT EXISTS(SELECT * FROM SYSCOLUMNS WHERE ID=OBJECT_ID(@TableName) AND name=@CodeField)
BEGIN
RAISERROR(N'列"%s"在用户表"%s"中不存在',1,16,@CodeField,@TableName)
RETURN
END
IF NOT EXISTS(SELECT * FROM SYSCOLUMNS WHERE ID=OBJECT_ID(@TableName) AND name=@ParentCodeField)
BEGIN
RAISERROR(N'列"%s"在用户表"%s"中不存在',1,16,@ParentCodeField,@TableName)
RETURN
END
SELECT @TableName=QUOTENAME(@TableName),
@CodeField=QUOTENAME(@CodeField),
@ParentCodeField=QUOTENAME(@ParentCodeField)--数据检查
EXEC(N'
--检查导致循环的节点
DECLARE @Level int
SET @Level=1
SELECT ID,PID,Path=CAST(ID as varchar(8000)),Level=@Level
INTO # FROM(--列出所有父节点不是根节点的数据(使用子查询是防止编码列为IDENTITY列时,导致后面的插入处理出错)
SELECT ID=a.'+@CodeField+N',PID=a.'+@ParentCodeField+N'
FROM '+@TableName+N' a,'+@TableName+N' b
WHERE a.'+@ParentCodeField+N'=b.'+@CodeField+N'
AND b.'+@ParentCodeField+N' IS NOT NULL)a
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT # SELECT a.'+@CodeField+N',b.PID,
CAST(a.'+@CodeField+N' as varchar(8000))+''>''+b.Path,@Level
FROM '+@TableName+N' a,# b
WHERE a.'+@ParentCodeField+N'=b.ID
AND b.Level=@Level-1
AND b.ID<>b.PID
END--显示结果
SELECT '+@CodeField+N',Description=N''父节点无效''
FROM '+@TableName+N' a
WHERE '+@ParentCodeField+N' IS NOT NULL
AND NOT EXISTS(
SELECT * FROM '+@TableName+N'
WHERE '+@CodeField+N'=a.'+@ParentCodeField+N')
UNION ALL --显示产生循环的节点
SELECT ID,N''循环:''+Path+''>''+CAST(ID as varchar(8000))
FROM # WHERE ID=PID
')

-- =========================================
-- -----------t_mac 小编-------------------
   --------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(node varchar(10),inherit_from  varchar(10))
go
insert tb SELECT
'A' ,'B' UNION ALL SELECT
'A' ,'C' UNION ALL SELECT
'B' ,'D' UNION ALL SELECT
'B' ,'E' UNION ALL SELECT
'C' ,'D' UNION ALL SELECT
'C' ,'F'
GO
DECLARE @NODE CHAR(1)
SET @NODE='a'
;WITH CTE AS (
SELECT inherit_from ,path=CAST (inherit_from  AS VARCHAR(8000)) FROM tb WHERE node=@NODE
UNION ALL
SELECT B.inherit_from,PATH=A.PATH+B.inherit_from
FROM CTE A JOIN tb B ON A.inherit_from=B.node
)
select distinct inherit_from into #2 from CTEdeclare @s varchar(1000)
SELECT @s=ISNULL(@s+',','')+inherit_from from #2
select @s
go----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
B,C,D,E,F

第二题不难，用函数WHILE就行了，如果查出父级是NULL或者输入参就跳出循环

问题1：假设继承树中不存在循环(A的父级节点中不存在任何节点继承A),写一个语句找出A的所有父级节点
如以上范例数据库,找出A的所有父级结果为{A,B,C,D,E,F}或{B,C,D,E,F}. 期待高人出现, 顺便学习学习如何在一个sql语句中(不用函数,存储过程)实现递归检索, 最好是2005以下版本的实现.如果问题1得到解决了, 问题2在1的基础上稍做改动就能解决了.

老大能否解释一下F_GETROOT这个函数的中间结果？实在看不懂

问下 WITH CTE AS 这个是什么东西，我文档都查不到

SQL2005新增加的东西可以理解为临时表

用表表达式 (CTE) 可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集。
CTE 与派生表类似，具体表现在不存储为对象，并且只在查询期间有效。
与派生表的不同之处在于，CTE 可自引用，还可在同一查询中引用多次。
CTE可用于：
1.创建递归查询(我个人认为CTE最好用的地方)
2.在同一语句中多次引用生成的表
CTE优点:
使用 CTE 可以获得提高可读性和轻松维护复杂查询的优点。
查询可以分为单独块、简单块、逻辑生成块。之后，这些简单块可用于生成更复杂的临时 CTE，直到生成最终结果集。
CTE可使用的范围：
可以在用户定义的例程（如函数、存储过程、触发器或视图）中定义 CTE。
下面看一个简单的CTE例题：
把test表中salary最大的id记录保存在test_CTE中,再调用
复制代码代码如下:
with test_CTE(id,salary)
as
(
select id ,max(salary)
from test
group by id
)
select * from test_cte 由上面例题可以看出：
CTE 由表示 CTE 的表达式名称、可选列列表和定义 CET 的查询组成。
定义 CTE 后，可以在 SELECT、INSERT、UPDATE 或 DELETE 语句中对其进行引用，就像引用表或视图一样。
简单的说CTE可以替代临时表和表变量的功能。
我个人认为cte最好用的地方是创建递归查询，下面演示一下这功能：
现有一数据结构如下：这些数据存放在表Co_ItemNameSet中,表结构和部分数据如下：
ItemId ParentItemId ItemName
2 0 管理费用
3 0 销售费用
4 0 财务费用
5 0 生产成本
35 5 材料
36 5 人工
37 5 制造费用
38 35 原材料
39 35 主要材料
40 35 间辅材料
41 36 工资
42 36 福利
43 36 年奖金
现在需求是:我想查询ItemId=2,也就是管理费用和其下属所有节点的信息
通过CTE可以很简单达到需求要的数据
为了体现CTE的方便性，我特意也写了一个sql2000版本的解决方法，先看看sql2000是怎么解决这个问题的
复制代码代码如下:
--sql2000版本
DECLARE @i INT
SELECT @i=2;
/*
使用临时表作为堆栈来跟踪所有正在处理中的项目（已经开始但尚未结束）。
某个项目一旦处理完毕，将被从堆栈中删除。
当发现新的项目时，这些项目将被添加到堆栈中。
*/
CREATE TABLE #tem(
[ItemId] [INT] NOT NULL,
[level] INT
);
/*
存放结果
*/
CREATE TABLE #list(
[ItemId] [INT] NOT NULL,
[ParentItemId] [INT] NOT NULL DEFAULT ((0)),
[ItemName] [nvarchar](100) NOT NULL DEFAULT (''),
[level] INT
);
INSERT INTO #tem([ItemId],[level])
SELECT ItemId, 1
FROM Co_ItemNameSet
WHERE itemid=@i
INSERT INTO #list([ItemId],[ParentItemId],[ItemName],[level])
SELECT ItemId, ParentItemId, ItemName ,1
FROM Co_ItemNameSet
WHERE itemid=@i
DECLARE @level INT
SELECT @level=1
DECLARE @current INT
SELECT @current=0
/*
当 @level 大于 0 时，执行以下步骤：
1.如果当前级别 (@level) 的堆栈中有项目，就选择其中一个，并称之为 @current。
2.从堆栈中删除该项目以免重复处理它，然后将其所有子项目添加到堆栈的下一级 (@level + 1) 中。
3.如果有子项目 (IF @@ROWCOUNT > 0)，则下降一级处理它们 (@level = @level + 1)；否则，继续在当前级别上处理。
4.最后，如果在当前级别的堆栈中没有待处理的项目，则返回到上一级，看上一级是否有待处理的项目 (@level = @level - 1)。当再没有上一级时，则完毕。
*/
WHILE(@level>0)
BEGIN
SELECT @current=ItemId
FROM #tem
WHERE [level]=@level
IF @@ROWCOUNT>0
BEGIN
--从堆栈中删除该项目以免重复处理它
DELETE FROM #tem
WHERE [level]=@level and ItemId=@current
--将其所有子项目添加到堆栈的下一级 (@level + 1) 中。
INSERT INTO #tem([ItemId],[level])
SELECT [ItemId],@level+1
FROM Co_ItemNameSet
WHERE ParentItemId=@current
--将其所有子项目添加
INSERT INTO #list([ItemId],[ParentItemId],[ItemName],[level])
SELECT [ItemId],[ParentItemId],[ItemName] ,@level+1
FROM Co_ItemNameSet
WHERE ParentItemId=@current
IF @@rowcount>0
BEGIN
SELECT @level=@level+1
END
END
ELSE
BEGIN
SELECT @level=@level-1
END
END
--显示结果
SELECT * FROM #list
DROP TABLE #tem
DROP TABLE #list
go 结果如下：
ItemId ParentItemId ItemName level
2 0 管理费用 1
52 2 汽车费用 2
55 2 招聘费 2
56 2 排污费 2
53 52 燃料 3
54 52 轮胎 3
大家看到sql2000解决这个问题比较麻烦，要实现这需求编写的代码比较多，比较复杂
现在好了，在sql2005中通过CTE的递归特点可以2步就实现.
得到同样的结果，sql2005的CTE代码简单了许多.这就是CTE支持递归查询的魅力。
请看下面的代码：
复制代码代码如下:
--sql2005版本
DECLARE @i INT
SELECT @i=2;
WITH Co_ItemNameSet_CTE(ItemId, ParentItemId, ItemName,Level)
AS
(
SELECT ItemId, ParentItemId, ItemName ,1 AS [Level]
FROM Co_ItemNameSet
WHERE itemid=@i
UNION ALL
SELECT c.ItemId, c.ParentItemId, c.ItemName ,[Level] + 1
FROM Co_ItemNameSet c INNER JOIN Co_ItemNameSet_CTE ct
ON c.ParentItemId=ct.ItemId
)
SELECT * FROM Co_ItemNameSet_CTE
go 本文来自: 脚本之家(www.jb51.net) 详细出处参考：http://www.jb51.net/article/19236.htm

如果WITH也不能用第一题应该怎么做呢？
我认为可以用游标做，在表中加个标识列，然后用游标进行标识，最后根据标识列输出
第二题就像我刚才说的那样用WHILE就循环应该就可以了，也不记录中间纪录，正好符合要求。

大大，能不能解释一下
WITH CTE AS (
SELECT inherit_from ,path=CAST (inherit_from AS VARCHAR(max)) FROM tb WHERE node=@NODE
UNION ALL
SELECT B.inherit_from,PATH=A.PATH+B.inherit_from
FROM CTE A JOIN tb B ON A.inherit_from=B.node
)
里面两个字语句是怎么工作的？是不是先执行
SELECT inherit_from ,path=CAST (inherit_from AS VARCHAR(max)) FROM tb WHERE node=@NODE
把结果插入CET以后再执行
SELECT B.inherit_from,PATH=A.PATH+B.inherit_from
FROM CTE A JOIN tb B ON A.inherit_from=B.node
产生新的结果再次插入CET

CTE是变形的临时表，我想考官不会满意，他要的是算法能力。

IF OBJECT_ID('TB') IS NOT NULL
DROP TABLE TB
GO
CREATE TABLE TB(node varchar(10),inherit_from  varchar(10))
go
INSERT INTO TB SELECT
'A' ,'B' UNION ALL SELECT
'A' ,'C' UNION ALL SELECT
'B' ,'D' UNION ALL SELECT
'B' ,'E' UNION ALL SELECT
'C' ,'D' UNION ALL SELECT
'C' ,'F' GO;WITH CTE AS
(
   SELECT INHERIT_FROM FROM TB WHERE NODE = 'A'
   UNION ALL
   SELECT A.INHERIT_FROM FROM TB AS A,CTE AS B WHERE A.NODE = B.INHERIT_FROM
)
,CTE1 AS
(
   SELECT DISTINCT * FROM CTE
)
SELECT DISTINCT STUFF((SELECT ','+ INHERIT_FROM FROM CTE1 ORDER BY INHERIT_FROM FOR XML PATH('')),1,1,'')
FROM CTE1 A
--
INSERT INTO TB
SELECT 'F' ,'A'
IF EXISTS(SELECT 1 FROM TB WHERE inherit_from = 'A') AND EXISTS(SELECT 1 FROM TB WHERE NODE = 'A')
BEGIN
    DECLARE  @FLAG INT
    ;WITH CTE AS
(
   SELECT INHERIT_FROM FROM TB WHERE NODE = 'A'
   UNION ALL
   SELECT A.INHERIT_FROM FROM TB AS A,CTE AS B WHERE A.NODE = B.INHERIT_FROM AND A.inherit_from <> 'A'
)
    SELECT @FLAG = COUNT(*) FROM CTE A INNER JOIN (SELECT NODE FROM TB WHERE inherit_from = 'A')B ON A.INHERIT_FROM = B.NODE
    IF(@FLAG >0)
       PRINT '存在A死循环'
    ELSE
       PRINT '不存在A死循环'
END
ELSE
   PRINT '不存在A死循环'DROP TABLE TB

我很好奇这种BOM型的表不用中间表应该怎么做，谁能给演示一下？

凡BOM 必出。。小F的笔记

调试易

某公司变态面试题，召唤SQL达人。

解决方案 »