一天几道SQL题（二）

1,现有表bill和表payment结构如下，两表通过pay_id关联：
bill表――bill_id是帐单标识，charge为帐单金额，pay_id为付款编号，PK：bill_id
bill_id INTEGER
charge INTEGER
pay_id INTEGER

payment表――pay_id为付款编号，charge为付款金额，PK：pay_id
pay_id INTEGER
charge INTEGER

要求更新payment使charge=bill表中同一pay_id的charge之和。

update payment 
set charge=(
select sum(charge) from bill 
group by pay_id
having payment.pay_id=bill.pay_id
)

 

2、表A定义如下：
属性 类型
Year Integer
Quarter Varchar（30）
Amount float

Year Quarter Amount
2000 1 1.1
2000 2 1.2
2000 3 1.3
2000 4 1.4
2001 1 2.1
2001 2 2.2
2001 3 2.3
2001 4 2.4
其中每行表表示一个季度的数据。

如果处理表A中的数据，得到如下的结果。
Year Quarter1 Quarter2 Quarter3 Quarter4
2000 1.1 1.2 1.3 1.4
2001 2.1 2.2 2.3 2.4
请用SQL写一段代码实现。

select a1.year,a1.amount as quater1,a2.amount as quater2,a3.amount as quater3,a4.amount as quater4
from A a1,A a2,A a3,A a4
where a1.quater='1' and a2.quater='2' and a3.quater='3' and a4.quater='4'
and a1.year=a2.year and a2.year=a3.year and a3.year=a4.year

3、有如下信息：
起始地 目的地 距离（公里）
A B 1000
A C 1100
A D 900
A E 400
B D 300
D F 600
E A 400
F G 1000
C B 600
请用SQL语句或一段代码写出从A出发，可达到的目的地（包括间接方式）。

select distinct b1.target from B b1 where exists
(
select distinct b2.target
from B b2
where b2.source='A' and b1.source=b2.target or exists
(
select distinct b3.target
from B b3
where b3.source='A' and b2.source=b3.target and b1.source=b2.target
)
)
or b1.source='A'

第二题的另一种解法：

select year,sum(case quater when '1' then amount else '0' end) as QUATER1,
sum(case quater when '2' then amount else '0' end) QUATER2,
sum(case quater when '3' then amount else '0' end) QUATER3,
sum(case quater when '4' then amount else '0' end) QUATER4
from A
group by year