mysql查询，使用结果作为变量

I need a query like following that doen not work:

我需要一个像下面这样的查询,它不起作用:

SELECT *, 
 CASE 
  WHEN x_sutun1 = '1' OR x_sutun2 >= CURRENT_DATE() THEN 0 
  WHEN x_sutun1 < 1 AND x_sutun2 < CURRENT_DATE() THEN 1 
 END as mecit
FROM tablo1
LEFT JOIN tablo2 ON x_sutun0 = y_sutun0
LEFT JOIN tablo3 ON x_sutun0 = z_sutun0
WHERE x_sutun3 + mecit < 4

How can I use 'mecit' as a variable (without stored procedure if possible).

如何使用'mecit'作为变量(如果可能,不使用存储过程)。

2 个解决方案

#1

Wrap your query in an outer query like this:

将查询包装在外部查询中,如下所示:

select * from
(
SELECT 
 *
 , CASE 
  WHEN x_sutun1 = '1' OR x_sutun2 >= CURRENT_DATE() THEN 0 
  WHEN x_sutun1 < 1 AND x_sutun2 < CURRENT_DATE() THEN 1 
 END as mecit
FROM tablo1
) m
LEFT JOIN tablo2 ON x_sutun0 = y_sutun0
LEFT JOIN tablo3 ON x_sutun0 = z_sutun0
WHERE x_sutun3 + m.mecit < 4

The reason for this is that the alias you use is not visible until the SELECT has been resolved, which is the last (or almost) last part of the query to be looked at by the engine.

这样做的原因是,在解析SELECT之前,您使用的别名是不可见的,这是引擎要查看的查询的最后(或几乎)最后一部分。

Note: you'll need to qualify column names if you have name collisions over the tables you are using.

注意:如果您正在使用的表上存在名称冲突,则需要对列名进行限定。

#2

As documented under Problems with Column Aliases:

如列别名问题中所述:

An alias can be used in a query select list to give a column a different name. You can use the alias in GROUP BY, ORDER BY, or HAVING clauses to refer to the column:

可以在查询选择列表中使用别名为列提供不同的名称。您可以使用GROUP BY,ORDER BY或HAVING子句中的别名来引用该列:
[ deletia ]
Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined.

标准SQL不允许在WHERE子句中引用列别名。强制执行此限制是因为在评估WHERE子句时,可能尚未确定列值。

Therefore you cannot reference mecit by alias in the WHERE clause. Whilst you could move the filter to a HAVING clause (or an outer query), that's generally not very efficient (as records cannot be filtered until all results have been computed and returned): you're better off just repeating the expression in the WHERE clause directly (more verbose, but more performant):

因此,您无法在WHERE子句中通过别名引用mecit。虽然您可以将过滤器移动到HAVING子句(或外部查询),但这通常效率不高(因为在计算并返回所有结果之前无法过滤记录):您最好只重复WHERE中的表达式直接的子句(更详细,但更高效):

SELECT *, 
 CASE 
  WHEN x_sutun1 = '1' OR x_sutun2 >= CURRENT_DATE() THEN 0 
  WHEN x_sutun1 <  1 AND x_sutun2 <  CURRENT_DATE() THEN 1 
 END as mecit
FROM tablo1
LEFT JOIN tablo2 ON x_sutun0 = y_sutun0
LEFT JOIN tablo3 ON x_sutun0 = z_sutun0
WHERE x_sutun3 + CASE 
  WHEN x_sutun1 = '1' OR x_sutun2 >= CURRENT_DATE() THEN 0 
  WHEN x_sutun1 <  1 AND x_sutun2 <  CURRENT_DATE() THEN 1 
 END < 4

#1