17,377
社区成员
发帖
与我相关
我的任务
分享
select sys_guid() as "key",
ent.entrust_name,
ent.bill_no,
ent.goods_type_name_cn,
ent.describe_goods,
ent.load_port,
ent.destination_port,
bk.zx_vessel_name,
bk.zx_voyage,
bk.zx_voyage_id,
bk.zx_vessel_id,
harbor.eta_date,
ent.total_weight,
ent.booking_req,
'' as "linkman",
'' as "remark",
(count(case cntr.cntr_type_code
when '20GP' then
cntr.cntr_type_code
end)) as "20GP",
(count(case cntr.cntr_type_code
when '20RF' then
cntr.cntr_type_code
end)) as "20RF",
(count(case cntr.cntr_type_code
when '20OT' then
cntr.cntr_type_code
end)) as "20OT",
(count(case cntr.cntr_type_code
when '40GP' then
cntr.cntr_type_code
end)) as "40GP",
(count(case cntr.cntr_type_code
when '40HC' then
cntr.cntr_type_code
end)) as "40HC",
(count(case cntr.cntr_type_code
when '40HR' then
cntr.cntr_type_code
end)) as "40HR"
from t_shp_entrust_cntr cntr
inner join t_shp_entrust ent on cntr.entrust_id = ent.entrust_id
inner join t_shp_booking bk on bk.entrust_id = ent.entrust_id
inner join (select t.eta_date, t.voy_id
from t_shp_voyage_harbor t
where t.port_code = 'CNDLC'
order by t.harbor_order) harbor on harbor.voy_id =
bk.zx_voyage_id
where cntr.entrust_id in ('16A36A4F317046029EFB61A892E21757',
'9F307E7AC4044F669F79C86BB4613B66'
)
group by cntr.cntr_type_code,
cntr.entrust_id,
ent.entrust_name,
ent.bill_no,
ent.goods_type_name_cn,
ent.describe_goods,
ent.load_port,
ent.destination_port,
bk.zx_vessel_name,
bk.zx_voyage,
bk.zx_voyage_id,
bk.zx_vessel_id,
harbor.eta_date,
ent.total_weight,
ent.booking_req
order by bk.zx_vessel_name
根据 相同的 船名航次 汇总出 20GP 20RF 20OT 40GP 40HC 40HR
列的合计
求大神 帮帮忙
订舱代理 ZX_VOYAGE_ID ENTRUST_ID 提单号 装货港 20GP 20RF 20OT 40GP 40HC 40HR 品名 货物描述 目的港 支线船名航次 ETA_DATE 毛重 联系人 订舱要求 备注
地中海航运有限公司 832F53E35A8B4B1CBDF14D412B566E9D 4E9B22D76DDD4E1681568EC5AAFBB7C1 177QJFJFD82086T QINHUANGDAO 1 0 0 0 0 0 镀膜深灰玻 ARICA HONG TAI 28 0870 2018/5/14 8:00 26000
秦皇岛集港船舶代理有限公司 832F53E35A8B4B1CBDF14D412B566E9D 52DF79119ACB46CD89CC24941D015FF0 578116319 QINHUANGDAO 4 0 0 0 0 0 浮法深灰玻 GUAYAQUIL HONG TAI 28 0870 2018/5/14 8:00 106000
total 5
法国达飞轮船(中国)有限公司 DFE8DD387FF9432197A45CA8D6BA998E 53C6F807F17A416A8F698B6B1DA48279 XDQV900453 QINHUANGDAO 6 0 0 0 0 0 硫酸铝 CAT LAI NAN HUI 2 0790 2018/4/17 8:00 162648
total 6
以上是想要的结果
t_shp_voyage_harbor 表的数据
eta_date voy_id harbor_order port_code
2018/4/17 8:00:00 DFE8DD387FF9432197A45CA8D6BA998E 1 CNDLC
2018/4/18 18:00:00 DFE8DD387FF9432197A45CA8D6BA998E 2 CNSHP
2018/4/20 10:00:00 DFE8DD387FF9432197A45CA8D6BA998E 3 CNDLC
2018/5/14 8:00:00 832F53E35A8B4B1CBDF14D412B566E9D 1 CNDLC
2018/5/15 18:00:00 832F53E35A8B4B1CBDF14D412B566E9D 2 CNSHP
2018/5/16 10:00:00 832F53E35A8B4B1CBDF14D412B566E9D 3 CNDLC
t_shp_entrust 表的数据
ENTRUST_ID BILL_NO LOAD_PORT ENTRUST_NAME GOODS_TYPE_NAME_CN DESCRIBE_GOODS DESTINATION_PORT TOTAL_WEIGHT BOOKING_REQ
4E9B22D76DDD4E1681568EC5AAFBB7C1 177QJFJFD82086T QINHUANGDAO 地中海航运有限公司 镀膜深灰玻 "DARK GREY REFLECTIVE GLASS
" ARICA 26000
53C6F807F17A416A8F698B6B1DA48279 XDQV900453 QINHUANGDAO 法国达飞轮船(中国)有限公司 硫酸铝 ALUMINIUM SULPHATE CAT LAI 162648
t_shp_booking 表的数据
ENTRUST_ID ZX_VESSEL_NAME ZX_VOYAGE ZX_VOYAGE_ID BOOKING_ID
4E9B22D76DDD4E1681568EC5AAFBB7C1 HONG TAI 28 870 832F53E35A8B4B1CBDF14D412B566E9D B476172B7EA2459F8AF0E1259192A671
53C6F807F17A416A8F698B6B1DA48279 NAN HUI 2 790 DFE8DD387FF9432197A45CA8D6BA998E FD3D9762F871409B943D71FD7C6BE791
52DF79119ACB46CD89CC24941D015FF0 HONG TAI 28 0870 832F53E35A8B4B1CBDF14D412B566E9D 8DB4E0CE819E4E23866753AB50474580
t_shp_entrust_cntr表的数据
ENTRUST_ID CNTR_SIZE CNTR_TYPE
4E9B22D76DDD4E1681568EC5AAFBB7C1 20 GP
53C6F807F17A416A8F698B6B1DA48279 20 GP
53C6F807F17A416A8F698B6B1DA48279 20 GP
53C6F807F17A416A8F698B6B1DA48279 20 GP
53C6F807F17A416A8F698B6B1DA48279 20 GP
53C6F807F17A416A8F698B6B1DA48279 20 GP
53C6F807F17A416A8F698B6B1DA48279 20 GP
52DF79119ACB46CD89CC24941D015FF0 20 GP
52DF79119ACB46CD89CC24941D015FF0 20 GP
52DF79119ACB46CD89CC24941D015FF0 20 GP
52DF79119ACB46CD89CC24941D015FF0 20 GP