选择不同的+选择顶部以合并多行 [英] Select distinct + select top to merge multiple rows

问题描述

我正在尝试从表格中选择行，每个电子邮件地址一行，然后从电子邮件列表的第一行中返回一个名字。但是查询将返回多个电子邮件地址。我在做什么错？

I'm trying to select rows from a table, one row per email address, and return one firstname from the top row in the email list. The query, though, returns multiple email addresses. What am I doing wrong?

SELECT 
    DISTINCT email,
    (SELECT TOP 1 firstname 
     FROM onsite_clients_archive oc 
     WHERE oc.client_id=oca.client_id 
     ORDER BY client_id)
FROM onsite_clients_archive oca 
WHERE users_user_id IS NULL

推荐答案

您的错误是，其中oc.client_id = oca.client_id 应该是哪里oc.email = oca.email 。

您没有说您是哪个DBMS重新使用，但如果使用的是MS SQL，则以下操作也可以满足您的要求。

You didn't say which DBMS you're using, but if it's MS SQL, the following will also do what you want.

SELECT email, firstname
FROM (
    SELECT 
        email, firstname, 
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY client_id DESC) AS intRow
    FROM onsite_clients_archive
    WHERE users_user_id IS NULL
) AS T
WHERE intRow = 1

这篇关于选择不同的+选择顶部以合并多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！