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

问题描述

这可能很简单，这是我的查询:

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%';

但这只会删除行具有相同 u.name 和 p.pic_id 的重复项.我想要它，所以如果名称有任何重复，它只会忽略其他行.这是一个奇怪的查询，但总的来说，如何将 distinct 应用于 SELECT 子句的单个列?

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?

推荐答案

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

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屋！