postgresql 查找在特定月份下订单并且在一年中的其他月份也有订单的用户

j91ykkif  于 2022-12-29  发布在  PostgreSQL
关注(0)|答案(2)|浏览(150)

我有两张table:

任务:
选择last_name个用户,这些用户:

  • 在2013年1月至少下了一个订单;
  • 并在同一年的任何其他月份作出最少一项命令。

我只能使用子查询、连接和联合(甚至不允许使用WITH)。
我不明白如何同时合并两个条件。
我运行此程序以获取2013年有订单(发票)的用户:

SELECT
    customer_id,
    EXTRACT(MONTH FROM invoice_date::timestamp) AS invoice_month,
    COUNT(invoice_id) as invoices
FROM invoice
WHERE EXTRACT(YEAR FROM invoice_date::timestamp) = 2013
GROUP BY customer_id, invoice_month

此外,我能够找到在2013年1月至少下过一个订单的用户:

SELECT 
client.last_name
FROM client
WHERE client.customer_id IN (
SELECT
    customer_id
    FROM invoice
    WHERE 
        TO_CHAR(invoice_date::timestamp, 'YYYY-MM') = '2013-01'
    GROUP BY customer_id
    HAVING COUNT(invoice_id) >= 1
)

但我不知道怎么加上第二个条件。有什么建议吗?

sz81bmfz

sz81bmfz1#

select last_name from
client left join
(
   select distinct customer_id from invoice where 
   month(invoice_date) = 1 and year(invoice_date) = 2013
) jan2013 on client.customer_id = jan2013.customer_id
left join
(
   select distinct customer_id from invoice where 
   month(invoice_date) != 1 and year(invoice_date) = 2013
) notjan2013 on jan2013.customer_id=notjan2013.customer_id
where notjan2013.customer_id is not null
guykilcj

guykilcj2#

您可以使用filtered count函数修改查询,如下所示:

SELECT 
client.last_name
FROM client
WHERE client.customer_id IN (
 SELECT customer_id
 FROM invoice
 WHERE EXTRACT(YEAR FROM invoice_date::timestamp) = 2013
 GROUP BY customer_id
 HAVING COUNT(*) Filter (Where EXTRACT(MONTH FROM invoice_date::timestamp) = 1) >= 1
 And COUNT(*) Filter (Where EXTRACT(MONTH FROM invoice_date::timestamp) <> 1) >= 1
)

相关问题