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
;
表 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
;