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

j91ykkif 于 2022-12-29 发布在 PostgreSQL

关注(0)|答案(2)|浏览(149)

我有两张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
)

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

postgresql

来源：https://stackoverflow.com/questions/74912752/find-users-who-make-orders-in-specific-month-and-also-have-orders-in-other-month

2条答案

按热度按时间

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

赞(0）回复(0）举报 2022-12-29

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
)

赞(0）回复(0）举报 2022-12-29

我来回答

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

2条答案

相关问题

热门标签

最新问答