SQL：在TEMP表中使用UNION(SQL: Using UNION Inside a TEMP Table)

SQL：在TEMP表中使用UNION(SQL: Using UNION Inside a TEMP Table)

课程问题所以请不要给我答案！

我正在尝试比较去年的销售额与今年的销售情况以及我正在使用的方法是使用临时表，其中包含由联合加入的2个Select语句

SELECT theatreNo, SUM(lastMay) AS lastMay, SUM(thisMay) AS thisMay FROM( SELECT theatreNo, COUNT(*) AS lastMay FROM Hospital_Operation WHERE year(startDateTime) = '2015' and month(startDateTime) = '05' GROUP BY theatreNo, DATE_FORMAT(startDateTime, '%d%c%y') UNION ALL SELECT theatreNo, COUNT(*) AS thisMay FROM Hospital_Operation WHERE year(startDateTime) = '2016' and month(startDateTime) = '05' GROUP BY theatreNo, DATE_FORMAT(startDateTime, '%d%c%y') )AS Temp GROUP BY theatreNo;

我收到错误：字段名称中的未知列“thismay”

有人可以向我解释为什么我会收到此错误吗？ 我理解的方式是我加入表并将其存储在TEMP表中。

Coursework question so please don't give me the answer!

I'm trying to compare last years sales to this years sales and the approach I'm using is using a temp table which contains 2 Select statements joined by a union

SELECT theatreNo, SUM(lastMay) AS lastMay, SUM(thisMay) AS thisMay FROM( SELECT theatreNo, COUNT(*) AS lastMay FROM Hospital_Operation WHERE year(startDateTime) = '2015' and month(startDateTime) = '05' GROUP BY theatreNo, DATE_FORMAT(startDateTime, '%d%c%y') UNION ALL SELECT theatreNo, COUNT(*) AS thisMay FROM Hospital_Operation WHERE year(startDateTime) = '2016' and month(startDateTime) = '05' GROUP BY theatreNo, DATE_FORMAT(startDateTime, '%d%c%y') )AS Temp GROUP BY theatreNo;

I'm getting the error: Unknown column "thismay" in field name

Could someone explain to me why I am getting this error? The way my understanding is that I am joining to tables and storing it in the TEMP table.

最满意答案

首先，在这里给你一个方法。

SELECT theatreNo, SUM(lastMay) AS lastMay, SUM(thisMay) AS thisMay FROM( SELECT theatreNo, COUNT(*) AS lastMay, 0 AS thisMay FROM Hospital_Operation WHERE year(startDateTime) = '2015' and month(startDateTime) = '05' GROUP BY theatreNo, DATE_FORMAT(startDateTime, '%d%c%y') UNION ALL SELECT theatreNo, 0 AS lastMay, COUNT(*) AS thisMay FROM Hospital_Operation WHERE year(startDateTime) = '2016' and month(startDateTime) = '05' GROUP BY theatreNo, DATE_FORMAT(startDateTime, '%d%c%y') )AS Temp GROUP BY theatreNo;

然后让我们做一些解释：

在你的Temp查询中，你使用UNION ALL ，第一个查询将决定此查询结果中的列名，因此temp只生成列theatreNo和lastMay ，然后在第二个查询中， thisMay将被第一个查询的lastMay ，所以当你在外部查询中选择thisMay列，它会导致未知列“thismay” 。

这是关于UNION的官方文件：

第一个SELECT语句中的列名称用作返回结果的列名称。 在每个SELECT语句的相应位置中列出的选定列应具有相同的数据类型。 （例如，第一个语句选择的第一列应该与其他语句选择的第一列具有相同的类型。）

Firstly, give you a approach here.

SELECT theatreNo, SUM(lastMay) AS lastMay, SUM(thisMay) AS thisMay FROM( SELECT theatreNo, COUNT(*) AS lastMay, 0 AS thisMay FROM Hospital_Operation WHERE year(startDateTime) = '2015' and month(startDateTime) = '05' GROUP BY theatreNo, DATE_FORMAT(startDateTime, '%d%c%y') UNION ALL SELECT theatreNo, 0 AS lastMay, COUNT(*) AS thisMay FROM Hospital_Operation WHERE year(startDateTime) = '2016' and month(startDateTime) = '05' GROUP BY theatreNo, DATE_FORMAT(startDateTime, '%d%c%y') )AS Temp GROUP BY theatreNo;

then let's do some explanation:

In your Temp query, you used UNION ALL, the first query will decide the column name in this query's result, so temp only produce column theatreNo and lastMay, then in second query, thisMay will be overridden by the first query's lastMay, so when you select column thisMay in external query, it caused Unknown column "thismay".

Here is the official doc about UNION:

The column names from the first SELECT statement are used as the column names for the results returned. Selected columns listed in corresponding positions of each SELECT statement should have the same data type. (For example, the first column selected by the first statement should have the same type as the first column selected by the other statements.)

