这面试题难吗？？？

--测试数据   深度排序
  DECLARE   @t   TABLE(ID   char(3),PID   char(3),Name   nvarchar(10))
  INSERT   @t   SELECT   '001',NULL   ,'山东省'
  UNION   ALL   SELECT   '002','001','烟台市'
  UNION   ALL   SELECT   '004','002','招远市'
  UNION   ALL   SELECT   '003','001','青岛市'
  UNION   ALL   SELECT   '005',NULL   ,'四会市'
  UNION   ALL   SELECT   '006','005','清远市'
  UNION   ALL   SELECT   '007','006','小分市'

  --深度排序显示处理
  --生成每个节点的编码累计(相同当单编号法的编码)
  DECLARE   @t_Level   TABLE(ID   char(3),Level   int,Sort   varchar(8000))
  DECLARE   @Level   int
  SET   @Level=0
  INSERT   @t_Level   SELECT   ID,@Level,ID
  FROM   @t
  WHERE   PID   IS   NULL
  WHILE   @@ROWCOUNT>0
  BEGIN
  SET   @Level=@Level+1
  INSERT   @t_Level   SELECT   a.ID,@Level,b.Sort+a.ID
  FROM   @t   a,@t_Level   b
  WHERE   a.PID=b.ID
  AND   b.Level=@Level-1
  END

  --显示结果
  SELECT   a.*
  FROM   @t   a,@t_Level   b
  WHERE   a.ID=b.ID
  ORDER   BY   b.Sort
  /*--结果
  ID       PID       Name
  ------   ---------   ----------
  001     NULL   山东省
  002     001       烟台市
  004     002       招远市
  003     001       青岛市
  005     NULL   四会市
  006     005       清远市
  007     006       小分市
  --*/--查询指定节点及其所有子节点的函数
  CREATE   FUNCTION   f_Cid(@ID   char(3))
  RETURNS   @t_Level   TABLE(ID   char(3),Level   int)
  AS
  BEGIN
  DECLARE   @Level   int
  SET   @Level=1
  INSERT   @t_Level   SELECT   @ID,@Level
  WHILE   @@ROWCOUNT>0
  BEGIN
  SET   @Level=@Level+1
  INSERT   @t_Level   SELECT   a.ID,@Level
  FROM   tb   a,@t_Level   b
  WHERE   a.PID=b.ID
  AND   b.Level=@Level-1
  END
  RETURN
  END
  GO

  --调用函数查询002及其所有子节点
  SELECT   a.*
  FROM   tb   a,f_Cid('002')   b
  WHERE   a.ID=b.ID
  /*--结果
  ID       PID     Name
  ------   -------   ----------
  002     001     烟台市
  004     002     招远市
  --*/ --测试数据
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
    SET @Level=@Level+1
    INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
    FROM @t a,@t_Level b
    WHERE a.PID=b.ID
        AND b.Level=@Level-1
END--显示结果
SELECT SPACE(b.Level*2)+'|--'+a.Name
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
|--山东省
  |--烟台市
    |--招远市
  |--青岛市
|--四会市
  |--清远市
    |--小分市
--*/
--树型结构处理之双编号（广度深度排序）
if OBJECTPROPERTY(object_id('tb'),'isusertable')<>0
  drop table tb
create table tb(ybh nvarchar(10),ebh nvarchar(10),beizhu nvarchar(1000))
insert tb
select '0001',null,'云南省'
union all select '0002','0001','昆明市'
union all select '0003','0001','昭通市'
union all select '0009','0001','大理市'
union all select '0008',null,'四川省'
union all select '0004',null,'贵州省'
union all select '0005','0002','五华区'
union all select '0007','0002','水富县'
union all select '0006','0005','西园路192号'
union all select '0010','0006','金色梧桐3-702'
union all select '0011','0010','昆明越科时空科技有限公司'
union all select '0015','0007','两碗乡'
union all select '0013','0015','两碗村'
union all select '0012','0013','某跨国集团董事长'
union all select '0014','0008','成都市'
  --深度排序（模拟单编码法)
   declare @level_tt table(ybh nvarchar(1000),ebh nvarchar(1000),level int)
  declare @level int
  set @level=0
  insert @level_tt(ybh,ebh,level)
  select ybh,ybh,@level from tb where ebh is null
  while @@ROWCOUNT>0
  begin
          set @level=@level+1
          insert @level_tt(ybh,ebh,level)
          select a.ybh,b.ebh+a.ybh,@level
            from tb a,@level_tt b
            where a.ebh=b.ybh and b.level=@level-1
