主页 / 问题 / 868621
Accepted
Sanvirtus
Asked:2020-08-14 22:25:18 +0000 UTC2020-08-14 22:25:18 +0000 UTC

如何提出请求？微软 SQL

  • 772

数据库 MS SQL。我写了一个查询，应该返回一个用户列表给我。但是我遇到了这个问题：

如果用户有多个电话号码（表 ClientsPhones），那么在返回的列表中，用户将与他拥有的电话号码一样多次重复。我无法理解如何做到这一点，以便客户不会在列表中重复。

DECLARE
       @Phone        NVARCHAR(64) = NULL,
       @Email        NVARCHAR(256) = NULL
SELECT t1.UserId,
       t2.CityId,
       t3.Balance,
       t1.FIO
FROM UsersInfo AS t1
       JOIN UsersToCity AS t2 ON t2.UserId = t1.UserId
       JOIN AccountsBalance AS t3 ON t1.UserId = t3.UserId
       JOIN ClientsPhones AS t4 ON t1.UserId = t4.UserId
WHERE (@Phone IS NULL OR t4.NormalizedPhone LIKE @Phone + '%')
  AND (@Email IS NULL OR t1.Email LIKE @Email + '%' COLLATE Cyrillic_General_CI_AI)

请告知如何纠正。

sql-server

1 个回答

  1. Best Answer

    像这样的东西，例如：

     DECLARE
           @Phone        NVARCHAR(64) = NULL,
           @Email        NVARCHAR(256) = NULL
    SELECT t1.UserId,
           t2.CityId,
           t3.Balance,
           t1.FIO
    FROM UsersInfo AS t1
           JOIN UsersToCity AS t2 ON t2.UserId = t1.UserId
           JOIN AccountsBalance AS t3 ON t1.UserId = t3.UserId
           CROSS APPLY ( 
                SELECT TOP 1 NormalizedPhone 
                FROM ClientsPhones 
                WHERE UserId = t1.UserId
                    AND (@Phone IS NULL OR NormalizedPhone LIKE @Phone + '%')
                ) cf
    WHERE (@Email IS NULL OR t1.Email LIKE @Email + '%' COLLATE Cyrillic_General_CI_AI)

    甚至只是添加DISTINCT：

    DECLARE
       @Phone        NVARCHAR(64) = NULL,
       @Email        NVARCHAR(256) = NULL
SELECT DISTINCT t1.UserId,
       t2.CityId,
       t3.Balance,
       t1.FIO
FROM UsersInfo AS t1
       JOIN UsersToCity AS t2 ON t2.UserId = t1.UserId
       JOIN AccountsBalance AS t3 ON t1.UserId = t3.UserId
       JOIN ClientsPhones AS t4 ON t1.UserId = t4.UserId
WHERE (@Phone IS NULL OR t4.NormalizedPhone LIKE @Phone + '%')
  AND (@Email IS NULL OR t1.Email LIKE @Email + '%' COLLATE Cyrillic_General_CI_AI)

    这完全取决于需要什么样的最终结果。

    • 1

相关问题