sql 试题 有点难

IT_2007 2010-10-12 09:11:07
考题一:

有一个简单的树型结构表,只有二个字段:
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的区别,尽量不使用子查询


-- 语句结束
...全文
122 点赞 收藏 19
写回复
19 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
SQLCenter 2010-10-12
/*
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年?
*/
回复
IT_2007 2010-10-12
太感谢你们,学习了
回复
zsh0809 2010-10-12
LZ贴点第二题的数据,SQL Center会帮你搞定的,我先去洗澡,回来我也学习学习。
回复
SQLCenter 2010-10-12
[Quote=引用 14 楼 zsh0809 的回复:]
不是需要过滤TB1的数据么?
[/Quote]
有道理,短路了哈。
回复
zsh0809 2010-10-12
[Quote=引用 12 楼 sqlcenter 的回复:]
引用 11 楼 zsh0809 的回复:

引用 7 楼 sqlcenter 的回复:
-- 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……
[/Quote]
不是需要过滤TB1的数据么?
回复
zsh0809 2010-10-12
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。
回复
SQLCenter 2010-10-12
[Quote=引用 11 楼 zsh0809 的回复:]

引用 7 楼 sqlcenter 的回复:
-- 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.nam……
[/Quote]

都不查它们的数据,和tb4/tb5又没有关系,为什么要连它们?
回复
zsh0809 2010-10-12
[Quote=引用 7 楼 sqlcenter 的回复:]
-- 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
[/Quote]
为什么说这里的tb2,tb3是废的呢?帮我也扫盲下。
这里是内连接,如何是没用的呢,我也没看出来。
回复
SQLCenter 2010-10-12
distinct
回复
SQLCenter 2010-10-12
[Quote=引用 8 楼 zsh0809 的回复:]

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
……
[/Quote]

报错,我的也报。
回复
zsh0809 2010-10-12
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不报错?
回复
SQLCenter 2010-10-12
-- 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


第二题确实很难处理
回复
IT_2007 2010-10-12
就是不知道怎么整了,我感觉有点繁琐[Quote=引用 5 楼 zsh0809 的回复:]
第三题,主要注意两点:
一是尽量减少子查询;
二是多表连接,注意添加index(ctrl + L查看执行计划)
另外,exists和in在性能上差别不大,有同仁专门做过相关测试,具体参考某一精华帖。
[/Quote]
回复
zsh0809 2010-10-12
第三题,主要注意两点:
一是尽量减少子查询;
二是多表连接,注意添加index(ctrl + L查看执行计划)
另外,exists和in在性能上差别不大,有同仁专门做过相关测试,具体参考某一精华帖。
回复
chen8410 2010-10-12
上面假定查询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
回复
chen8410 2010-10-12
考题一:
;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
回复
zsh0809 2010-10-12
第一题,使用递归就可以了,具体的LZ可以搜索一下相关用法,论坛里多呢
回复
SQLCenter 2010-10-12
不难 但...

楼下意见如何
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-10-12 09:11
社区公告
暂无公告