end
select space(b.level*2)+'----'+a.beizhu,a.*,b.*
  from tb a,@level_tt b
  where a.ybh=b.ybh
  order by b.ebh
/*
(结果)    ybh    ebh    beizhu    ybh    ebh    level
----云南省    0001    NULL    云南省    0001    0001    0
  ----昆明市    0002    0001    昆明市    0002    00010002    1
    ----五华区    0005    0002    五华区    0005    000100020005    2
      ----西园路192号    0006    0005    西园路192号    0006    0001000200050006    3
        ----金色梧桐3-702    0010    0006    金色梧桐3-702    0010    00010002000500060010    4
          ----昆明越科时空科技有限公司    0011    0010    昆明越科时空科技有限公司    0011    000100020005000600100011    5
    ----水富县    0007    0002    水富县    0007    000100020007    2
      ----两碗乡    0015    0007    两碗乡    0015    0001000200070015    3
        ----两碗村    0013    0015    两碗村    0013    00010002000700150013    4
          ----某跨国集团董事长    0012    0013    某跨国集团董事长    0012    000100020007001500130012    5
  ----昭通市    0003    0001    昭通市    0003    00010003    1
  ----大理市    0009    0001    大理市    0009    00010009    1
----贵州省    0004    NULL    贵州省    0004    0004    0
----四川省    0008    NULL    四川省    0008    0008    0
  ----成都市    0014    0008    成都市    0014    00080014    1
  */

  --查找子节点（包括本身节点和子节点)
declare @level_tt table(ybh nvarchar(1000),ebh nvarchar(1000),level int)
  declare @level int
  set @level=0
  insert @level_tt(ybh,ebh,level)
  select ybh,ybh,@level from tb where ybh='0005'
  while @@ROWCOUNT>0
  begin
          set @level=@level+1
          insert @level_tt(ybh,ebh,level)
          select a.ybh,b.ebh+a.ybh,@level
            from tb a,@level_tt b
            where a.ebh=b.ybh and b.level=@level-1
end
select space(b.level*2)+'----'+a.beizhu,a.*,b.*
  from tb a,@level_tt b
  where a.ybh=b.ybh
  order by b.ebh
/*
(无列名)    ybh    ebh    beizhu    ybh    ebh    level
----五华区    0005    0002    五华区    0005    0005    0
  ----西园路192号    0006    0005    西园路192号    0006    00050006    1
    ----金色梧桐3-702    0010    0006    金色梧桐3-702    0010    000500060010    2
      ----昆明越科时空科技有限公司    0011    0010    昆明越科时空科技有限公司    0011    0005000600100011    3
*/

/*
标题：查询各节点的父路径函数
作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-05-12
地点：广东深圳
*//*
原始数据及要求结果如下：
--食品
  --水果
    --香蕉
    --苹果
  --蔬菜
    --青菜
id          pid         name
----------- ----------- --------------------
1           0           食品
2           1           水果
3           1           蔬菜
4           2           香蕉
5           2           苹果
6           3           青菜要求得到各节点的父路径即如下结果：
id  pid name  路径
--- --- ----- ---------------
1   0   食品  食品
2   1   水果  食品,水果
3   1   蔬菜  食品,蔬菜
4   2   香蕉  食品,水果,香蕉
5   2   苹果  食品,水果,苹果
6   3   青菜  食品,蔬菜,青菜
*/create table tb (id int , pid int , name nvarchar(20))
insert into tb values(1 , 0 , '食品')
insert into tb values(2 , 1 , '水果')
insert into tb values(3 , 1 , '蔬菜')
insert into tb values(4 , 2 , '香蕉')
insert into tb values(5 , 2 , '苹果')
insert into tb values(6 , 3 , '青菜')
go--查询各节点的父路径函数
create function f_pid(@id int) returns varchar(100)
as
begin
  declare @re_str as varchar(100)
  set @re_str = ''
  select @re_str = name from tb where id = @id
  while exists (select 1 from tb where id = @id and pid <> 0)
    begin
      select @id = b.id , @re_str = b.name + ',' + @re_str from tb a , tb b where a.id = @id and a.pid = b.id
    end
  return @re_str
