Skip to content

5.1.11升级到 5.3.2 时候，union语句 order by报错 #856

Open
Open
5.1.11升级到 5.3.2 时候，union语句 order by报错#856
@lcl-yn

Description

@lcl-yn
lcl-yn
opened on Jun 4, 2025

在原来版本5.1.11时候 order by 拼接正常，但是在5.3.2的时候，order by 拼接在了里层，导致报错：SQL 错误 [1033] [S0001]: 除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。

下面是还原SQL
在原来版本5.1.11时候 对下面SQL order by 是正常的

select ocnt_departure_date,
           movement_number,
           a.ocnt_mawb_number,
           iif(sum(cast(a.pack_no AS int)) is null,0,sum(cast(a.pack_no AS int))) as packNo,
           count(a.logistics_no) as logisticsCount,
           sum(iif(orders_status not in (-1,0,4,100),1,0)) as orders_status,
           sum(iif(logistics_status not in (-1,0,4,100),1,0)) as logistics_status,
           sum(iif(receipts_status not in (-1,0,4,100),1,0)) as receipts_status,
           sum(iif(orders_status in (2,120),1,0)) as orders_storage,
           sum(iif(logistics_status in (2,120),1,0)) as logistics_storage,
           sum(iif(receipts_status in (2,120),1,0)) as receipts_storage,
           sum(iif(orders_status in (2,120),1,0)) as orders_success,
           sum(iif(logistics_status in (2,120),1,0)) as logistics_success,
           sum(iif(receipts_status in (2,120),1,0)) as receipts_success,
           sum(temp.shipment_weight) as shipmentActualWeightSum
    from import_data as a
    join (select iif(cc.declare_weight = 0,id.shipment_weight,id.shipment_actual_weight) as shipment_weight,id.logistics_no
                    from import_data as id, corporate_customers as cc where id.shipper_account_number = cc.account and id.area = cc.area and cc.status=1) as temp on temp.logistics_no = a.logistics_no
    inner join declare_status as b on a.logistics_no = b.logistics_no
    inner join corporate_customers cc2 on cc2.account = a.shipper_account_number and cc2.area = a.area and cc2.status = 1
    where
      a.ocnt_mawb_number = b.ocnt_mawb_number
      and a.area = #{dto.area,jdbcType=CHAR}
      and cc2.declare_mode in (1,2,3)

    group by a.ocnt_departure_date,a.movement_number,a.ocnt_mawb_number
    having count(a.logistics_no) != sum(iif(logistics_status in (2,120),1,0))

    union

    select ocnt_departure_date,
    movement_number,
    a.ocnt_mawb_number,
    iif(sum(cast(a.pack_no AS int)) is null,0,sum(cast(a.pack_no AS int))) as packNo,
    count(a.logistics_no) as logisticsCount,
    sum(iif(orders_status not in (-1,0,4,100),1,0)) as orders_status,
    sum(iif(logistics_status not in (-1,0,4,100),1,0)) as logistics_status,
    sum(iif(receipts_status not in (-1,0,4,100),1,0)) as receipts_status,
    sum(iif(orders_status in (2,120),1,0)) as orders_storage,
    sum(iif(logistics_status in (2,120),1,0)) as logistics_storage,
    sum(iif(receipts_status in (2,120),1,0)) as receipts_storage,
    sum(iif(orders_status in (2,120),1,0)) as orders_success,
    sum(iif(logistics_status in (2,120),1,0)) as logistics_success,
    sum(iif(receipts_status in (2,120),1,0)) as receipts_success,
    sum(temp.shipment_weight) as shipmentActualWeightSum
    from import_data as a
    join (select iif(cc.declare_weight = 0,id.shipment_weight,id.shipment_actual_weight) as shipment_weight,id.logistics_no
                    from import_data as id, corporate_customers as cc where id.shipper_account_number = cc.account and id.area = cc.area and cc.status=1) as temp on temp.logistics_no = a.logistics_no
    inner join declare_status as b on a.logistics_no = b.logistics_no
    inner join corporate_customers cc2 on cc2.account = a.shipper_account_number and cc2.area = a.area and cc2.status = 1
    where
    a.ocnt_mawb_number = b.ocnt_mawb_number
    and a.area = #{dto.area,jdbcType=CHAR}
    and cc2.declare_mode = 0
   
    group by a.ocnt_departure_date,a.movement_number,a.ocnt_mawb_number
    having count(a.logistics_no) != sum(iif(orders_status in (2,120),1,0))
    or count(a.logistics_no) != sum(iif(logistics_status in (2,120),1,0))

