请主人,我有三个表,我需要使用LEFT JOIN
从中选择数据;并且我需要按字段q(存在于table3
中)进行排序,但仅限于状态= 1(也存在于table3
中)。
我试过这个,但它不起作用:
SELECT * FROM table 1
LEFT JOIN table2 ON x = y
LEFT JOIN table3 ON z=w
WHERE w = 1
ORDER BY q IN ( SELECT q FROM table3 WHERE state = 1);
请提出任何建议?
这有多大:
SELECT * FROM table 1
LEFT JOIN table2 ON x = y
LEFT JOIN table3 ON z=w
WHERE w = 1
ORDER BY case when state=1 then 0 else 1 end,q
试试这个:
SELECT *,
(SELECT q FROM table3 WHERE state = 1) as sort_order
FROM table 1
LEFT JOIN table2 ON x = y
LEFT JOIN table3 ON z=w
WHERE w = 1
ORDER BY sort_order
由于我不知道所需的结果应该是什么,因此这可能是正确的查询:
SELECT *
FROM table1 as t1
LEFT JOIN table2 as t2 ON t1.x = t2.y
LEFT JOIN table3 as t3 ON t2.z = t3.w AND t3.state = 1
WHERE t3.w = 1
ORDER BY t3.q
尝试这样的事情:
SELECT * FROM table 1
LEFT JOIN table2 ON x = y
LEFT JOIN table3 ON z=w
WHERE w = 1 AND table3.state = 1
ORDER BY q