end
goselect * , dbo.f_pid(id) 路径 from tb order by iddrop table tb
drop function f_pid

面试asp.net程序员
在上海个人感觉这个题目有点难，不知道大家感觉怎么样。

net程序员都出这sql题，待遇如何？

JUST TRY~~~~~~CREATE TABLE tb_user(id INT IDENTITY(1,1) PRIMARY KEY, username VARCHAR(50), parentid INT)
CREATE TABLE tb_grade(id INT IDENTITY(1,1) PRIMARY KEY, uerid int, op char(1), point int)
GoINSERT tb_user SELECT '用户1',0 UNION ALL
SELECT '用户2',1 UNION ALL
SELECT '用户3',2 UNION ALL
SELECT '用户4',3 UNION ALL
SELECT '用户5',1
Go
-->SELECT * FROM tb_user
/**************************************************************************
id          username                                           parentid
----------- -------------------------------------------------- -----------
1           用户1                                                0
2           用户2                                                1
3           用户3                                                2
4           用户4                                                3
5           用户5                                                1
**************************************************************************/INSERT tb_grade SELECT 1,'A',100
UNION ALL SELECT 2,'C',10
UNION ALL SELECT 5,'A',5010
UNION ALL SELECT 3,'C',30
UNION ALL SELECT 3,'A',50
UNION ALL SELECT 4,'C',201
UNION ALL SELECT 1,'A',50
UNION ALL SELECT 5,'C',60
UNION ALL SELECT 1,'A',40
UNION ALL SELECT 2,'A',111
UNION ALL SELECT 1,'C',50
Go-->SELECT * FROM tb_grade
/****************************************
id          uerid       op   point
----------- ----------- ---- -----------
1           1           A    100
2           2           C    10
3           5           A    5010
4           3           C    30
5           3           A    50
6           4           C    201
7           1           A    50
8           5           C    60
9           1           A    40
10          2           A    111
11          1           C    50
****************************************/-->2.1
ALTER FUNCTION fun_addchar (@lvl INT)
RETURNS varchar(100)
AS
BEGIN
DECLARE @pref as varchar(100)
SET @pref=''
WHILE(@lvl>0)
begin
SET @pref=@pref+'--------'
SET @lvl=@lvl-1
END
RETURN @pref
ENDWITH T(id,username,sp) AS
(SELECT id,username, 1 AS sp FROM tb_user WHERE parentid=0
UNION ALL
SELECT tb_user.id,tb_user.username,T.sp+1 AS sp FROM tb_user, T WHERE tb_user.parentid=T.id)
SELECT dbo.fun_addchar(sp)+username AS user_structure FROM T
/*****************************************
user_structure
-----------------------------------------
--------用户1
----------------用户2
----------------用户5
------------------------用户3
--------------------------------用户
*****************************************/-->2.2
SELECT tb_user.username,(select SUM(ISNULL(CASE WHEN op='A' THEN point ELSE 0-point END,0)) FROM tb_grade WHERE uerid=tb_user.id) AS total_grade  from tb_user
/****************************************************************
username                                           total_grade
-------------------------------------------------- -----------
用户1                                                140
用户2                                                101
用户3                                                20
用户4                                                -201
用户5                                                4950
****************************************************************/

