请大家指教。
有三个表,结构如下:
表t_a: a_id a_name
c1 aaa
c2 bbb
c3 ddd
表t_b: b_id b_hall b_fee
c1 h68 800
c2 h67 600
c2 h69 500
c2 h70 400
表t_c: c_id c_hall c_type c_fee
c1 hallpre 600
c1 h68 hall 100
c2 hallpre 300
c2 hallpre 200
c2 h69 hall 300
c2 hall 600
最后希望得到的结果是: id name b_money c_money
c1 aaa 800 700
c2 bbb 1500 1400
其中b_money是表t_b中b_fee的和,c_money是t_c中c_fee的和。c_hall可以为‘’(空),也可以与b_hall中相同。
即根据t_c中的id号分组,计算和。请教各位大牛这个语句怎么写呢?
有三个表,结构如下:
表t_a: a_id a_name
c1 aaa
c2 bbb
c3 ddd
表t_b: b_id b_hall b_fee
c1 h68 800
c2 h67 600
c2 h69 500
c2 h70 400
表t_c: c_id c_hall c_type c_fee
c1 hallpre 600
c1 h68 hall 100
c2 hallpre 300
c2 hallpre 200
c2 h69 hall 300
c2 hall 600
最后希望得到的结果是: id name b_money c_money
c1 aaa 800 700
c2 bbb 1500 1400
其中b_money是表t_b中b_fee的和,c_money是t_c中c_fee的和。c_hall可以为‘’(空),也可以与b_hall中相同。
即根据t_c中的id号分组,计算和。请教各位大牛这个语句怎么写呢?
select c_id as id,a_name as name,sum(b_fee),sum(c_money) from t_a,t_b,t_c where a_id=c_id and c_id=b_id and (c_hall=b_hall or c_hall='') and c_type like 'hall%' group by c_id,a_name order by c_id得出错误的结果,请问哪里错了呢?
select a_id id,a_name name,sum(b_fee)b_money,sum(c_fee)c_money from t_a a,t_b b,t_c c
where a.a_id=b.b_id and a.a_id=c.c_id
group by a_id,a_name
t_a t1,t_b t2,t_c t3
where
t1.a_id=t2.b_id and t2.b_id=t3.c_id
group by t3.c_id
insert into t_a values('c1', 'aaa')
insert into t_a values('c2', 'bbb')
insert into t_a values('c3', 'ddd')
create table t_b(b_id varchar(10), b_hall varchar(10), b_fee int)
insert into t_b values('c1', 'h68', 800)
insert into t_b values('c2', 'h67', 600)
insert into t_b values('c2', 'h69', 500)
insert into t_b values('c2', 'h70', 400)
create table t_c(c_id varchar(10), c_hall varchar(10), c_type varchar(10), c_fee int)
insert into t_c values('c1', '' , 'hallpre' , 600)
insert into t_c values('c1', 'h68' , 'hall' , 100)
insert into t_c values('c2', '' , 'hallpre' , 300)
insert into t_c values('c2', '' , 'hallpre' , 200)
insert into t_c values('c2', 'h69' , 'hall' , 300)
insert into t_c values('c2', '' , 'hall' , 600)
go--方法一
select t_a.* ,
b_money = isnull((select sum(b_fee) from t_b where b_id = t_a.a_id),0) ,
c_money = isnull((select sum(c_fee) from t_c where c_id = t_a.a_id),0)
from t_a
/*
a_id a_name b_money c_money
---------- ---------- ----------- -----------
c1 aaa 800 700
c2 bbb 1500 1400
c3 ddd 0 0(所影响的行数为 3 行)
*/--方法二
select t1.* , t2.b_money , t3.c_money from t_a t1,
(select b_id , b_money = sum(b_fee) from t_b group by b_id) t2,
(select c_id , c_money = sum(c_fee) from t_c group by c_id) t3
where t1.a_id = t2.b_id and t1.a_id = t3.c_id
/*
a_id a_name b_money c_money
---------- ---------- ----------- -----------
c1 aaa 800 700
c2 bbb 1500 1400(所影响的行数为 2 行)
*/drop table t_a,t_b,t_c