在 LEFT JOIN 子查询中显示 COUNT？

发布于 2024-12-02 01:50:55 字数 679 浏览 1 评论 0原文

我有一个查询，选择选定时间段之间的订单信息。我想包含一个 where 子句，将订单信息限制为所有订单总数只有 1 个的订单（一直以来）。

这是我到目前为止所得到的：

SELECT o.orders_id, o.customers_id, o.customers_name, o.payment_method, o.date_purchased,o.orders_status, o.shipping_status, ot.value 
FROM orders as o 
LEFT JOIN orders_total as ot ON o.orders_id = ot.orders_id 
WHERE date_purchased between '2011-07-30' AND '2011-08-30 23:59:59' 
AND ot.class = 'ot_total' 
AND o.customer_service_id = '' 
OR o.customer_service_id IS NULL 
ORDER BY orders_id DESC

该查询为我提供了指定时间段内的所有订单。我需要包含一个子查询（或类似的东西）来计算所有以前的（整个时间）订单（order_count）BYcustomers_id。然后包括“HAVING order_count <” 2' 在 where 子句中。

这可能吗？这有道理吗？

原文

I have a query that selects order info between a selected time period. I want to include a where clause that limits the order info to all orders that have only 1 order total(through out all time).

Here is what I have so far:

SELECT o.orders_id, o.customers_id, o.customers_name, o.payment_method, o.date_purchased,o.orders_status, o.shipping_status, ot.value 
FROM orders as o 
LEFT JOIN orders_total as ot ON o.orders_id = ot.orders_id 
WHERE date_purchased between '2011-07-30' AND '2011-08-30 23:59:59' 
AND ot.class = 'ot_total' 
AND o.customer_service_id = '' 
OR o.customer_service_id IS NULL 
ORDER BY orders_id DESC

This query gives me all orders in the specified time period. I need to include a subquery(or something similar) that counts all previous(through out all time) orders(order_count) BY customers_id. Then include a 'HAVING order_count < 2' in the where clause.

Is this possible? Does this make sense?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

末蓝 2024-12-09 01:50:55

只需在 close 处添加此内容即可：

AND (
    SELECT COUNT(o.id)
    FROM   orders o2
    WHERE  o2.customers_id = o.customers_id
) < 2

或者，如果您想返回订单计数，请将其添加到 SELECT 子句中，然后添加 HAVING 子句：

SELECT o.orders_id, ..., (
    SELECT COUNT(o.id)
    FROM   orders o2
    WHERE  o2.customers_id = o.customers_id
) as orders_count
...
HAVING orders_count < 2

Just add this in you where close:

AND (
    SELECT COUNT(o.id)
    FROM   orders o2
    WHERE  o2.customers_id = o.customers_id
) < 2

Or if you want to return the orders count, add it in your SELECT clause, and add a HAVING clause:

SELECT o.orders_id, ..., (
    SELECT COUNT(o.id)
    FROM   orders o2
    WHERE  o2.customers_id = o.customers_id
) as orders_count
...
HAVING orders_count < 2

回复收藏 0 原文

~没有更多了~