找到你要的答案

Q:How to use subquery table alias in WHERE clause in MySQL

Q:如何使用子查询的WHERE子句中MySQL表别名

SELECT invoice_id, sum_amount
FROM (SELECT invoice_id, SUM(amount) AS sum_amount
      FROM invoice
      GROUP BY invoice_id) AS TEMP_TABLE
WHERE sum_amount in (SELECT MAX(sum_amount) 
                     FROM TEMP_TABLE); 

When I tried to use the TEMP_TABLE, an error occurred and said TEMP_TABLE doesn't exist. Why doesn't it work? I think the execution order is "FROM" then "WHERE", the table alias has been created at that time.

SELECT invoice_id, sum_amount
FROM (SELECT invoice_id, SUM(amount) AS sum_amount
      FROM invoice
      GROUP BY invoice_id) AS TEMP_TABLE
WHERE sum_amount in (SELECT MAX(sum_amount) 
                     FROM TEMP_TABLE); 

当我试图用temp_table,出现了一个错误,说temp_table不存在。为什么不工作?我认为执行顺序是“从”然后“哪里”,表别名被创建在那个时候。

answer1: 回答1:

You can't calculate max for the previously calculated alias in sub query alternatively you can rewrite your query as

SELECT a.invoice_id, SUM(a.amount) AS sum_amount,
c.max_amount
FROM invoice AS a
CROSS JOIN (
      SELECT MAX(sum_amount) max_amount FROM(
          SELECT invoice_id, SUM(amount) AS sum_amount
          FROM invoice
          GROUP BY invoice_id
      ) b
) c
GROUP BY a.invoice_id
HAVING sum_amount = c.max_amount

I have created a sample demo for testing purpose

Sample data

INSERT INTO invoice
    (`invoice_id`, `amount`)
VALUES
    (1, 10),
    (2, 5),
    (2, 5),
    (3, 1),
    (4, 2);

In above query you can see maximum sum of amount for invoice is 10 so invoice_id =>1,2 should be returned 1 has amount of 10 and two entries for invoice_id => 2 (5+5 = 10) is also 10 ,3 and 4 should not be included in result set sample output

invoice_id  sum_amount  max_amount
1           10          10
2           10          10

DEMO

您不能计算在子查询中先前计算的别名的最大值,或者您可以重写您的查询

SELECT a.invoice_id, SUM(a.amount) AS sum_amount,
c.max_amount
FROM invoice AS a
CROSS JOIN (
      SELECT MAX(sum_amount) max_amount FROM(
          SELECT invoice_id, SUM(amount) AS sum_amount
          FROM invoice
          GROUP BY invoice_id
      ) b
) c
GROUP BY a.invoice_id
HAVING sum_amount = c.max_amount

我创建了一个用于测试目的的示例演示程序

样本数据

INSERT INTO invoice
    (`invoice_id`, `amount`)
VALUES
    (1, 10),
    (2, 5),
    (2, 5),
    (3, 1),
    (4, 2);

在上面的查询可以看到发票金额最大的和是10,invoice_id = & gt;1,2应该返回1已invoice_id = & gt 10两项金额;2(5 + 5 = 10)为10,3和4不应包括在结果集的示例输出

invoice_id  sum_amount  max_amount
1           10          10
2           10          10

演示

mysql  subquery