sql server语言 - sql删除具有相同数据(不重复)的行

Question

结果如下(没有10个声望点)。我想删除"***"所表示的"重复"。我不知道该怎么做。

Client Name----Medication 1------Medication 2
John             Adderall XR       Concerta
John             Adderall XR       Intuniv ER
John             Adderall XR       Risperdal   *** 
John             Adderall XR       Prazosin
John             Risperdal         Concerta
John             Risperdal         Intuniv ER
John             Risperdal         Adderall XR ***
John             Risperdal         Prazosin

这是我的查询

create table #temp5 (medCode int, medText varchar(500), clientid int)
insert into #temp5 (medCode, medText, clientid)
select distinct  
    ClientMedications.MedicationNameId,
    dbo.GetMedicationName(ClientMedications.MedicationNameId),
    Clients.ClientId
from 
    ClientMedications
    join Clients on ClientMedications.ClientId = Clients.ClientId

select distinct
    c.LastName + ' ,' + c.FirstName as [Client Name],
    dbo.GetMedicationName(cm.MedicationNameId) as [Medication 1],
    t1.medText as [Medication 2],
    cmi.InteractionLevel, 
    cmi.ClientMedicationId2
from 
    Clients as c
    join ClientMedications as cm on c.ClientId = cm.ClientId
    join ClientMedicationInteractions as cmi 
        on cm.ClientMedicationId = cmi.ClientMedicationId1
    join #temp5 as t1 on t1.clientId = c.ClientId
where 
    t1.medText != dbo.GetMedicationName(cm.MedicationNameId)

Answer 1

为每行药品创建一个alpha排序列表，然后删除排序列表。

WITH medicationsWithAlphaList
AS
(
     SELECT
        clientname,
        medication1,
        medication2,
        CASE 
            WHEN medication1<medication2
                THEN medication1 +', ' + medication2
            ELSE 
                medication2 + ', ' + medication1 
        END AS medsAlphaList
    FROM
        clientmedications;
)
SELECT
    clientname,
    medication1,
    medication2
FROM
    medicationsWithAlphaList
WHERE
    medsAlphaList IN
        (
        SELECT
            medsAlphaList
        FROM
            medicationsWithAlphaList
        GROUP BY
            medsAlphaList
        HAVING
            COUNT(*)=1
        );

Answer 2

如果您想要删除第二行(或第一行)，那么您实际上不需要药物的顺序。在这种情况下，您可以将查询结果放在另一个临时表中，例如#temp6，并使用case对两个列进行排序，如下例所示:

SELECT distinct
    Name, CASE WHEN med1 > med2 
            THEN med1 + ', ' + med2 
            ELSE med2 + ', ' + med1 
          END as meds
FROM
    (SELECT 'John' as Name, 'Amerral' as med1, 'Metteral' as med2
    UNION
    SELECT 'John', 'Metteral', 'Amerral') medss