BigQuery，如何在where子句中使用别名? [英] BigQuery, how to use alias in where clause?

问题描述

我有这个小查询，BigQuery无法识别别名maxdate，我试图返回表，但仅返回最后一个日期

I have this small query, BigQuery don't recognise the alias maxdate, I am trying to return the table but only for that last date

WITH
  maxdate AS (
  SELECT
    MAX(DATE(SETTLEMENTDATE))
  FROM
    `biengine-252003.aemo2.daily`)
SELECT
  *
FROM
  `biengine-252003.aemo2.daily`
WHERE
  DATE(SETTLEMENTDATE)= maxdate

推荐答案

您正试图从表maxdate中获取maxdate的值.将别名添加到您的列中并使用它.

You are trying to get the value of maxdate from your table maxdate. Add alias to your column and use this.

WITH
  maxdate AS (
  SELECT
    MAX(DATE(SETTLEMENTDATE)) as mx
  FROM
    `biengine-252003.aemo2.daily`)
SELECT
  *
FROM
  `biengine-252003.aemo2.daily`
WHERE
  DATE(SETTLEMENTDATE) = (SELECT mx FROM maxdate LIMIT 1)

这篇关于BigQuery，如何在where子句中使用别名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！