问题描述
我使用用户变量进行仿真
Im using user variables to emulate
ROW_NUMBER() OVER (PARTITION BY `wsf_ref`, `type` ORDER BY `wsf_value` DESC)
请注意@type变量.我将其设置为a以使问题明确，但起初是一个空字符串.
Pay attention to the @type variable. I set it to a to make the issue clear but at first was an empty string.
CROSS JOIN ( SELECT @rn := 0, @type := 'a', @ref := '') as var
CREATE TABLE t (
`id` INTEGER,
`wsf_ref` INTEGER,
`status` VARCHAR(8),
`type` VARCHAR(6),
`wsf_progress` VARCHAR(5),
`wsf_value` INTEGER
);
SELECT t.*, @rn := if( @ref = `wsf_ref`,
if ( @type = `type`,
@rn + 1,
if( @type := `type`, 1, 1)
),
if ( (@ref := `wsf_ref`) and (@type := `type`), 1, 1)
) as rn,
@type,
@ref
FROM t
CROSS JOIN ( SELECT @rn := 0, @type := 'a', @ref := '') as var
ORDER BY `wsf_ref`, `type`, `wsf_value` DESC;
您可以看到第一行输入了最后一个条件，并将两个变量都设置为正确:
You can see first row enter the last condition and set both variable correct:
输出
| id | wsf_ref | status | type | wsf_progress | wsf_value | rn | @type | @ref |
|----|---------|----------|--------|--------------|-----------|----|--------|------|
| 6 | 1 | Approved | blue | Day 1 | 25 | 1 | blue | 1 |
| 5 | 1 | Approved | blue | Day 1 | 10 | 2 | blue | 1 |
| 3 | 1 | Approved | orange | Day 1 | 20 | 1 | orange | 1 |
如果wsf_ref是VARCHAR，则错误，我得到了不同的结果
Buf if wsf_ref is a VARCHAR i got a different result
CREATE TABLE t (
`id` INTEGER,
`wsf_ref` VARCHAR(255),
`status` VARCHAR(255),
`type` VARCHAR(255),
`wsf_progress` VARCHAR(5),
`wsf_value` INTEGER
);
在这里您可以看到第一行未设置变量@type并仍然具有a
Here you can see first row the variable @type isnt set and still have a
输出
| id | wsf_ref | status | type | wsf_progress | wsf_value | rn | @type | @ref |
|----|----------|----------|--------|--------------|-----------|----|--------|----------|
| 3 | WSF19-01 | Approved | Perch | Day 2 | 20 | 1 | a | WSF19-01 |
| 4 | WSF19-01 | Approved | Perch | Day 2 | 10 | 1 | Perch | WSF19-01 |
经过一些调试后，我发现问题出在最后一次分配上
After some debuging I found the problem is with the last assignment
if ( (@ref := `wsf_ref`) and (@type := `type`), 1, 1)
在第一种情况下，当wsf_ref为整数时，赋值评估为true，然后还检查第二种条件.在第二种情况下，当wsf_ref为字符串时，结果为false，并且第二个条件被忽略.
On first case when wsf_ref is integer the assignment evaluate to true and then the second condition is also checked. On the second case when wsf_ref is string the result is false and the second condition is ignored.
我将条件更改为:
if ( (@ref := `wsf_ref`) OR (@type := `type`), 1, 1)
因此，即使第一个条件为假，仍然尝试评估第二个条件，现在两个查询都可以正常工作.
So even if the first condition is false still try to evaluate the second condition and now both query are working ok.
那为什么给@ref分配一个数字而不是给一个字符串分配一个布尔值?
So why assign @ref a number get a different boolean than assign a string?
推荐答案
您的表达式是:
if ( (@ref := `wsf_ref`) and (@type := `type`), 1, 1)
MySQL不必同时评估这两种情况.如果第一个"的计算结果为true，则仅需要评估第二个"的计算结果. (我将第一"和第二"用引号引起来，因为未确定评估顺序，但是无论如何，想法都是相同的.)
MySQL does not necessarily evaluate both conditions. It only needs to evaluate the "second" one if the "first" evaluates to true. (I put "first" and "second" in quotes because the order of evaluation is not determined, but the idea is the same regardless.)
当这些是字符串时，@ref := wsf_rf的结果是一个字符串.字符串通过数字转换为布尔值.值为0-这是错误的-除非字符串碰巧以数字开头.
When these are strings, the result of @ref := wsf_rf is a string. The string is converted to a boolean, via a number. The value is 0 -- which is false -- unless the string happens to start with digit.
因此，两个条件都不会评估，第二个条件也不会分配.
Hence, both conditions are not evaluated and the second is not assigned.
我会这样写:
SELECT t.*,
(@rn := if(@tr = CONCAT_WS(':', wsf_ref, type),
@rn + 1,
if(@tr := CONCAT_WS(':', wsf_ref, type), 1, 1
)
)
) as rn
FROM (SELECT t.*
FROM t
ORDER BY `wsf_ref`, `type`, `wsf_value` DESC
) t CROSS JOIN
(SELECT @rn := 0, @tr := '') params;
我将ORDER BY移到了子查询，因为更新的MySQL版本不能很好地处理ORDER BY和变量.
I moved the ORDER BY to a subquery because more recent versions of MySQL don't handle ORDER BY and variables very well.
这篇关于返回的布尔值将整数或字符串分配给变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！