从SQL查询中删除重复项（不仅仅是“使用不同的”） [英] Removing duplicates from a SQL query (not just "use distinct")

问题描述

这很简单，这里是我的查询：

  SELECT DISTINCT U.NAME，P.PIC_ID 
 FROM USERS U，PICTURES P，POSTINGS P1 
 WHERE U.EMAIL_ID = P1.EMAIL_ID AND P1.PIC_ID = P.PIC_ID AND P.CAPTION LIKE'％car％'; 
  

但这只会删除重复的行，其中一行具有相同的u.name和p.pic_id。我想要的是，如果有任何重复的名称，它只是遗漏其他行。这是一个奇怪的查询，但是一般来说，如何将独特的应用程序应用于SELECT子句的单个列？

解决方案

任意地选择保持最小PIC_ID。另外，避免使用隐式连接语法。

  SELECT U.NAME，MIN（P.PIC_ID）
 FROM USERS U 
 INNER JOIN POSTINGS P1 
 ON U.EMAIL_ID = P1.EMAIL_ID 
 INNER JOIN PICTURES P 
 ON P1.PIC_ID = P.PIC_ID 
 WHERE P.CAPTION LIKE '％car％'
 GROUP BY U.NAME; 
  

It's probably simple, here is my query:

SELECT DISTINCT U.NAME, P.PIC_ID
FROM USERS U, PICTURES P, POSTINGS P1
WHERE U.EMAIL_ID = P1.EMAIL_ID AND P1.PIC_ID = P.PIC_ID AND P.CAPTION LIKE '%car%';

but this will only remove duplicates where a row has both the same u.name and p.pic_id. I want it so if there is any duplicates of the names, it just leaves out the other rows. It's a weird query, but in general, how can I apply the distinct to a single column of the SELECT clause?

解决方案

Arbitrarily choosing to keep the minimum PIC_ID. Also, avoid using the implicit join syntax.

SELECT U.NAME, MIN(P.PIC_ID)
    FROM USERS U
        INNER JOIN POSTINGS P1
            ON U.EMAIL_ID = P1.EMAIL_ID
        INNER JOIN PICTURES P
            ON P1.PIC_ID = P.PIC_ID
    WHERE P.CAPTION LIKE '%car%'
    GROUP BY U.NAME;

这篇关于从SQL查询中删除重复项（不仅仅是“使用不同的”）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！