以a.ch_no 分组,其他字段取聚合函数(具体看需求定) select a.ch_no, a.ch_redat=max(a.ch_redat), a.ch_qty=max(a.ch_qty), a.ch_qty1=max(a.ch_qty1),, b.ch_date=max(b.ch_date), a.el_no=max(a.el_no), a.ch_unit=max(a.ch_unit), a.ch_size=max(a.ch_size), a.ch_unit1=max(a.ch_unit1) from iech01d1 as a inner join iech03d1 as b on b.ch_sno=a.ch_no and a.el_no = b.el_no where 1=1 and a.ch_redat>='2005/07/10' and a.ch_redat<='2005/07/11' group by a.ch_no
你把distinct写前面,那他后面的几个字段任意搭配,都算唯一, 所以你结果并不是你想要的.
select a.ch_no ,a.ch_redat,a.ch_qty,a.ch_qty1, b.ch_date,a.el_no,a.ch_unit,a.ch_size,a.ch_unit1 from iech01d1 as a inner join iech03d1 as b on b.ch_sno=a.ch_no and a.el_no = b.el_no where 1=1 and a.ch_redat>='2005/07/10' and a.ch_redat<='2005/07/11' and not exists (select 1 from jech01d1 where ch_no=a.ch_no and ch_redat<a.chredat)
select a.ch_no,
a.ch_redat=max(a.ch_redat),
a.ch_qty=max(a.ch_qty),
a.ch_qty1=max(a.ch_qty1),,
b.ch_date=max(b.ch_date),
a.el_no=max(a.el_no),
a.ch_unit=max(a.ch_unit),
a.ch_size=max(a.ch_size),
a.ch_unit1=max(a.ch_unit1)
from
iech01d1 as a inner join iech03d1 as b
on b.ch_sno=a.ch_no and a.el_no = b.el_no
where
1=1 and a.ch_redat>='2005/07/10' and a.ch_redat<='2005/07/11'
group by a.ch_no
所以你结果并不是你想要的.
select a.ch_no ,a.ch_redat,a.ch_qty,a.ch_qty1,
b.ch_date,a.el_no,a.ch_unit,a.ch_size,a.ch_unit1 from iech01d1 as a inner join iech03d1 as b on b.ch_sno=a.ch_no and a.el_no = b.el_no
where 1=1 and a.ch_redat>='2005/07/10' and a.ch_redat<='2005/07/11' and not exists
(select 1 from jech01d1 where ch_no=a.ch_no and ch_redat<a.chredat)