SQL：在TEMP表中使用UNION(SQL: Using UNION Inside a TEMP Table)

课程问题所以请不要给我答案！

我正在尝试比较去年的销售额与今年的销售情况以及我正在使用的方法是使用临时表，其中包含由联合加入的2个Select语句

SELECT theatreNo, SUM(lastMay) AS lastMay, SUM(thisMay) AS thisMay FROM( SELECT theatreNo, COUNT(*) AS lastMay FROM Hospital_Operation WHERE year(startDateTime) = '2015' and month(startDateTime) = '05' GROUP BY theatreNo, DATE_FORMAT(startDateTime, '%d%c%y') UNION ALL SELECT theatreNo, COUNT(*) AS thisMay FROM Hospital_Operation WHERE year(startDateTime) = '2016' and month(startDateTime) = '05' GROUP BY theatreNo, DATE_FORMAT(startDateTime, '%d%c%y') )AS Temp GROUP BY theatreNo;

我收到错误：字段名称中的未知列“thismay”

有人可以向我解释为什么我会收到此错误吗？ 我理解的方式是我加入表并将其存储在TEMP表中。

Coursework question so please don't give me the answer!

I'm trying to compare last years sales to this years sales and the approach I'm using is using a temp table which contains 2 Select statements joined by a union

SELECT theatreNo, SUM(lastMay) AS lastMay, SUM(thisMay) AS thisMay FROM( SELECT theatreNo, COUNT(*) AS lastMay FROM Hospital_Operation WHERE year(startDateTime) = '2015' and month(startDateTime) = '05' GROUP BY theatreNo, DATE_FORMAT(startDateTime, '%d%c%y') UNION ALL SELECT theatreNo, COUNT(*) AS thisMay FROM Hospital_Operation WHERE year(startDateTime) = '2016' and month(startDateTime) = '05' GROUP BY theatreNo, DATE_FORMAT(startDateTime, '%d%c%y') )AS Temp GROUP BY theatreNo;

I'm getting the error: Unknown column "thismay" in field name

Could someone explain to me why I am getting this error? The way my understanding is that I am joining to tables and storing it in the TEMP table.

最满意答案

首先，在这里给你一个方法。

SELECT theatreNo, SUM(lastMay) AS lastMay, SUM(thisMay) AS thisMay FROM( SELECT theatreNo, COUNT(*) AS lastMay, 0 AS thisMay FROM Hospital_Operation WHERE year(startDateTime) = '2015' and month(startDateTime) = '05' GROUP BY theatreNo, DATE_FORMAT(startDateTime, '%d%c%y') UNION ALL SELECT theatreNo, 0 AS lastMay, COUNT(*) AS thisMay FROM Hospital_Operation WHERE year(startDateTime) = '2016' and month(startDateTime) = '05' GROUP BY theatreNo, DATE_FORMAT(startDateTime, '%d%c%y') )AS Temp GROUP BY theatreNo;

然后让我们做一些解释：

在你的Temp查询中，你使用UNION ALL ，第一个查询将决定此查询结果中的列名，因此temp只生成列theatreNo和lastMay ，然后在第二个查询中， thisMay将被第一个查询的lastMay ，所以当你在外部查询中选择thisMay列，它会导致未知列“thismay” 。

这是关于UNION的官方文件：

第一个SELECT语句中的列名称用作返回结果的列名称。 在每个SELECT语句的相应位置中列出的选定列应具有相同的数据类型。 （例如，第一个语句选择的第一列应该与其他语句选择的第一列具有相同的类型。）

Firstly, give you a approach here.

SELECT theatreNo, SUM(lastMay) AS lastMay, SUM(thisMay) AS thisMay FROM( SELECT theatreNo, COUNT(*) AS lastMay, 0 AS thisMay FROM Hospital_Operation WHERE year(startDateTime) = '2015' and month(startDateTime) = '05' GROUP BY theatreNo, DATE_FORMAT(startDateTime, '%d%c%y') UNION ALL SELECT theatreNo, 0 AS lastMay, COUNT(*) AS thisMay FROM Hospital_Operation WHERE year(startDateTime) = '2016' and month(startDateTime) = '05' GROUP BY theatreNo, DATE_FORMAT(startDateTime, '%d%c%y') )AS Temp GROUP BY theatreNo;

then let's do some explanation:

In your Temp query, you used UNION ALL, the first query will decide the column name in this query's result, so temp only produce column theatreNo and lastMay, then in second query, thisMay will be overridden by the first query's lastMay, so when you select column thisMay in external query, it caused Unknown column "thismay".

Here is the official doc about UNION:

The column names from the first SELECT statement are used as the column names for the results returned. Selected columns listed in corresponding positions of each SELECT statement should have the same data type. (For example, the first column selected by the first statement should have the same type as the first column selected by the other statements.)