sql 试题 有点难

Question

考题一：有一个简单的树型结构表，只有二个字段：
id为主键
pid为上级节点的id请用尽量简单的SQL语句编写：根据一个变量，获取该节点的下级所有子节点（包括子节点的子节点，以此类推）-- 构造事例数据
declare @tb Table(id int,pid int)
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,2)
insert into @tb values(7,3)
insert into @tb values(8,3)
insert into @tb values(9,3)
insert into @tb values(10,4)
insert into @tb values(11,5)
insert into @tb values(12,6)
insert into @tb values(13,7)
insert into @tb values(14,8)-- 定义变量
declare @id int
set @id = 2-- 请在此写查询语句 返回@id节点下面所有子节点
-- 重要提示:注意该题目要求查询该节点下面N级子节点,并非1,2级.-- 语句结束考题二：游戏数据库有个userlog表，里面有3个主要字段，用来记录用户的登录和退出时间
userId int 用户ID
type nvarchar(10) 用户登入还是登出(值为login或logout)
times  datetime 操作时间因一些原因，该数据中有些异常数据，如只有login或只有logout，在统计中忽略该异常数据。请写SQL统计出：
用户ID，该用户平均每天在线小时数，该用户一共在线时间，最长不上线间隔天数-- 请在此写查询语句
-- 重要提示:该题目较复杂,可根据你的理解任意写.
-- 语句结束
考题三：
请优化下面的SQL语句，提高该语句的执行效率。-- 原语句
Select tb1.id,tb1.name,isnull((select name from tb5 where id=tb1.id),’’)
from tb1,tb2,tb3 
where tb1.id=tb2.pid and tb1.id=tb3.pid and tb1.id not in (select id from tb4)
group by tb1.id,tb1.name-- 优化后的语句
-- 重要提示:注意join语句的使用,注意exists和in的区别,尽量不使用子查询
-- 语句结束

Answer 1

考题一：
;with cte
as(
select *
from @tb
where id=2
union all
select a.*
from @tb a,cte b
where a.pid=b.id)
select * from cte

Answer 2

上面假定查询id=2的子节点，下面改用参数：
;with cte
as(
select *
from @tb
where id=@id
union all
select a.*
from @tb a,cte b
where a.pid=b.id)
select * from cte

Answer 3

第三题，主要注意两点：
一是尽量减少子查询；
二是多表连接，注意添加index（ctrl + L查看执行计划）
另外，exists和in在性能上差别不大，有同仁专门做过相关测试，具体参考某一精华帖。

Answer 4

-- 1:
;with tree as
(
select * from @tb where id=@id
union all
select a.* from @tb a join tree b on a.pid=b.id
)
select * from tree-- 3: tb2,tb3是废的,一点用都没有
select a.id, a.name, isnull(b.name,0) from tb1 a left join tb5 b on a.id=b.id
where not exists (select 1 from tb4 where id=a.id)
group by a.id, a.name
第二题确实很难处理

Answer 5

Select tb1.id,tb1.name,isnull((select name from tb5 where id=tb1.id),’’)
from tb1,tb2,tb3  
where tb1.id=tb2.pid and tb1.id=tb3.pid and tb1.id not in (select id from tb4)
group by tb1.id,tb1.name===
你这样的group by不报错？

Answer 6

为什么说这里的tb2,tb3是废的呢？帮我也扫盲下。
这里是内连接，如何是没用的呢，我也没看出来。

Answer 7

都不查它们的数据，和tb4/tb5又没有关系，为什么要连它们？

Answer 8

SELECT TB1.ID,TB1.NAME,ISNULL((SELECT TOP 1 NAME FROM TB5 WHERE ID=TB1.ID),’’) ---你确认这边只返回一条，否则会报错，保险起见加上TOP 1
FROM TB1 ,TB2,TB3  
WHERE TB1.ID=TB2.PID AND TB1.ID=TB3.PID 
AND NOT EXISTS(SELECT NULL FROM TB4 WHERE TB1.ID=ID)
GROUP BY TB1.ID,TB1.NAME
这里还要注意的是，坚持有没有table scan，有的话就加上index。

Answer 9

LZ贴点第二题的数据，SQL Center会帮你搞定的，我先去洗澡，回来我也学习学习。

Answer 10

/*
login之后多条logout，只取最先logout的记录
logout之后多条login, 只取最后login的记录
*/
;with t1 as
(
select id=row_number()over(partition by userId order by times),* from userlog where type='login'
),
t2 as
(
select id=row_number()over(partition by userId order by times),* from userlog where type='logout'
),
t3 as
(
select a.userId, a.times i, b.times o from t1 a, t2 b where a.userId=b.userId and a.times<b.times
and b.times  < isnull((select times from t1 where userId=a.userId and id=a.id+1),'9999')
),
t4 as -- 写到这里终于把有效的i/o对应起来
(
select * from t3 t where not exists (select 1 from t3 where userId=t.userId and i=t.i and o<t.o)
)
select userId, sum(datediff(hour,i,o)) from t4 group by userId --该用户一共在线时间
/*
回头一瞄：该用户平均每天在线小时数，最长不上线间隔天数该问题缺条件，没有时间范围，无法计算，难道从1753年计算到9999年？
*/