MySQL 获取子查询内表的行号

Question

我有一个查询，需要按特定 ID 获取表的行数:

SELECT
    (CAST(`ot`.`value` AS DECIMAL(6,2))) AS `value`,
    `op`.`orders_id`,
    (
        SELECT
            COUNT(1) AS `total`
        FROM 
        (
            SELECT
                `op2`.`concert_date`
            FROM
                `orders_products` `op2`
            WHERE
                `op2`.`orders_id` = `op`.`orders_id`
            AND
                `op2`.`concert_date` <> ''
            GROUP BY CONCAT(`op2`.`concert_date`,' ',`op2`.`concert_time`)
        ) AS `e`
    ) AS `devider`
FROM
    `categories` `c`
    JOIN `products` `p` ON `p`.`section_id` = `c`.`section_id`
    JOIN `orders_products` `op` ON `op`.`products_id` = `p`.`products_id`
    JOIN `orders_total` `ot` ON `ot`.`orders_id` = `op`.`orders_id`
WHERE
    `c`.`section_id` = 25
AND
    `p`.`product_type` IN ('P')
AND
    `ot`.`class` IN ('ot_shipping')
GROUP BY `op`.`orders_id`

主要问题是我收到错误

#1054 - “where 子句”中存在未知列“op.orders_id”

并且无法运行这个。我在循环中分离了查询，但这造成了性能问题，并且希望将其推送到一个查询中。有什么想法吗？

Answer 1

尝试删除子子查询并使用COUNT(DISTINCT ..)

SELECT
    (CAST(`ot`.`value` AS DECIMAL(6,2))) AS `value`,
    `op`.`orders_id`,
    (
        SELECT
           COUNT(DISTINCT CONCAT(`op2`.`concert_date`,' ',`op2`.`concert_time`))
        FROM
          `orders_products` `op2`
        WHERE
          `op2`.`orders_id` = `op`.`orders_id`
        AND
           `op2`.`concert_date` <> ''
    ) AS `devider`
FROM
    `categories` `c`
    JOIN `products` `p` ON `p`.`section_id` = `c`.`section_id`
    JOIN `orders_products` `op` ON `op`.`products_id` = `p`.`products_id`
    JOIN `orders_total` `ot` ON `ot`.`orders_id` = `op`.`orders_id`
WHERE
    `c`.`section_id` = 25
AND
    `p`.`product_type` IN ('P')
AND
    `ot`.`class` IN ('ot_shipping')
GROUP BY `op`.`orders_id`

只要 Concert_date 或 Concert_time 为 null，您甚至不需要子查询或 concat

SELECT
    (CAST(`ot`.`value` AS DECIMAL(6,2))) AS `value`,
    `op`.`orders_id`,
    COUNT(DISTINCT `op`.`concert_date`, `op`.`concert_time`) AS `devider`
FROM
    `categories` `c`
    JOIN `products` `p` ON `p`.`section_id` = `c`.`section_id`
    LEFT JOIN `orders_products` `op` ON `op`.`products_id` = `p`.`products_id`
    JOIN `orders_total` `ot` ON `ot`.`orders_id` = `op`.`orders_id`
WHERE
    `c`.`section_id` = 25
AND
    `p`.`product_type` IN ('P')
AND
    `ot`.`class` IN ('ot_shipping')
GROUP BY `op`.`orders_id`