仅在mysql的IN子句中显示指定的id

Question

    SELECT * FROM `charges`
    UNION ALL
    SELECT * FROM `confinement`
    UNION ALL
    SELECT * FROM `ultrasound` 
WHERE procedure_no IN (100, 200)
ORDER BY FIELD(procedure_no,100,200);

THe命令的第一部分是我要连接3个费用表的地方。它们都是相同的，只是上下文不同。我希望该命令仅显示过程编号100和200。所以只有2个结果。但它在mysql中显示了很多结果。

这是结果http://i.stack.imgur.com/2STXD.png

Answer 1

您发布的查询实质上执行三个查询：

SELECT * FROM `charges`

SELECT * FROM `confinement`


SELECT * FROM `ultrasound` WHERE procedure_no IN (100, 200) ORDER BY FIELD(procedure_no,100,200);

也就是说，目前只有超声波被程序编号过滤。尝试(未测试)：

SELECT * FROM
    (SELECT * FROM `charges`
    UNION ALL
    SELECT * FROM `confinement`
    UNION ALL
    SELECT * FROM `ultrasound` ) AS t1
WHERE t1.procedure_no IN (100, 200)
ORDER BY FIELD(t1.procedure_no,100,200);

基本SQL fiddle在这里：http://sqlfiddle.com/#!2/e1f08/1