sql-server - 将 IN 与多个链接条件一起使用 - SQL

Question

我的代码目前如下所示...

SELECT DISTINCT
    CLIENT.FIRST_NAME,
    CLIENT.LAST_NAME,
    DATE_DIM.DATE
FROM
    DW.DBO.TEST
WHERE
    CLIENT.ID1 IN (A1, A2, A3, A4, A5)
AND
    DATE_DIM.DATE IN (1/11/2015, 1/12/2015, 1/13/2015, 1/14/2015, 1/15/2015)

我想知道有什么方法可以将这两个条件联系起来吗？ IE。我不想要客户端 id1 A1 链接到 1/12/2015 的结果，因为这导致我必须在输出后进行大量手动编辑。谢谢!

Answer 1

是的，这很简单，只需将其放在 table 上或即时制作即可

SELECT DISTINCT
    CLIENT.FIRST_NAME,
    CLIENT.LAST_NAME,
    DATE_DIM.DATE
FROM DW.DBO.TEST
JOIN CLIENT -- you didn't include this part
JOIN DATE_DIM -- you didn't include this part
JOIN (
   SELECT 'A1', '1/11/2015'
    UNION ALL
   SELECT 'A2', '1/12/2015'
    UNION ALL
   SELECT 'A3', '1/13/2015'
    UNION ALL
   SELECT 'A4', '1/14/2015'
    UNION ALL
   SELECT 'A5', '1/15/2015'
) AS TMP(ID, D) ON CLIENT.ID1 = TMP.ID AND DATE_DIM.DATE = TMP.D

I fixed up your code a little since you included TABLES you did not define and illegal constants -- I just made everything a string for this example.

---

某些平台(DB2 和 Oracle - 我肯定知道)您可以在 IN 子句中使用元组....看起来像这样:

WHERE (CLIENT.ID, DATE_DIM) IN ( -- list of tuples here or select statement)