正常的order by SQL

SELECT
	**ROW_NUMBER() OVER (
	ORDER BY ocnt_departure_date DESC) PAGE_ROW_NUMBER,**  -- 在此处拼接了order by 
	ocnt_departure_date,
	movement_number,
	ocnt_mawb_number,
	packNo,
	logisticsCount,
	orders_status,
	logistics_status,
	receipts_status,
	orders_storage,
	logistics_storage,
	receipts_storage,
	orders_success,
	logistics_success,
	receipts_success,
	shipmentActualWeightSum
FROM
	(
	SELECT
		ocnt_departure_date,
		movement_number,
		ocnt_mawb_number,
		packNo,
		logisticsCount,
		orders_status,
		logistics_status,
		receipts_status,
		orders_storage,
		logistics_storage,
		receipts_storage,
		orders_success,
		logistics_success,
		receipts_success,
		shipmentActualWeightSum
	FROM
		(
		SELECT
			a.customize_date AS ocnt_departure_date,
			a.customize_movement_number AS movement_number,
			a.customize_mawb_number AS ocnt_mawb_number,
			iif(sum(CAST(a.pack_no AS int)) IS NULL,
			0,
			sum(CAST(a.pack_no AS int))) AS packNo,
			count(a.logistics_no) AS logisticsCount,
			sum(iif(orders_status NOT IN (-1, 0, 4, 100), 1, 0)) AS orders_status,
			sum(iif(logistics_status NOT IN (-1, 0, 4, 100), 1, 0)) AS logistics_status,
			sum(iif(receipts_status NOT IN (-1, 0, 4, 100), 1, 0)) AS receipts_status,
			sum(iif(orders_status IN (2, 120), 1, 0)) AS orders_storage,
			sum(iif(logistics_status IN (2, 120), 1, 0)) AS logistics_storage,
			sum(iif(receipts_status IN (2, 120), 1, 0)) AS receipts_storage,
			sum(iif(orders_status IN (2, 120), 1, 0)) AS orders_success,
			sum(iif(logistics_status IN (2, 120), 1, 0)) AS logistics_success,
			sum(iif(receipts_status IN (2, 120), 1, 0)) AS receipts_success,
			sum(temp.shipment_weight) AS shipmentActualWeightSum
		FROM
			import_data AS a
		JOIN (
			SELECT
				iif(cc.declare_weight = 0,
				id.shipment_weight,
				id.shipment_actual_weight) AS shipment_weight,
				id.logistics_no
			FROM
				import_data AS id,
				corporate_customers AS cc
			WHERE
				id.shipper_account_number = cc.account
				AND id.area = cc.area
				AND cc.status = 1) AS temp ON
			temp.logistics_no = a.logistics_no
		INNER JOIN declare_status AS b ON
			a.logistics_no = b.logistics_no
		INNER JOIN corporate_customers cc2 ON
			cc2.account = a.shipper_account_number
			AND cc2.area = a.area
			AND cc2.status = 1
		WHERE
			a.area = 'CAN'
			AND cc2.declare_mode IN (1, 2, 3)
			AND a.customize_mawb_number != ''
			AND a.customize_mawb_number IS NOT NULL

		GROUP BY
			a.customize_date,
			a.customize_movement_number,
			a.customize_mawb_number
		HAVING
			count(a.logistics_no) != sum(iif(logistics_status IN (2, 120), 1, 0))
	UNION
		SELECT
			a.customize_date AS ocnt_departure_date,
			a.customize_movement_number AS movement_number,
			a.customize_mawb_number AS ocnt_mawb_number,
			iif(sum(CAST(a.pack_no AS int)) IS NULL,
			0,
			sum(CAST(a.pack_no AS int))) AS packNo,
			count(a.logistics_no) AS logisticsCount,
			sum(iif(orders_status NOT IN (-1, 0, 4, 100), 1, 0)) AS orders_status,
			sum(iif(logistics_status NOT IN (-1, 0, 4, 100), 1, 0)) AS logistics_status,
			sum(iif(receipts_status NOT IN (-1, 0, 4, 100), 1, 0)) AS receipts_status,
			sum(iif(orders_status IN (2, 120), 1, 0)) AS orders_storage,
			sum(iif(logistics_status IN (2, 120), 1, 0)) AS logistics_storage,
			sum(iif(receipts_status IN (2, 120), 1, 0)) AS receipts_storage,
			sum(iif(orders_status IN (2, 120), 1, 0)) AS orders_success,
			sum(iif(logistics_status IN (2, 120), 1, 0)) AS logistics_success,
			sum(iif(receipts_status IN (2, 120), 1, 0)) AS receipts_success,
			sum(temp.shipment_weight) AS shipmentActualWeightSum
		FROM
			import_data AS a
		JOIN (
			SELECT
				iif(cc.declare_weight = 0,
				id.shipment_weight,
				id.shipment_actual_weight) AS shipment_weight,
				id.logistics_no
			FROM
				import_data AS id,
				corporate_customers AS cc
			WHERE
				id.shipper_account_number = cc.account
				AND id.area = cc.area
				AND cc.status = 1) AS temp ON
			temp.logistics_no = a.logistics_no
		INNER JOIN declare_status AS b ON
			a.logistics_no = b.logistics_no
		INNER JOIN corporate_customers cc2 ON
			cc2.account = a.shipper_account_number
			AND cc2.area = a.area
			AND cc2.status = 1
		WHERE
			a.area =  'CAN'
			AND cc2.declare_mode = 0
			AND a.customize_mawb_number != ''
			AND a.customize_mawb_number IS NOT NULL

		GROUP BY
			a.customize_date,
			a.customize_movement_number,
			a.customize_mawb_number
		HAVING
			count(a.logistics_no) != sum(iif(orders_status IN (2, 120), 1, 0))
			OR count(a.logistics_no) != sum(iif(logistics_status IN (2, 120), 1, 0))) AS WRAP_OUTER_TABLE) AS PAGE_TABLE_ALIAS

