I'd like to select all the rows from a temporary table that have a value equal to the maximum value.
我想从临时表中选择值等于最大值的所有行。
TEMPORARY TABLE test
id | value
1 | 7
2 | 6
3 | 7
Expected result:
id | value
1 | 7
3 | 7
I've already tried several things but all attempts (except creating another temporary table with the same content) failed so far because of "You cannot refer to a TEMPORARY table more than once in the same query." (Source: http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html). Is there a possibility to do this in a more "elegant" way than copying the table and its contents?
我已经尝试了几件事但是所有尝试(除了创建具有相同内容的另一个临时表)都失败了,因为“你不能在同一个查询中多次引用TEMPORARY表”。 (来源:http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html)。是否有可能以比复制表格及其内容更“优雅”的方式执行此操作?
What I've tested so far:
到目前为止我测试了什么:
SELECT table1.* FROM test table1 LEFT JOIN test table2 ON table1.value < table2.value
WHERE table2.value IS NULL;
SELECT * FROM test WHERE value = (SELECT MAX(value) FROM test);
And this is my current solution:
这是我目前的解决方案:
CREATE TEMPORARY TABLE test2 (...);
INSERT INTO test2 SELECT * FROM test;
SELECT * FROM test WHERE value = (SELECT MAX(value) FROM test2);
3 个解决方案
#1
2
You could try using variables. The following will scan the test table only once (not-tested):
你可以尝试使用变量。以下内容仅扫描测试表一次(未测试):
SELECT test.*,
@curMax := IF(@curMax = -1, value, @curMax)
FROM test,
(SELECT @curMax := -1) r
HAVING @curMax = value OR @curMax = -1
ORDER BY value DESC;
#2
1
How about this:
这个怎么样:
SELECT MAX(value) INTO @max
FROM test;
SELECT *
FROM test
WHERE value = @max;
#3
0
Select id,value From SomeTable
inner Join (Select Max(SomeValue) as MaximumValue From SomeTable) maximums
On SomeTable.Value = maximums.MaximumValue
Should do the job
应该做的工作
#1
2
You could try using variables. The following will scan the test table only once (not-tested):
你可以尝试使用变量。以下内容仅扫描测试表一次(未测试):
SELECT test.*,
@curMax := IF(@curMax = -1, value, @curMax)
FROM test,
(SELECT @curMax := -1) r
HAVING @curMax = value OR @curMax = -1
ORDER BY value DESC;
#2
1
How about this:
这个怎么样:
SELECT MAX(value) INTO @max
FROM test;
SELECT *
FROM test
WHERE value = @max;
#3
0
Select id,value From SomeTable
inner Join (Select Max(SomeValue) as MaximumValue From SomeTable) maximums
On SomeTable.Value = maximums.MaximumValue
Should do the job
应该做的工作