Mysql左连接/右连接/全连接查询

喜旎果

Mysql左连接/右连接查询
表 turck --车辆信息:id,ch...
表 fee    --费用信息:summary,type...
-----------------------------------------------------------------------------------------------------------------------
查询出:每辆车应收实收费用
select a.ch ,b.ys,b.sh from
truck a,
(
select t1.ys,t2.sh,t2.truck_id from
(select truck_id ,sum(summary) ys from fee where type = '付' group by truck_id)
as t1
right outer join
(select truck_id ,sum(summary) sh from fee where type = '收' group by truck_id)
as t2
on (t1.truck_id = t2.truck_id)
) b
where a.id = b.truck_id
;
本查询,只检索交过费用,可不一定挂过帐的信息
ch        ys       sh
A35251 2200   2440
A36365 24944 27422
A36063 33001 32501
A36053 22001 22001
A41845 2392   2392
A41708 Null       2322
A40163 Null       2240
A40866 4020   2 720
A42219 Null      2020
A29353 2044  2545
A28510 Null      2244
A28616 Null      20422
A27346 Null     430
A28948 Null     2222
A16071 Null     3020
A58745 Null     25004
A58396 2200 2324
A20897 Null     2202
A67889 4042 1011

--------------------------------------------------------------------------------------------------------------------------------
查询出:每辆车应收实收费用;
车辆,已挂过帐,不一定是已收费
select a.ch ,b.ys,b.sh from
truck a,
(
select t1.ys,t2.sh,t2.truck_id from
(select truck_id ,sum(summary) ys from fee where type = '付' group by truck_id)
as t1
left outer join
(select truck_id ,sum(summary) sh from fee where type = '收' group by truck_id)
as t2
on (t1.truck_id = t2.truck_id)
) b
where a.id = b.truck_id
;
-----------------------------------------------------------------------------------------------------------------------------
查询出:每辆车应收实收费用;
车辆,已挂过帐,并且是已收费

select t3.ch,t1.ys,t2.ss from
(select truck_id,sum(summary) ys from fee where type = '付' group by truck_id) as t1,
(select truck_id,sum(summary) ss from fee where type = '收' group by truck_id) as t2,
truck t3
where t1.truck_id = t2.truck_id and t1.truck_id = t3.id
;