升级版本后 5.3.2 之后 order by 变成了 这个样子

SELECT
	TOP 10 ocnt_departure_date,
	movement_number,
	ocnt_mawb_number,
	packNo,
	logisticsCount,
	orders_status,
	logistics_status,
	receipts_status,
	orders_storage,
	logistics_storage,
	receipts_storage,
	orders_success,
	logistics_success,
	receipts_success,
	shipmentActualWeightSum
FROM
	(
	SELECT
		ROW_NUMBER() OVER (
		ORDER BY RAND()) PAGE_ROW_NUMBER,
		ocnt_departure_date,
		movement_number,
		ocnt_mawb_number,
		packNo,
		logisticsCount,
		orders_status,
		logistics_status,
		receipts_status,
		orders_storage,
		logistics_storage,
		receipts_storage,
		orders_success,
		logistics_success,
		receipts_success,
		shipmentActualWeightSum
	FROM
		(
		SELECT
			ocnt_departure_date,
			movement_number,
			ocnt_mawb_number,
			packNo,
			logisticsCount,
			orders_status,
			logistics_status,
			receipts_status,
			orders_storage,
			logistics_storage,
			receipts_storage,
			orders_success,
			logistics_success,
			receipts_success,
			shipmentActualWeightSum
		FROM
			(
			SELECT
				a.customize_date AS ocnt_departure_date,
				a.customize_movement_number AS movement_number,
				a.customize_mawb_number AS ocnt_mawb_number,
				iif(sum(CAST(a.pack_no AS int)) IS NULL,
				0,
				sum(CAST(a.pack_no AS int))) AS packNo,
				count(a.logistics_no) AS logisticsCount,
				sum(iif(orders_status NOT IN (-1, 0, 4, 100), 1, 0)) AS orders_status,
				sum(iif(logistics_status NOT IN (-1, 0, 4, 100), 1, 0)) AS logistics_status,
				sum(iif(receipts_status NOT IN (-1, 0, 4, 100), 1, 0)) AS receipts_status,
				sum(iif(orders_status IN (2, 120), 1, 0)) AS orders_storage,
				sum(iif(logistics_status IN (2, 120), 1, 0)) AS logistics_storage,
				sum(iif(receipts_status IN (2, 120), 1, 0)) AS receipts_storage,
				sum(iif(orders_status IN (2, 120), 1, 0)) AS orders_success,
				sum(iif(logistics_status IN (2, 120), 1, 0)) AS logistics_success,
				sum(iif(receipts_status IN (2, 120), 1, 0)) AS receipts_success,
				sum(temp.shipment_weight) AS shipmentActualWeightSum
			FROM
				import_data AS a
			JOIN (
				SELECT
					iif(cc.declare_weight = 0,
					id.shipment_weight,
					id.shipment_actual_weight) AS shipment_weight,
					id.logistics_no
				FROM
					import_data AS id,
					corporate_customers AS cc
				WHERE
					id.shipper_account_number = cc.account
					AND id.area = cc.area
					AND cc.status = 1) AS temp
 ON
				temp.logistics_no = a.logistics_no
			INNER JOIN declare_status AS b
 ON
				a.logistics_no = b.logistics_no
			INNER JOIN corporate_customers cc2
 ON
				cc2.account = a.shipper_account_number
				AND cc2.area = a.area
				AND cc2.status = 1
			WHERE
				a.area = 'CAN'
				AND cc2.declare_mode IN (1, 2, 3)
				AND a.customize_mawb_number != ''
				AND a.customize_mawb_number IS NOT NULL
				AND a.customize_date BETWEEN '2022-06-01 00:00:00.0' AND '2025-06-07 00:00:00.0'
			GROUP BY
				a.customize_date,
				a.customize_movement_number,
				a.customize_mawb_number
			HAVING
				count(a.logistics_no) != sum(iif(logistics_status IN (2, 120), 1, 0))
		UNION
			SELECT
				a.customize_date AS ocnt_departure_date,
				a.customize_movement_number AS movement_number,
				a.customize_mawb_number AS ocnt_mawb_number,
				iif(sum(CAST(a.pack_no AS int)) IS NULL,
				0,
				sum(CAST(a.pack_no AS int))) AS packNo,
				count(a.logistics_no) AS logisticsCount,
				sum(iif(orders_status NOT IN (-1, 0, 4, 100), 1, 0)) AS orders_status,
				sum(iif(logistics_status NOT IN (-1, 0, 4, 100), 1, 0)) AS logistics_status,
				sum(iif(receipts_status NOT IN (-1, 0, 4, 100), 1, 0)) AS receipts_status,
				sum(iif(orders_status IN (2, 120), 1, 0)) AS orders_storage,
				sum(iif(logistics_status IN (2, 120), 1, 0)) AS logistics_storage,
				sum(iif(receipts_status IN (2, 120), 1, 0)) AS receipts_storage,
				sum(iif(orders_status IN (2, 120), 1, 0)) AS orders_success,
				sum(iif(logistics_status IN (2, 120), 1, 0)) AS logistics_success,
				sum(iif(receipts_status IN (2, 120), 1, 0)) AS receipts_success,
				sum(temp.shipment_weight) AS shipmentActualWeightSum
			FROM
				import_data AS a
			JOIN (
				SELECT
					iif(cc.declare_weight = 0,
					id.shipment_weight,
					id.shipment_actual_weight) AS shipment_weight,
					id.logistics_no
				FROM
					import_data AS id,
					corporate_customers AS cc
				WHERE
					id.shipper_account_number = cc.account
					AND id.area = cc.area
					AND cc.status = 1) AS temp
 ON
				temp.logistics_no = a.logistics_no
			INNER JOIN declare_status AS b
 ON
				a.logistics_no = b.logistics_no
			INNER JOIN corporate_customers cc2
 ON
				cc2.account = a.shipper_account_number
				AND cc2.area = a.area
				AND cc2.status = 1
			WHERE
				a.area = 'CAN'
				AND cc2.declare_mode = 0
				AND a.customize_mawb_number != ''
				AND a.customize_mawb_number IS NOT NULL
				AND a.customize_date BETWEEN '2022-06-01 00:00:00.0' AND '2025-06-07 00:00:00.0'
			GROUP BY
				a.customize_date,
				a.customize_movement_number,
				a.customize_mawb_number
			HAVING
				count(a.logistics_no) != sum(iif(orders_status IN (2, 120), 1, 0))
				OR count(a.logistics_no) != sum(iif(logistics_status IN (2, 120), 1, 0))
			**ORDER BY
				ocnt_departure_date DESC**) AS WRAP_OUTER_TABLE) AS PAGE_TABLE_ALIAS) AS PAGE_TABLE_ALIAS
WHERE
	PAGE_ROW_NUMBER > 0
ORDER BY
	PAGE_ROW_NUMBER;

可以明显看到 order by 语句 （ORDER BY ocnt_departure_date DESC） 拼接到了后面，导致报错。这是为什么？如何修复？

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Fields

    No fields configured for issues without a type.

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions