mysql - SQL - 我应该向此 WHERE 子句添加 IF 或 CASE 吗？

Question

我编写了一个如下所示的查询:

SELECT
xx.DATE
xx.TRANSACTION_ID
xx.ID
xx.CODE

FROM TABLE_X xx

WHERE
xx.DATE >= TO_DATE('2015-01-01', 'YYYY-MM-DD')
AND xx.ID IN 
(SELECT
yy.id
FROM TABLE_Y yy
WHERE
yy.status = 'OPEN'
AND yy.type = 1
AND trunc(yy.date) = TO_DATE('{FREE_FORM}', 'YYYY-MM-DD')
)

例如TABLE_X

DATE        TRANSACTION_ID  ID      CODE
1/1/2015    1020            CAT     RED
1/1/2015    1010            CAT     GREEN
1/2/2015    2020            DOG     RED
1/2/2015    2010            DOG     GREEN
1/3/2015    3030            RABBIT  BLUE
1/3/2015    3020            SPIDER  RED
1/3/2015    3010            RABBIT  GREEN
1/4/2015    4020            CAT     RED
1/4/2015    4010            SNAKE   GREEN
1/4/2015    4000            MONKEY  ORANGE
1/5/2015    5020            HORSE   RED
1/5/2015    5010            COW     GREEN

例如TABLE_Y

date        id      status  type
1/1/2015    CAT     OPEN    1
1/2/2015    DOG     OPEN    1
1/3/2015    RABBIT  OPEN    1

通过这些示例表，我当前的输出是:

例如输出

DATE        TRANSACTION_ID  ID      CODE
1/1/2015    1020            CAT     RED
1/1/2015    1010            CAT     GREEN
1/2/2015    2020            DOG     RED
1/2/2015    2010            DOG     GREEN
1/3/2015    3030            RABBIT  BLUE
1/3/2015    3010            RABBIT  GREEN
1/4/2015    4020            CAT     RED

我想在 WHERE 子句中添加一些内容，例如:

if (ex.output.CODE = RED){
SELECT
xx.DATE
xx.TRANSACTION_ID
xx.ID
xx.CODE

FROM TABLE_X xx

WHERE xx.TRANSACTION_ID = ex.output.TRANSACTION_ID - 10
}
AND
if (ex.output.CODE = GREEN){
SELECT
xx.DATE
xx.TRANSACTION_ID
xx.ID
xx.CODE

FROM TABLE_X xx

WHERE xx.TRANSACTION_ID = ex.output.TRANSACTION_ID + 10
}

我的目标是最终得到以下输出:

DATE        TRANSACTION_ID  ID      CODE
1/1/2015    1020            CAT     RED
1/1/2015    1010            CAT     GREEN
1/2/2015    2020            DOG     RED
1/2/2015    2010            DOG     GREEN
1/3/2015    3030            RABBIT  BLUE
1/3/2015    3020            SPIDER  RED
1/3/2015    3010            RABBIT  GREEN
1/4/2015    4020            CAT     RED
1/4/2015    4010            SNAKE   GREEN

这可能吗？

Answer 1

尝试这个查询(用 MS T-SQL 编写)

WITH    CTE
          AS ( SELECT   xx.DATE ,
                        xx.TRANSACTION_ID ,
                        xx.ID ,
                        xx.CODE
               FROM     TABLE_X xx
               WHERE    xx.DATE >= '2015-01-01'
                        AND xx.ID IN ( SELECT   yy.id
                                       FROM     TABLE_Y yy
                                       WHERE    yy.status = 'OPEN'
                                                AND yy.type = 1 )
             )
    SELECT  *
    FROM    CTE
    UNION
    SELECT  xx.*
    FROM    TABLE_X xx
            JOIN CTE ON xx.Transaction_ID = CTE.Transaction_ID - 10
                        AND CTE.Code = 'RED'
    UNION
    SELECT  xx.*
    FROM    TABLE_X xx
            JOIN CTE ON xx.Transaction_ID = CTE.Transaction_ID + 10
                        AND CTE.Code = 'GREEN'
    ORDER BY Date ,
            ID;

上述查询将提供输出