问题描述
SELECT Distinct shmt_no，shmt_id，cnsg_id，shpr_id，shmt_item_count，
source_oloc_id，destination_oloc_id，shmt_note，shmt_created_date，
shmt_doc_ref_no，styp_type，shmt_damage，shmt_is_invoice_attached，shmt_is_packing_list_attached，
shmt_recd_created_date，shmt_recd_modified_date，
styp_is_active，styp_modified_date，styp_timestamp ，ssta_id，
shps_modified_date，shps_timestamp，cnsg_cust_id，cnsg_name，cnsg_address，cnsg_city，
cnsg_state，cnsg_country，cnsg_postal_code，cnsg_telephone1，
cnsg_telephone2，cnsg_cell_phone ，cnsg_modified_date，
cnsg_timestamp，shpr_cust_id，shpr_name，
shpr_address，shpr_city，shpr_country，shpr_state，shpr_postal_code，
shpr_telephone1，shpr_telephone2，shpr_cell_phone，shpr_modified_date，
shpr_timestamp，source_oloc_name，destination_oloc_name，destination_oloc_currency，
source_oloc_short_name，destination_oloc_short_name，source_oloc_currency，dlrl_bltype，dlpl_id，
dlrl_id，dlpl_name，dlpl_area_code，dlpl_state，shmt_dangerous，
oloc_id，dlpl_is_default，dlpl_is_active，dlpl_modified_date，
dlpl_timestamp，dtyp_id，dtyp_name，deli_place_oloc_id，
source_port_id，destination_port_id，shmt_transp_mode，shmt_loading_mode，
shmt_shipping_mode，shmt_shipping_direction，source_port_name，destination_port_name，created_oloc_name，styp_id
FROM（SELECT Shipments.shmt_no，Shipments.shmt_id，Shipments.cnsg_id，Shipments.shpr_id，Shipments.shmt_item_count，Shipments.source_oloc_id，
Shipments.source_port_id，Shipments.destination_port_id，Shipments.shmt_transp_mode，Shipments.shmt_loading_mode，
Shipments.shmt_shipping_mode，Shipments.shmt_shipping_direction，Shipments.oloc_id，Shipments.destination_oloc_id，
Shipments.shmt_dangerous，Shipments.shmt_note，Shipments.shmt_created_date，Shipments.shmt_doc_ref_no，Shipment_Types.styp_type，Shipments.styp_id，
Shipments.shmt_damage，Shipments.shmt_is_invoice_attached，Shipments.shmt_is_packing_list_attached，Shipments.shmt_recd_created_date，
Sh ipments.shmt_recd_modified_date，CONVERT（Varchar，CAST（Shipments.shmt_timestamp AS datetime），13）AS shmt_timestamp，Receipts.rcpt_id，
Shipments.shmt_has_chrg_share_rule，Receipts.rcpt_modified_date，CONVERT（Varchar，CAST（Receipts。 rcpt_timestamp AS datetime），13）
AS rcpt_timestamp，Shipment_Types.styp_is_active，Shipment_Types.styp_modified_date，CONVERT（Varchar，
CAST（Shipment_Types.styp_timestamp AS datetime），13）AS styp_timestamp，Shipment_Shipment_States.ssta_id，
Shipment_Shipment_States.shps_modified_date，CONVERT（Varchar，CAST（Shipment_Shipment_States.shps_timestamp AS datetime），13）
AS shps_timestamp，Consignees.cnsg_cust_id，Consignees。 cnsg_name，Consignees.cnsg_address，Consignees.cnsg_city，
Consignees.cnsg_state，Consignees.cnsg_country，Consignees.cns g_postal_code，Consignees.cnsg_telephone1，Consignees.cnsg_telephone2，
Consignees.cnsg_cell_phone，Consignees.cnsg_modified_date，CONVERT（Varchar，CAST（Consignees.cnsg_timestamp AS datetime），13）
AS cnsg_timestamp，Shippers.shpr_cust_id，Shippers.shpr_name，Shippers.shpr_address，Shippers.shpr_city，Shippers.shpr_country，
Shippers.shpr_state，Shippers.shpr_postal_code，Shippers.shpr_telephone1，Shippers.shpr_telephone2，Shippers.shpr_cell_phone，
Shippers.shpr_modified_date，CONVERT（Varchar，CAST（Shippers.shpr_timestamp AS datetime），13）AS shpr_timestamp，
OFFICE_LOCATIONS_1.oloc_name AS source_oloc_name，Office_Locations.oloc_name AS destination_oloc_name，
OFFICE_LOCATIONS_1.oloc_short_name AS source_oloc_short_name，Office_Locations.oloc_short_name AS destinati on_oloc_short_name，
OFFICE_LOCATIONS_1.oloc_currency AS source_oloc_currency，Office_Locations.oloc_currency AS destination_oloc_currency，
vw_Shmt_itms.jobs_id，vw_Shmt_itms.jobs_no，vw_Shmt_itms.coit_id，Delivery_Place_Relations.dlrl_bltype，
ISNULL（Delivery_Place_Relations.dlpl_id，0）AS dlpl_id，Delivery_Place_Relations.dlrl_id，Delivery_Places.dlpl_name，
Delivery_Places.dlpl_area_code，Delivery_Places.dlpl_state，Delivery_Places.oloc_id AS deli_place_oloc_id，Delivery_Places.dlpl_is_default，
Delivery_Places.dlpl_is_active，Delivery_Places.dlpl_modified_date，CONVERT（Varchar，CAST（Delivery_Places.dlpl_timestamp AS datetime），13）
AS dlpl_timestamp，Delivery_Types.dtyp_name，Shipments.dtyp_id，Ports .port_name AS source_port_name，
Ports_1.port_name AS destination_port_name，Office_Locations_2.oloc_name AS created_oloc_name
来自Office_Locations AS OFFICE_LOCATIONS_1正确的外部加入
端口正确的外部加入
发货INNER JOIN
收货ON Shipments.shmt_id = Receipts.shmt_id INNER JOIN
Shipment_Types ON Shipments.styp_id = Shipment_Types.styp_id LEFT OUTER JOIN
Shipment_Shipment_States ON货件。 shmt_id = Shipment_Shipment_States.shmt_id INNER JOIN
托运人ON Shipments.shpr_id = Shippers.shpr_id内部联系
收货人ON Shipments.cnsg_id = Consignees.cnsg_id INNER JOIN
Office_Locations ON Shipments.destination_oloc_id = Office_Locations.oloc_id INNER JOIN
vw_Shmt_itms ON Shipments.shmt_id = vw_Shmt_itms.shmt_id INNER JOIN
端口AS Ports_1 ON Shipments.destination_port_id = Ports_1.port_id LEFT OUTER JOIN
Office_Locations AS Office_Locations_2 ON Shipments.oloc_id = Office_Locations_2 .oloc_id ON Ports.port_id = Shipments.source_port_id ON
OFFICE_LOCATIONS_1.oloc_id = Shipments.source_oloc_id LEFT OUTER JOIN
Delivery_Types ON Shipments.dtyp_id = Delivery_Types.dtyp_id LEFT JOIN
Delivery_Place_Relations ON Shipments.shmt_id = Delivery_Place_Relations.dlrl_blitem_id AND
Delivery_Place_Relations.dlrl_bltype ='ApprovalItemType.SHIPMENT.ToString（）'LEFT JOIN
Delivery_Places ON Delivery_Place_Relations .dlpl_id = Delivery_Places.dlpl_id）AS TAB;
SELECT Distinct shmt_no, shmt_id, cnsg_id, shpr_id, shmt_item_count,
source_oloc_id, destination_oloc_id, shmt_note, shmt_created_date,
shmt_doc_ref_no, styp_type,shmt_damage, shmt_is_invoice_attached, shmt_is_packing_list_attached,
shmt_recd_created_date, shmt_recd_modified_date,
shmt_timestamp, rcpt_id, shmt_has_chrg_share_rule,
rcpt_modified_date, rcpt_timestamp,
styp_is_active, styp_modified_date, styp_timestamp, ssta_id,
shps_modified_date, shps_timestamp, cnsg_cust_id, cnsg_name, cnsg_address, cnsg_city,
cnsg_state, cnsg_country, cnsg_postal_code, cnsg_telephone1,
cnsg_telephone2, cnsg_cell_phone, cnsg_modified_date,
cnsg_timestamp, shpr_cust_id, shpr_name,
shpr_address, shpr_city, shpr_country, shpr_state, shpr_postal_code,
shpr_telephone1, shpr_telephone2, shpr_cell_phone, shpr_modified_date,
shpr_timestamp, source_oloc_name, destination_oloc_name , destination_oloc_currency,
source_oloc_short_name ,destination_oloc_short_name ,source_oloc_currency, dlrl_bltype, dlpl_id,
dlrl_id, dlpl_name, dlpl_area_code, dlpl_state, shmt_dangerous,
oloc_id, dlpl_is_default, dlpl_is_active, dlpl_modified_date,
dlpl_timestamp , dtyp_id, dtyp_name , deli_place_oloc_id,
source_port_id, destination_port_id, shmt_transp_mode, shmt_loading_mode,
shmt_shipping_mode, shmt_shipping_direction, source_port_name, destination_port_name, created_oloc_name, styp_id
FROM (SELECT Shipments.shmt_no, Shipments.shmt_id, Shipments.cnsg_id, Shipments.shpr_id, Shipments.shmt_item_count, Shipments.source_oloc_id,
Shipments.source_port_id, Shipments.destination_port_id, Shipments.shmt_transp_mode, Shipments.shmt_loading_mode,
Shipments.shmt_shipping_mode, Shipments.shmt_shipping_direction, Shipments.oloc_id, Shipments.destination_oloc_id,
Shipments.shmt_dangerous, Shipments.shmt_note, Shipments.shmt_created_date, Shipments.shmt_doc_ref_no, Shipment_Types.styp_type, Shipments.styp_id,
Shipments.shmt_damage, Shipments.shmt_is_invoice_attached, Shipments.shmt_is_packing_list_attached, Shipments.shmt_recd_created_date,
Shipments.shmt_recd_modified_date, CONVERT(Varchar, CAST(Shipments.shmt_timestamp AS datetime), 13) AS shmt_timestamp, Receipts.rcpt_id,
Shipments.shmt_has_chrg_share_rule, Receipts.rcpt_modified_date, CONVERT(Varchar, CAST(Receipts.rcpt_timestamp AS datetime), 13)
AS rcpt_timestamp, Shipment_Types.styp_is_active, Shipment_Types.styp_modified_date, CONVERT(Varchar,
CAST(Shipment_Types.styp_timestamp AS datetime), 13) AS styp_timestamp, Shipment_Shipment_States.ssta_id,
Shipment_Shipment_States.shps_modified_date, CONVERT(Varchar, CAST(Shipment_Shipment_States.shps_timestamp AS datetime), 13)
AS shps_timestamp, Consignees.cnsg_cust_id, Consignees.cnsg_name, Consignees.cnsg_address, Consignees.cnsg_city,
Consignees.cnsg_state, Consignees.cnsg_country, Consignees.cnsg_postal_code, Consignees.cnsg_telephone1, Consignees.cnsg_telephone2,
Consignees.cnsg_cell_phone, Consignees.cnsg_modified_date, CONVERT(Varchar, CAST(Consignees.cnsg_timestamp AS datetime), 13)
AS cnsg_timestamp, Shippers.shpr_cust_id, Shippers.shpr_name, Shippers.shpr_address, Shippers.shpr_city, Shippers.shpr_country,
Shippers.shpr_state, Shippers.shpr_postal_code, Shippers.shpr_telephone1, Shippers.shpr_telephone2, Shippers.shpr_cell_phone,
Shippers.shpr_modified_date, CONVERT(Varchar, CAST(Shippers.shpr_timestamp AS datetime), 13) AS shpr_timestamp,
OFFICE_LOCATIONS_1.oloc_name AS source_oloc_name, Office_Locations.oloc_name AS destination_oloc_name,
OFFICE_LOCATIONS_1.oloc_short_name AS source_oloc_short_name, Office_Locations.oloc_short_name AS destination_oloc_short_name,
OFFICE_LOCATIONS_1.oloc_currency AS source_oloc_currency, Office_Locations.oloc_currency AS destination_oloc_currency,
vw_Shmt_itms.jobs_id, vw_Shmt_itms.jobs_no, vw_Shmt_itms.coit_id, Delivery_Place_Relations.dlrl_bltype,
ISNULL(Delivery_Place_Relations.dlpl_id, 0) AS dlpl_id, Delivery_Place_Relations.dlrl_id, Delivery_Places.dlpl_name,
Delivery_Places.dlpl_area_code, Delivery_Places.dlpl_state, Delivery_Places.oloc_id AS deli_place_oloc_id, Delivery_Places.dlpl_is_default,
Delivery_Places.dlpl_is_active, Delivery_Places.dlpl_modified_date, CONVERT(Varchar, CAST(Delivery_Places.dlpl_timestamp AS datetime), 13)
AS dlpl_timestamp, Delivery_Types.dtyp_name, Shipments.dtyp_id, Ports.port_name AS source_port_name,
Ports_1.port_name AS destination_port_name, Office_Locations_2.oloc_name AS created_oloc_name
FROM Office_Locations AS OFFICE_LOCATIONS_1 RIGHT OUTER JOIN
Ports RIGHT OUTER JOIN
Shipments INNER JOIN
Receipts ON Shipments.shmt_id = Receipts.shmt_id INNER JOIN
Shipment_Types ON Shipments.styp_id = Shipment_Types.styp_id LEFT OUTER JOIN
Shipment_Shipment_States ON Shipments.shmt_id = Shipment_Shipment_States.shmt_id INNER JOIN
Shippers ON Shipments.shpr_id = Shippers.shpr_id INNER JOIN
Consignees ON Shipments.cnsg_id = Consignees.cnsg_id INNER JOIN
Office_Locations ON Shipments.destination_oloc_id = Office_Locations.oloc_id INNER JOIN
vw_Shmt_itms ON Shipments.shmt_id = vw_Shmt_itms.shmt_id INNER JOIN
Ports AS Ports_1 ON Shipments.destination_port_id = Ports_1.port_id LEFT OUTER JOIN
Office_Locations AS Office_Locations_2 ON Shipments.oloc_id = Office_Locations_2.oloc_id ON Ports.port_id = Shipments.source_port_id ON
OFFICE_LOCATIONS_1.oloc_id = Shipments.source_oloc_id LEFT OUTER JOIN
Delivery_Types ON Shipments.dtyp_id = Delivery_Types.dtyp_id LEFT JOIN
Delivery_Place_Relations ON Shipments.shmt_id = Delivery_Place_Relations.dlrl_blitem_id AND
Delivery_Place_Relations.dlrl_bltype = ' ApprovalItemType.SHIPMENT.ToString() ' LEFT JOIN
Delivery_Places ON Delivery_Place_Relations.dlpl_id = Delivery_Places.dlpl_id ) AS TAB ;
推荐答案
EXEC sp_configure 'query governor cost limit'
默认最低为se t为0，最大值设置为2147483647.
检查服务器设置。
查询调控器允许我们确保不运行昂贵的查询。这可以使您免于在一天中间执行的意外失控查询，并且可以防止它破坏系统的性能。
i会建议优化你的查询，这样它就不应该哄骗你的服务器资源。
by default the minimum is set to 0 and maximum is set to 2147483647.
check your server setting.
The Query Governor allows us to make sure that expensive queries do not run. This can save you from the accidental runaway query that gets executed in the middle of the day, and can keep it from killing your system’s performance.
i would suggest to optimize your query so that its should not chowk your server resources.
这篇关于查询已被取消，因为此查询错误消息的估计成本来自服务器，同时排除查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！