Как манипулировать значениями и получать новый результат в SQL?

Question

У меня есть этот запрос:

SELECT 
    c.ID, c.Firstname, c.lastname, c.BDaY, c.gender, 
    cp.code, cp.Citizenship, r.race, e.ethnicity 
FROM 
    Client AS C (nolock) 
JOIN
    Citizenship AS cp (nolock) ON c.ID = cp.client_ID
JOIN
    Race AS r (nolock) ON c.ID = R.Client_ID 
JOIN 
    Ethnicity AS E (nolock) ON E.Client_ID = c.ID

Этот запрос вернет несколько дублированных имен клиентов, потому что они имеют различную расу и этническую принадлежность.

Пример:

    ID |FirstName|Lastname|  BDay    | gender | code |citizenship|    race    |    ethnicity 
    1   Pedram    Salamati 01-20-1998    M      1     US citizen   Middle-east     Spanish
    1   Pedram    Salamati 01-20-1998    M      1     US Citizen   Middle-east     unknown
    1   Pedram    Salamati 01-20-1998    M      1     US Citizen   Middle-east     Brazilian
    2   Jesse     Albert   03-05-1982    F      1     US Citizen   African         not Spanish
    2   Jesse     Albert   03-05-1982    F      1     US Citizen   American        not Spanish

Мне было интересно, есть ли какой-нибудь способ сказать, если раса не =, то раса должна быть многорасовой, и если этническая принадлежность не = друг другу для того же Id, выберите последний обновленный.

PS.Ethnicity имеет метку времени, и я могу использовать Max(e.LastUpdate)

Я думаю, может быть, подзапрос может помочь!

Любая помощь или мысль будет высоко ценится!

Answer 1

Вот некоторые тестовые данные, чтобы имитировать вашу среду в будущем, вы должны разделить соответствующие таблицы и тестовые данные. в том числе и заявления DML уместны и полезны, так что люди могут попробовать свое решение, прежде чем ответить.

DECLARE @Client AS TABLE (ID INT, Firstname VARCHAR(25), LastName VARCHAR(25), BDay DATE, Gender CHAR(1))
INSERT INTO @Client VALUES (1,'Pedram','Salamati','01-20-1998','M')
,(2,'Jesse','Albert','03-05-1982','F')
DECLARE @Citizenship AS TABLE (Client_ID INT, Code INT, Citizenship VARCHAR(100))
INSERT INTO @Citizenship VALUES (1,1,'US citizen'),(2,1,'US citizen')
DECLARE @Ethnicity AS TABLE (Client_ID INT, Ethnicity VARCHAR(50))
INSERT INTO @Ethnicity VALUES (1,'Spanish'),(1,'unknown'),(1,'Brazilian'),(2,'not Spanish')
DECLARE @Race AS TABLE (Client_Id INT, Race VARCHAR(50), LastUpdate DATETIME)
INSERT INTO @Race VALUES (1,'Middle-east',GETDATE()),(2,'African',GETDATE()),(2,'American',GETDATE() -1)

С этими переменными вы можете сделать следующее, конечно, есть более 1 способа, это просто 1 способ, который я выбираю по нескольким причинам:

;WITH cteEthnicity AS (
    SELECT
       e.Client_ID
       ,CASE WHEN COUNT(DISTINCT e.Ethnicity) > 1 THEN 'Multiracial' ELSE MIN(e.Ethnicity) END as Ethnicity
    FROM
       @Ethnicity e
    GROUP BY
       e.Client_ID
)

, cteRace AS (
    SELECT
       r.Client_Id
       ,r.Race
       ,ROW_NUMBER() OVER (PARTITION BY r.Client_Id ORDER BY r.LastUpdate DESC) as RowNumber
    FROM
       @Race r
)

SELECT
    c.ID
    ,c.Firstname
    ,c.lastname
    ,c.BDaY
    ,c.gender
    ,cp.code
    ,cp.Citizenship
    ,r.race
    ,e.ethnicity
From
    @Client AS C --(nolock) 
    Join @Citizenship as cp --(nolock)
    on  c.ID = cp.client_ID
    Join cteRace as r --(nolock)
    ON c.ID = R.Client_ID
    AND r.RowNumber = 1
    Join cteEthnicity as E --(nolock)
    ON E.Client_ID = c.ID

Вы показали 2 вопроса 1 с расой и 1 с этнической принадлежностью

• Для этнической принадлежности: вы хотите использовать агрегацию для определения этнической принадлежности. это также можно сделать с помощью оконной функции, но, как я написал здесь, она будет учитывать наличие дубликатов даже в таблице Ethnicity.

• Для Race: вы просто хотите, чтобы последняя строка была разделена клиентом, вы можете использовать функцию ROW_NUMBER(), чтобы сгенерировать ее, а затем выбрать, где она равна 1, в операторе соединения

Третья проблема, на которую вы не указали, но которая в любом случае может быть возможна в некоторых странах, - ДВОЙНОЕ ГРАЖДАНСТВО. В этом случае вы можете использовать метод, аналогичный Race.

Заметьте, что даже если используются общие табличные выражения [CTE], вы также можете вкладывать их как вложенный выбор.