select a.ship_cor_code as 船公司,a.mode_code as 方式1, count(1) as 总数,
count(case when b.ctn_no is not null then 1 end) as 已进来,
count(case when b.ctn_no is null then 1 end) as 未进来
from in_ifcsum_empty a left join ctn_in_out b on a.ctn_no=b.ctn_no
where a.input_time>='2008-05-01 14:41:54' and a.input_time<='2008-05-09 14:41:54'
and a.ship_cor_code='APL' and
a.mode_code in ('SJ','TK','TS') group by a.ship_cor_code,a.mode_code
order by a.ship_cor_code 在ctn_in_out这个表中有重复的ctn_no 我只想要ctn_in_out.in_date>='2008-05-01 14:41:54'
如果把这个判断条件放在where 那就只能查出所有ctn_in_out.in_date>='2008-05-01 14:41:54'
那些没有记录的我就查不出来了,也就是说我的 “未进来”没有用了都是“已进来”的了如果我加在
count(case when b.ctn_no is not null and b.in_date>='2008-05-05 14:41:54' then 1 end) as 已进来,
那么 "总数"还是有问题 因为总数还是包含ctn_in_out里面重复的记录各位帮忙 我现在有点晕了
count(case when b.ctn_no is not null then 1 end) as 已进来,
count(case when b.ctn_no is null then 1 end) as 未进来
from in_ifcsum_empty a left join
(
select * from ctn_in_out aa
where not exists(select 1 from ctn_in_ount bb where aa.ctn_no=bb.ctn_no and aa.id<bb.id)
) b on a.ctn_no=b.ctn_no
where a.input_time>='2008-05-01 14:41:54' and a.input_time<='2008-05-09 14:41:54'
and a.ship_cor_code='APL' and
a.mode_code in ('SJ','TK','TS') group by a.ship_cor_code,a.mode_code
order by a.ship_cor_code