本文是小编为大家收集整理的关于选择不同的+选择顶部来合并多行的处理/解决方法，可以参考本文帮助大家快速定位并解决问题，中文翻译不准确的可切换到English标签页查看源文。
问题描述
我试图从表中选择行，每个电子邮件地址一行，然后从电子邮件列表中的顶行中返回一个名称.但是，查询返回多个电子邮件地址.我在做什么错?
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
推荐答案
您的错误是WHERE oc.client_id = oca.client_id应该是WHERE oc.email = oca.email.
您没有说您正在使用哪个DBM，但是如果是MS SQL，以下内容也将执行您想要的.
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
其他推荐答案
独特的列表中的所有列都适用不同的名称.
考虑一下您要做的事情...您希望每个不同的(不必在此处使用SQL关键字)的电子邮件地址，其名字匹配它的名字(首先是由客户端的订单定义).我很难在没有看到实际数据模型的情况下确定地说，但这是我的第一个猜测:
SELECT
T1.email,
T1.first_name
FROM
Onsite_Clients_Archive T1
LEFT OUTER JOIN Onsite_Clients_Archive T2 ON
T2.email = T1.email AND
T2.client_id < T1.client_id
WHERE
T2.client_id IS NULL -- Assuming that this is a not null column, the only way it could be NULL is if there was no match
问题描述
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
推荐答案
Your bug is WHERE oc.client_id = oca.client_id should be WHERE oc.email = oca.email.
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
其他推荐答案
The DISTINCT will apply to all columns in your column list, so in this case if there are multiple rows in the Onsite_Clients_Archive with the same email address then you will get those back from the query as long as they have a different name.
Think about what you're trying to do... you want each distinct (not meaning the SQL keyword necessarily here) email address with the first name that matches for it (first defined by order of client_id). This is difficult for me to say definitively without seeing the actual data model, but here's my first guess:
SELECT
T1.email,
T1.first_name
FROM
Onsite_Clients_Archive T1
LEFT OUTER JOIN Onsite_Clients_Archive T2 ON
T2.email = T1.email AND
T2.client_id < T1.client_id
WHERE
T2.client_id IS NULL -- Assuming that this is a not null column, the only way it could be NULL is if there was no match
更多相关问答
联系站长