In another answer I've spotted a weird syntax:
在另一个答案中,我发现了一个奇怪的语法:
(SELECT * FROM `articles`
WHERE date >= UNIX_TIMESTAMP(DATE(NOW() - INTERVAL 30 DAY))
ORDER BY `views` DESC
LIMIT 20
) ORDER by `views` ASC
which was executed by mysql well though.
虽然很好地执行了mysql。
Why I think it should fail:
为什么我认为它应该失败:
- The subquery doesn't have alias
- The whole query lacks
SELECT
clause
子查询没有别名
整个查询缺少SELECT子句
I find it unexpected to run and don't have an explanation why it works.
我发现运行意外,并没有解释为什么它的工作原理。
It does not fit the grammar defined on https://dev.mysql.com/doc/refman/5.5/en/select.html
它不符合https://dev.mysql.com/doc/refman/5.5/en/select.html上定义的语法
So, why is it valid? Any references?
那么,为什么它有效?任何参考?
2 个解决方案
#1
6
It's the alternative UNION
syntax with a final ORDER BY
.
它是具有最终ORDER BY的替代UNION语法。
This is what such a union between two selects looks like:
这就是两个选择之间的这种联合看起来像:
(SELECT ...)
UNION
(SELECT ...) ORDER BY ... LIMIT ...
And this is what such a union between one select looks like:
这就是一个选择之间的这种联合看起来像:
(SELECT ...) ORDER BY ... LIMIT ...
Not related to subqueries at all.
根本与子查询无关。
This isn't documented in MySQL, but is obvious from the grammar:
这在MySQL中没有记载,但从语法中可以明显看出:
top_level_select_init:
SELECT_SYM
{
Lex->sql_command= SQLCOM_SELECT;
}
select_init2
| '(' select_paren ')' union_opt
;
/* Need select_init2 for subselects. */
union_select_init:
SELECT_SYM select_init2
| '(' select_paren ')' union_opt
;
...
union_opt:
/* Empty */ { $$= 0; }
| union_list { $$= 1; }
| union_order_or_limit { $$= 1; }
;
#2
1
The syntax is useful when you want to sort the end result of the UNION.
当您想要对UNION的最终结果进行排序时,语法很有用。
The following would sort only the last SELECT:
以下将仅排序最后一个SELECT:
SELECT …
UNION
SELECT …
UNION
SELECT … ORDER BY views
But this would sort the whole result:
但这会对整个结果进行排序:
(SELECT …)
UNION
(SELECT …)
UNION
(SELECT …) ORDER BY views
You're doing something like this query, but you've got just one SELECT.
你正在做这样的查询,但你只有一个SELECT。
#1
6
It's the alternative UNION
syntax with a final ORDER BY
.
它是具有最终ORDER BY的替代UNION语法。
This is what such a union between two selects looks like:
这就是两个选择之间的这种联合看起来像:
(SELECT ...)
UNION
(SELECT ...) ORDER BY ... LIMIT ...
And this is what such a union between one select looks like:
这就是一个选择之间的这种联合看起来像:
(SELECT ...) ORDER BY ... LIMIT ...
Not related to subqueries at all.
根本与子查询无关。
This isn't documented in MySQL, but is obvious from the grammar:
这在MySQL中没有记载,但从语法中可以明显看出:
top_level_select_init:
SELECT_SYM
{
Lex->sql_command= SQLCOM_SELECT;
}
select_init2
| '(' select_paren ')' union_opt
;
/* Need select_init2 for subselects. */
union_select_init:
SELECT_SYM select_init2
| '(' select_paren ')' union_opt
;
...
union_opt:
/* Empty */ { $$= 0; }
| union_list { $$= 1; }
| union_order_or_limit { $$= 1; }
;
#2
1
The syntax is useful when you want to sort the end result of the UNION.
当您想要对UNION的最终结果进行排序时,语法很有用。
The following would sort only the last SELECT:
以下将仅排序最后一个SELECT:
SELECT …
UNION
SELECT …
UNION
SELECT … ORDER BY views
But this would sort the whole result:
但这会对整个结果进行排序:
(SELECT …)
UNION
(SELECT …)
UNION
(SELECT …) ORDER BY views
You're doing something like this query, but you've got just one SELECT.
你正在做这样的查询,但你只有一个SELECT。