第一个，画的不大好看，凑合着用吧
IF OBJECT_ID('USERS') IS NOT NULL DROP TABLE USERS
IF OBJECT_ID('SCORES') IS NOT NULL DROP TABLE SCORES
GO
CREATE TABLE USERS(
UID INT
,USERNAME VARCHAR(50)
,PARENTID INT
)
INSERT INTO USERS
SELECT 1,'用户A',0 UNION ALL
SELECT 2,'用户B',4 UNION ALL
SELECT 3,'用户C',1 UNION ALL
SELECT 4,'用户D',1 UNION ALL
SELECT 5,'用户E',0 UNION ALL
SELECT 6,'用户F',5 UNION ALL
SELECT 7,'用户G',3 UNION ALL
SELECT 8,'用户H',3
;WITH MU AS (
SELECT CAST('|----' AS VARCHAR(8000)) 'ID0'
,CAST(RIGHT('000'+CAST(ROW_NUMBER() OVER (ORDER BY UID) AS VARCHAR(10)) ,3) AS VARCHAR(8000))'ID',UID,USERNAME
FROM USERS WHERE PARENTID=0
UNION ALL
SELECT SPACE(LEN(ID0))+'|----' 'ID0'
,MU.ID+RIGHT('000'+CAST(ROW_NUMBER() OVER (ORDER BY USERS.UID) AS VARCHAR(10)),3) 'ID'
,USERS.UID,USERS.USERNAME
FROM MU
INNER JOIN USERS ON MU.UID=USERS.PARENTID
)
SELECT ID0+USERNAME FROM MU
ORDER BY ID
/*
|----用户A
     |----用户C
          |----用户G
          |----用户H
     |----用户D
          |----用户B
|----用户E
     |----用户F
*/

两个问题一块发，希望你能应聘成功IF OBJECT_ID('USERS') IS NOT NULL DROP TABLE USERS
IF OBJECT_ID('SCORES') IS NOT NULL DROP TABLE SCORES
GO
CREATE TABLE USERS(
UID INT
,USERNAME VARCHAR(50)
,PARENTID INT
)
INSERT INTO USERS
SELECT 1,'用户A',0 UNION ALL
SELECT 2,'用户B',4 UNION ALL
SELECT 3,'用户C',1 UNION ALL
SELECT 4,'用户D',1 UNION ALL
SELECT 5,'用户E',0 UNION ALL
SELECT 6,'用户F',5 UNION ALL
SELECT 7,'用户G',3 UNION ALL
SELECT 8,'用户H',3
;WITH MU AS (
SELECT CAST('|----' AS VARCHAR(8000)) 'ID0'
,CAST(RIGHT('000'+CAST(ROW_NUMBER() OVER (ORDER BY UID) AS VARCHAR(10)) ,3) AS VARCHAR(8000))'ID',UID,USERNAME
FROM USERS WHERE PARENTID=0
UNION ALL
SELECT SPACE(LEN(ID0))+'|----' 'ID0'
,MU.ID+RIGHT('000'+CAST(ROW_NUMBER() OVER (ORDER BY USERS.UID) AS VARCHAR(10)),3) 'ID'
,USERS.UID,USERS.USERNAME
FROM MU
INNER JOIN USERS ON MU.UID=USERS.PARENTID
)
SELECT ID0+USERNAME FROM MU
ORDER BY ID
/*
|----用户A
     |----用户C
          |----用户G
          |----用户H
     |----用户D
          |----用户B
|----用户E
     |----用户F
*/CREATE TABLE SCORES(
ID INT
,UID INT
,OP CHAR(1)
,POINT INT
)
INSERT INTO SCORES
SELECT 1,1,'A',10 UNION ALL
SELECT 1,1,'C',5 UNION ALL
SELECT 1,1,'C',100 UNION ALL
SELECT 1,3,'A',20 UNION ALL
SELECT 1,4,'C',13 SELECT
USERS.USERNAME,SUM(CASE OP WHEN 'A' THEN POINT ELSE 0-POINT END)
FROM SCORES
INNER JOIN USERS ON SCORES.UID=USERS.UID
GROUP BY USERS.USERNAME
/*
USERNAME
-------------------------------------------------- -----------
用户A                                                -95
用户C                                                20
用户D                                                -13
*/

调试易

这面试题难吗？？？

解决方案 »