我知道我可能需要进行分组才能完成此任务,但我不太了解。PostgreSQL 8.1
我需要将结果集限制为“原始> 2017-01-01”。原始设置在第11行中。它在Excel中作为一列返回,是一个日期。
目前,它返回的结果可以追溯到几年前的数据,而我们并不需要它。
select distribution_stop_information.customer_no, distribution_line_items.item_number, distribution_line_items.container_id, distribution_line_items.item_description, distribution_stop_information.customer_reference, distribution_stop_information.bol_number, SUBSTRING(distribution_stop_information.bol_number,1,4) as ODDC, SUBSTRING(distribution_stop_information.bol_number,9,4) as ODRT, distribution_stop_information.branch_id, distribution_stop_information.route_date, (select count(innerDLI.item_number)-1 from distribution_line_items innerDLI where innerDLI.item_number = distribution_line_items.item_number) as RDLcount, (select innerDLI.datetime_created from distribution_line_items innerDLI where innerDLI.item_number = distribution_line_items.item_number order by innerDLI.datetime_created asc limit 1) as Origination, distribution_stop_information.route_code, distribution_stop_information.stop_name, distribution_stop_information.stop_address, distribution_stop_information.stop_city, distribution_stop_information.stop_state, distribution_stop_information.stop_zip_postal_code, distribution_stop_information.stop_signature, CASE WHEN distribution_line_items.exception_code is not null THEN distribution_line_items.exception_code ELSE distribution_stop_information.stop_exception_code END as ExceptionCode, distribution_stop_information.signature_file_name, distribution_stop_information.sign_bitmap_file_exists, ScanR.item_was_scanned as ScanR, ScanL.item_was_scanned as ScanL, ScanD.item_was_scanned as ScanD from distribution_stop_information inner join distribution_line_items on distribution_line_items.unique_id_no = distribution_stop_information.unique_id_no left join distribution_item_scans ScanR on ScanR.item_sequence_no = distribution_line_items.item_sequence_no and ScanR.stop_unique_id_no = distribution_stop_information.unique_id_no and ScanR.scan_type = 1 left join distribution_item_scans ScanL on ScanL.item_sequence_no = distribution_line_items.item_sequence_no and ScanL.stop_unique_id_no = distribution_stop_information.unique_id_no and ScanL.scan_type = 2 and ScanL.scan_datetime between to_date(?, 'YYYY-MM-DD') and ( to_date(?, 'YYYY-MM-DD') + interval '1 day' ) left join distribution_item_scans ScanD on ScanD.item_sequence_no = distribution_line_items.item_sequence_no and ScanD.stop_unique_id_no = distribution_stop_information.unique_id_no and ScanD.scan_type = 9 and ScanD.scan_datetime between to_date(?, 'YYYY-MM-DD') and ( to_date(?, 'YYYY-MM-DD') + interval '1 day' ) where distribution_stop_information.customer_no in ( '90202' ) and distribution_stop_information.route_date between ( to_date(?, 'YYYY-MM-DD') ) and ( to_date(?, 'YYYY-MM-DD') ) and distribution_line_items.datetime_created between ( to_date(?, 'YYYY-MM-DD') - interval '288 hours' ) and ( to_date(?, 'YYYY-MM-DD') + interval '12 hours' )
更新:我已将选择内容更改为这样,它似乎正在工作-是否对这样做是否正确有任何想法?它只会一直盯着过去的180天。
(select innerDLI.datetime_created from distribution_line_items innerDLI where innerDLI.item_number = distribution_line_items.item_number and innerDLI.datetime_created > to_date(?, 'YYYY-MM-DD') - interval '180 days' order by innerDLI.datetime_created asc limit 1) as Origination,
这是您的操作方式-列名中的别名
select -- etc etc (select innerDLI.datetime_created from distribution_line_items innerDLI where innerDLI.item_number = distribution_line_items.item_number order by innerDLI.datetime_created asc limit 1) as Origination, -- etc etc from distribution_stop_information -- etc etc
这是将它放在where别名中的方式
select -- etc etc Origination.datetime_created -- etc etc from distribution_stop_information left join distribution_line_items AS Origination ON Origination.item_number = distribution_line_items.item_number where Origination.datetime_created > to_date(?, 'YYYY-MM-DD') - interval '180 days' -- etc etc
没有什么比您发布的解决方案 更好 (sql优化器应该产生相同的计划),但是它是“在where子句中使用别名”