mysql - 使用 Case(或其他方法)使用查询中的值更改查询的 WHERE 子句

Question

假设我们有 4 张表

TableA
A_Id    int
ValA1   int
ValA2   int
ValA3   int

TableB
B_Id    int
ValB1   int
ValB2   int
ValB3   int 


TableC
C_Id    int
A_Id    int
B_Id    int
D_Id    int
Type    Varchar(1)


TableD
D_Id    int
ValD    Varchar(50)

“用户输入”是 $A、$B 和 $D，

我想要的是以下结构:

If (TableC.Type == 'A' AND  TableA.ValA1 == $A AND TableD.ValD == $D)
{
    Return TableA.ValA2, TableA.ValA3, TableD.ValD
}
Else If(TableC.Type == 'B' AND  TableB.ValB1 == $B AND TableD.ValD == $D)
{
    Return TableB.ValB2, TableB.ValB3, TableD.ValD
}
Else If((TableC.Type == 'C' OR TableC.Type == 'D') AND TableD.ValD == $D)
{
    Return TableA.ValA2, TableA.ValA3, TableB.ValB2, TableB.ValB3, TableD.ValD
}

这可能吗？如果是这样，正确的语法是什么？

Answer 1

    SELECT TableA.ValA2 AS field1, TableA.ValA3 AS field2, TableD.ValD AS field3, NULL AS field4, NULL AS field5
    FROM (joined tableset)
    WHERE (TableC.Type == 'A' AND  TableA.ValA1 == $A AND TableD.ValD == $D)
UNION ALL
    SELECT TableB.ValB2, TableB.ValB3, TableD.ValD, NULL, NULL
    FROM (joined tableset)
    WHERE (TableC.Type == 'B' AND  TableB.ValB1 == $B AND TableD.ValD == $D)
UNION ALL
    SELECT TableA.ValA2, TableA.ValA3, TableB.ValB2, TableB.ValB3, TableD.ValD
    FROM (joined tableset)
    WHERE ((TableC.Type == 'C' OR TableC.Type == 'D') AND TableD.ValD == $D)

一般来说，WHERE 中的条件必须没有交叉潜力(在您的情况下满足此条件)。如果不是，则每个下一个条件必须附有 AND NOT(先前条件)。