如果数据基于SQL中的给定优先级存在，则返回行

I am trying to form a postgresSQL statement that returns a customer email based on the email type with a given priority. Below I have a table with customers 1 and two. Customer 1 has both personal and company emails where as customer 2 has on company.

我正在尝试形成一个postgresSQL语句，根据给定优先级的电子邮件类型返回客户电子邮件。下面我有一张与顾客1和2的桌子。客户1有个人和公司的电子邮件，客户2有公司。

The problem I am trying to solve is return the customers personal email if it exists first and if not return the company. So, the personal email is given priority over the company. Is this even possible in postgresSQL.

我正在尝试解决的问题是，如果客户的个人电子邮件是先存在的，如果不返回公司，我将返回客户的个人电子邮件。因此，个人邮件优先于公司。这在postgresSQL中是否可行。

 customers
+------------+
| cusomterID |
+------------+
| 1          |
| 2          |
+------------+

customer_email
+------------+-------------+
| cusomterID | email_type  |
+------------+-------------+
| 1          | personal    | -- 0
| 2          | company     | -- 1
| 1          | company     | -- 1
+------------+-------------+

What I am trying now is not really working. It returns all of the rows and does not filter

我现在所尝试的并不是真的有效。它返回所有的行，不过滤

SELECT *
FROM customers cs
JOIN cumstomer_email cm ON cm.customerId = cs.customreId
WHERE COALESCE(cm.email_type,0) IN (0,1)

2 个解决方案

#1

One option would be to use conditional aggregation:

一种选择是使用条件聚合:

select customerId, max(case when email_type = 'personal' then email_type
                       else email_type 
                       end) email_type
from customer_email
group by customerId

SQL Fiddle Demo
SQL小提琴演示

And here's another option using row_number():

这里还有另一个使用row_number()的选项:

select customerId, email_type
from (select *, 
           row_number() over (partition by customerId 
                              order by email_type = 'personal' desc) rn
      from customer_email) t
where rn = 1

More Fiddle
更多的小提琴

#2

You could do this with a common table expression (CTE):

可以使用公共表表达式(CTE):

with emailPriority as (
    select customerId,
           max(email_type) emailType
    from   customer_email
    group by customer_id)
select  cs.*, cm.email_address
from    customers cs join emailPriority ep on cs.customerId = ep.customerId
        join customer_email cm on cm.email_type = ep.email_type

#1