mysql - 存储过程中查询的未知列错误 (1054)，在查询窗口中工作

Question

我在 MySQL 中有一个奇怪的问题。我写了一个查询，将跨国数据聚合成每小时的数据块。该查询在 Workbench 查询窗口中运行良好。然后，我将查询实现为存储过程，并收到“错误代码：1054。“字段列表”中的未知列“d.SiteId”消息。

SELECT  `d`.`SiteId`, `d`.`TransactionDate`, (`d`.`TimeGrouping` * 10000) as 'HourEndTime', Sum(`d`.`SalesAmount`) as 'TotalSales', count(`d`.`PosTransactionId`) as 'TransactionCount', Sum(`d`.`ItemCount`) as 'ItemCount', `d`.`Currency`
FROM    (SELECT DISTINCT `SiteId`, `TransactionDate`, `TimeGrouping`, `SalesAmount`, `PosTransactionId`, `ItemCount`, `Currency`
        FROM PosSalesDev.Transaction
        WHERE `SiteId` = 31
        AND `TransactionDate` = '2018-06-08') AS `d`
        GROUP BY TransactionDate, TimeGrouping
ORDER BY HourEndTime;

存储过程中的查询：

CREATE DEFINER=`user`@`%` PROCEDURE `TransactionGet`(siteId varchar(10),
                                    transactionDate Date)
BEGIN

# Query will remove duplicate records from the aggregated data
SELECT  `d`.`SiteId`, `d`.`TransactionDate`, (`d`.`TimeGrouping` * 10000) as 'HourEndTime', Sum(`d`.`SalesAmount`) as 'TotalSales', count(`d`.`PosTransactionId`) as 'TransactionCount', Sum(`d`.`ItemCount`) as 'ItemCount', `d`.`Currency`
FROM    (SELECT DISTINCT `SiteId`, `TransactionDate`, `TimeGrouping`, `SalesAmount`, `PosTransactionId`, `ItemCount`, `Currency`
        FROM PosSalesDev.Transaction
        WHERE `SiteId` = siteId
        AND `TransactionDate` = transactionDate) AS `d`
GROUP BY TransactionDate, TimeGrouping;

END

任何帮助将非常感激。

谢谢乔迪。

标签： mysqlmysql-workbench

Answer 1

避免将参数命名为列名。请参阅13.6.4.2 局部变量范围和解析。

尝试：

DELIMITER //

CREATE DEFINER=`user`@`%` PROCEDURE `TransactionGet`(
  _siteId varchar(10),
  _transactionDate Date
)
BEGIN

# Query will remove duplicate records from the aggregated data
SELECT
  `d`.`SiteId`,
  `d`.`TransactionDate`,
  (`d`.`TimeGrouping` * 10000) as 'HourEndTime',
  Sum(`d`.`SalesAmount`) as 'TotalSales',
  count(`d`.`PosTransactionId`) as 'TransactionCount',
  Sum(`d`.`ItemCount`) as 'ItemCount',
  `d`.`Currency`
FROM
  (SELECT
     DISTINCT `SiteId`,
     `TransactionDate`,
     `TimeGrouping`,
     `SalesAmount`,
     `PosTransactionId`,
     `ItemCount`,
     `Currency`
   FROM
     PosSalesDev.Transaction
   WHERE
     `SiteId` = _siteId AND
     `TransactionDate` = _transactionDate
) AS `d`
GROUP BY
  `d`.`TransactionDate`,
  `d`.`TimeGrouping`;
END//

DELIMITER ;