几个sql语句
几个sql语句

作者:boater 提交日期:2006-10-23 22:37:00
一、逐级向上汇总:
数据表tree,其中code字段表明了在树形结构中该结点的路径,leaf字段指出该结点是否叶子结点,acoumt指出了叶结点的数值。要求写一sql语句统计出各结点所下辖的叶子结点所有数值之和。
create table tree (code varchar(20),leaf int,amount int )
insert into tree values('1001',0,0)
insert into tree values('10011001',0,0)
insert into tree values('100110011001',1,50)
insert into tree values('1002',0,0)
insert into tree values('10021001',0,0)
insert into tree values('100210011001',1,50)
insert into tree values('1003',0,0)
insert into tree values('10031001',0,0)
insert into tree values('10011002',0,0)
insert into tree values('100110021001',1,25)
select * from tree order by code

sql语句如下:
select T.code,amount=(select sum(amount) from tree where code like T.code+'%' and leaf=1)
from tree T.

二、
表a如下:
id . .name . . ...province
1. . . . bear.. . shang xi
2. . . . smith. . hu bei
3. . . . tom. . . bei jing
4. . . . fox. . . hong kong
5. . . . mike. . shang hai
6. . . . jack. . xin jiang
表b是对表a按id值进行的分组:
group2 id1. id2.. id3 . id4
1001. ..2. .4. .. 5.. NULL
1002. ..1. .NULL..NULL NULL
1003. ..3.. 6. .. NULL.NULL

编写一sql使其输出以下内容,其中每一行左侧系group2的值,右侧系对应id的name,province对,各对间以'/'号分隔。
1001 smith hu bei/fox hong kong/mike shang hai
1002 bear shang xi
1003 tom bei jing/jack xin jiang

答案:
select * from a
select * from b
select b.group2,
isnull(a1.name+a1.province+'/','')+
isnull(a2.name+a2.province+'/','')+
isnull(a3.name+a3.province+'/','')+
isnull(a4.name+a4.province,'') as nameAndProvince
from b
left outer join a a1 on b.id1=a1.id
left outer join a a2 on b.id2=a2.id
left outer join a a3 on b.id3=a3.id
left outer join a a4 on b.id4=a4.id

#日志日期:2006-10-23 星期一(Monday) 晴 送小红花 推荐指数:复制链接 举报
天涯“2016年度十大最具影响力博客”评选


登录 | 新人注册>>
输入您的评论:(不支持HTML标签)


验证码
本文所属博客:IT,软件
引用地址:
© 天涯社区