在我的表中有很多行,我准备了test_result_table:
SELECT * FROM
(
SELECT ID, DATA
FROM table
WHERE DATA = "A" OR DATA = "B" OR DATA = "C"
) AS test_result_table
如何做到这一点:
SELECT * FROM
(
SELECT 1 AS RESULT, ID
FROM test_result_table
WHERE DATA = "A"
UNION
SELECT 2 AS RESULT, ID
FROM test_result_table
WHERE DATA = "B"
UNION
SELECT 3 AS RESULT, ID
FROM test_result_table
WHERE DATA = "C"
) AS result_id
ORDER BY RESULT
我知道如何处理临时表,有更好(更快(的解决方案吗?
我期望(x=一些ID(:
RESULT |ID
1 |x
1 |x
2 |x
2 |x
3 |x
使用函数FIND_IN_SET()
可以获得相同的结果,该函数将返回列RESULT
:的值
SELECT t.*
FROM (
SELECT
FIND_IN_SET(DATA, 'A,B,C') AS RESULT,
ID
FROM tablename
) t
WHERE t.RESULT > 0
ORDER BY t.RESULT
查看简化的演示
我说不出它的效率,但它肯定更简单。