Mariadb Subqueries in a FROM Clause

SELECT ID from (SELECT * FROM notice ORDER BY REGDATE DESC) as t
WHERE REGDATE < (SELECT REGDATE FROM notice WHERE ID = 5 )
AND ROWNUM() = 1

 

중복되는 정의가 있으면 "as 이름"을 붙여야 결과가 나옴

아마도 동일한 select가 실행되서 이름을 붙여서 구분하는 듯...

안되는 이유를 몰라 엄청 해맴

 

여기서 보고 해결함

https://mariadb.com/kb/en/subqueries-in-a-from-clause/

Subqueries in a FROM Clause

Although subqueries are more commonly placed in a WHERE clause, they can also form part of the FROM clause. Such subqueries are commonly called derived tables.

If a subquery is used in this way, you must also use an AS clause to name the result of the subquery.

ORACLE mode

MariaDB starting with 10.6.0

From MariaDB 10.6.0, anonymous subqueries in a FROM clause (no AS clause) are permitted in ORACLE mode.

Examples

CREATE TABLE student (name CHAR(10), test CHAR(10), score TINYINT); 

INSERT INTO student VALUES 
  ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73), 
  ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31), 
  ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88), 
  ('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83);

Assume that, given the data above, you want to return the average total for all students. In other words, the average of Chun's 148 (75+73), Esben's 74 (43+31), etc.

You cannot do the following:

SELECT AVG(SUM(score)) FROM student GROUP BY name;
ERROR 1111 (HY000): Invalid use of group function

A subquery in the FROM clause is however permitted:

SELECT AVG(sq_sum) FROM (SELECT SUM(score) AS sq_sum FROM student GROUP BY name) AS t;
+-------------+
| AVG(sq_sum) |
+-------------+
|    134.0000 |
+-------------+

From MariaDB 10.6 in ORACLE mode, the following is permitted:

SELECT * FROM (SELECT 1 FROM DUAL), (SELECT 2 FROM DUAL);