带有内部连接的 mySQL 聚合函数

Question

我有以下表格:

CREATE TABLE `funds_balance` (
  `idBUSINESS` int(11) NOT NULL,
  `PREMIUM_POSITIONS_CREDIT` decimal(18,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`idBUSINESS`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `elevated_business_queue` (
  `idBUSINESS` int(11) NOT NULL,
  `KEYWORD_TEXT` varchar(200) NOT NULL,
  `CITY` varchar(50) NOT NULL,
  `BID_AMOUNT` decimal(18,2) NOT NULL,
  `NO_OF_DAYS` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

我正在尝试从 elevated_business_queue 表中选择具有足够资金来支付 funds_balance 表中所有出价的企业。我提出了以下查询:

SELECT ebq.idBUSINESS
     , KEYWORD_TEXT
     , CITY
     , BID_AMOUNT 
  FROM elevated_business_queue ebq
  JOIN funds_balance fb 
    ON fb.idBUSINESS = ebq.idBUSINESS
 GROUP 
    BY idBUSINESS
HAVING PREMIUM_POSITIONS_CREDIT >= (SUM(ebq.BID_AMOUNT) + (ROUND((12.36/100)*SUM(ebq.BID_AMOUNT)), 2));

但它不起作用。我收到错误未知列 PREMIUM_POSITIONS_CREDIT。为什么？

Answer 1

HAVING 子句过滤在查询序列的“末尾”，并且只能作用于查询中实际选择的字段。由于您没有在字段列表中包含 PREMIUM_POSTIIONS_CREDIT，因此它不存在于结果集中，因此 HAVING 无法对其进行过滤。

简单地做

SELECT PREMIUM_POSITIONS_CREDIT, rest,of,the,fields

它会开始工作。

例如

mysql> CREATE TABLE foo (int x, int y);
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO foo (x,y) VALUES (1,1);
Query OK, 1 row affected (0.05 sec)
Records: 1 Duplicates: 0  Warnings: 0

mysql> SELECT x FROM foo HAVING y=1
ERROR 1054 (42S22): Unknown column 'y' in 'having clause'

mysql> select x,y from foo having y=1;
+------+------+
| x    | y    |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)