sql - 条件左连接最大日期和第二个表中的 where 子句

Question

我正在尝试将客户表与销售表连接起来，在销售表中显示数据库中所有客户的列表以及客户可能在销售表中进行的任何付费销售。现在一个客户可以在销售表中有多个销售行。这是销售表中一个客户有多个销售的示例销售记录

在提取此记录时，我只想获得 MAX (q_saledatetime)，其中 q_paidamount > 0。显示该客户上次向我们付款的时间。因此，在这种情况下，他们支付 8.90 的第 2 行是我希望为该客户获得的价格。 如果客户在销售表中没有记录，则以任何一种方式在列表中显示他们的姓名/详细信息。

我目前的失败是如何包含支付金额 + 最大日期列的 where 子句。

尝试 A

select DISTINCT ON (q_customer.q_code)
q_customer.q_code, q_customer.q_name,  -- customer info  
MAX(q_saleheader.q_saledatetime) AS latestDate, q_saleheader.q_paidamount  -- saleheader info
FROM q_customer
LEFT JOIN q_saleheader ON (q_customer.q_code =  q_saleheader.q_customercode) 
group by q_customer.q_code, q_customer.q_name , q_saleheader.q_saledatetime, q_saleheader.q_paidamount
order by q_customer.q_code ASC

结果是

所以 Fred Blogg 从第 4 行而不是第 2 行提取细节(第一张图片)。由于此时没有 q_paidamount 的规则

尝试 B

SELECT 
customer.q_code, customer.q_name,  -- customer info
sale.q_saledatetime, sale.q_paidamount  -- sale info        
FROM q_customer customer
LEFT JOIN (SELECT * FROM q_saleheader WHERE q_saledatetime = 
(SELECT MAX(q_saledatetime) FROM q_saleheader b1 where q_paidamount > 0 )) 
sale ON sale.q_customercode = customer.q_code

结果是

这似乎根本没有从销售表中获取任何信息。

更新:

在仔细研究了我的第一次尝试后，我修改了声明并提出了这个解决方案，它实现了与 Michal 的回答相同的结果。我只是想知道以下方法是否存在任何陷阱或性能缺点。

select DISTINCT ON (q_customer.q_code)
q_customer.q_code, q_customer.q_name,  -- customer info  
q_saleheader.q_saledatetime, q_saleheader.q_paidamount  -- saleheader   info
FROM q_customer
LEFT JOIN q_saleheader ON (q_customer.q_code =   q_saleheader.q_customercode AND 
q_saleheader.q_paidamount > 0 ) 
group by q_customer.q_code, q_customer.q_name ,  q_saleheader.q_saledatetime, 
q_saleheader.q_paidamount
order by q_customer.q_code ASC, q_saleheader.q_saledatetime DESC 

主要变化是在连接上添加 AND q_saleheader.q_paidamount > 0 和 q_saleheader.q_saledatetime DESC 以确保获取相关数据的第一行。如前所述，Michal 的回答和此解决方案都达到了相同的结果。只是对这两种方式中的任何一种的陷阱感到好奇。

Answer 1

试试这个查询:

SELECT c.q_code,
       c.q_name,
       CASE WHEN q_saledatetime <> '1900-01-01 00:00:00.000' THEN q_saledatetime END q_saledatetime,
       q_paidamount
FROM (
    SELECT c.q_code, 
           c.q_name,
           coalesce(s.q_saledatetime, '1900-01-01 00:00:00.000') q_saledatetime, --it will indicate customer with no data
           s.q_paidamount,
           ROW_NUMBER() OVER (PARTITION BY c.q_code ORDER BY COALESCE(s.q_saledatetime, '1900-01-01') DESC) rn
    FROM q_customer c
    LEFT JOIN (SELECT q_saledatetime,
                      q_paidamount
               FROM q_saleheader
               WHERE q_paidamount > 0) s 
    ON c.q_code = s.q_customercode
) c WHERE rn = 1