sql - 如何将一对多关系显示为一对一

Question

我正在使用寻呼系统。对于我的一些同事，我需要创建一些目录。目录中的每个人都有一个 PAGER_ID 和一个 MESSAGING_ID。 PAGER_ID 对于给定的寻呼设备是唯一的，而 MESSAGING_ID 对于一个人是唯一的。一个小组可能会轮类工作，并在一个类次之间传递一个设备，导致几个人拥有相同的 PAGER_ID 和不同的 MESSAGING_ID。这很好，是设计使然。

对于我们的目录，用户希望满足以下条件:

1. 每个 PAGER_ID 只显示一次(我知道我可以通过 SELECT TOP 1 到 PAGER_ID 来做到这一点)
2. 当 PAGER_ID 具有一个十位数的 MESSAGING_ID 和一个或多个具有不同位数的其他 MESSAGING_ID 时，首选十位数的 MESSAGING_ID。
3. 如果没有 10 位数字的 MESSAGING_ID，可以使用任何其他数字的 MESSAGING_ID。

简而言之:他们只想查看 PAGER_ID/MESSAGING_ID 的一条记录。他们不关心目录中是否列出了每个人。他们并不真正关心哪个 MESSAGING_ID，只要目录中显示的每个 PAGER_ID 只有一个 MESSAGING_ID，并且只要 10 位 MESSAGING_ID 优先于那些长度不是 10 位的 MESSAGING_ID。

我尝试了 TOP 和 IIF 查询的组合，但无法让它们以所需的方式很好地协同工作。

我的基本选择查询是:

SELECT
    tbl_Amcom_Prod.NAME,
    tbl_Amcom_Prod.PAGER_ID,
    tbl_Amcom_Prod.MESSAGING_ID
FROM tbl_Amcom_Prod
WHERE
    (((tbl_Amcom_Prod.PAGER_ID) Like "241662"
    Or (tbl_Amcom_Prod.PAGER_ID) Like "18888"))
ORDER BY tbl_Amcom_Prod.PAGER_ID;

结果是:

|     NAME       | PAGER_ID | MESSAGING_ID |
--------------------------------------------
| TESTER 1       |    18888 | 18888        |
--------------------------------------------
| SMITH, MARK    |    18888 | 5735551262   |
--------------------------------------------
| SUPERVISOR     |   241662 | 102621       |
--------------------------------------------
| JOHN, JONES    |   241662 | 101436       |
--------------------------------------------
| SEEGER, ROBERT |   241662 | 101409       |
--------------------------------------------

他们想看到这个:

|     NAME       | PAGER_ID | MESSAGING_ID |
--------------------------------------------
| SMITH, MARK    | 18888    | 5735551262   |
--------------------------------------------
| SUPERVISOR     | 241662   | 102621       |
--------------------------------------------

有什么想法吗？

Answer 1

如果 PAGER_ID 和 MESSAGING_ID 都是文本数据类型，则此查询返回您请求的结果。

SELECT
    t.NAME,
    t.PAGER_ID,
    t.MESSAGING_ID
FROM
    (
        SELECT
            sub1.PAGER_ID,
            DLookUp(
                "MESSAGING_ID",
                "tbl_Amcom_Prod",
                "PAGER_ID = '" & sub1.PAGER_ID &
                    "' AND Len(MESSAGING_ID) = " &
                    sub1.MESSAGING_ID_max_length
            ) AS MESSAGING_ID
        FROM
            (
                SELECT
                    PAGER_ID,
                    Max(Len(MESSAGING_ID))
                        AS MESSAGING_ID_max_length
                FROM tbl_Amcom_Prod
                GROUP BY PAGER_ID
            ) AS sub1
    ) AS sub2
    INNER JOIN tbl_Amcom_Prod AS t
    ON sub2.MESSAGING_ID = t.MESSAGING_ID
WHERE t.PAGER_ID In ("241662","18888")
ORDER BY t.PAGER_ID;

如果 PAGER_ID 是 Long Integer 而不是文本，使用 ...

            DLookUp(
                "MESSAGING_ID",
                "tbl_Amcom_Prod",
                "PAGER_ID = " & sub1.PAGER_ID &
                    " AND Len(MESSAGING_ID) = " &
                    sub1.MESSAGING_ID_max_length
            ) AS MESSAGING_ID

和...

WHERE t.PAGER_ID In (241662,18888)

如果 MESSAGING_ID 是长整数而不是文本，将 Max(Len(MESSAGING_ID)) 更改为 Max(Len(CStr(MESSAGING_ID))) 和 Len(MESSAGING_ID) 到 Len(Cstr(MESSAGING_ID))