依靠缺少零计数的多个表

I am running this query to return data with count < 0. It works fine until count is > 0 and < 50. But when count becomes 0, it doesnot return the data. Count is defined by coupons`.`status. On count zero, there will be no data in coupons table with status as 1. This is creating the issue, as it omits the whole row.

我正在运行此查询以返回count <0的数据。它一直正常工作,直到count> 0且<50。但是当count变为0时,它不会返回数据。计数由优惠券`.status定义。在计数零时,优惠券表中将没有状态为1的数据。这是创建问题,因为它省略了整行。

SELECT count(*) AS count, clients.title, plans.name 
FROM `coupons`
    INNER JOIN `clients` ON `coupons`.`client_id` = `clients`.`id`  
    INNER JOIN `plans` ON `coupons`.`plan_id` = `plans`.`id`
WHERE `coupons`.`status` = 1
GROUP BY `coupons`.`client_id`, `coupons`.`plan_id` 
HAVING count < 50

Please help how to fix it.

请帮忙解决问题。

Table definitions.

coupons (id, client_id, plan_id, customer_id, status, code)
plans (id, name)
clients (id, name...)
client_plans (id, client_id, plan_id)

Basically, a client can have multiple plans and a plan can belong to multiple clients.

基本上,客户可以有多个计划,计划可以属于多个客户。

Coupons table stores predefined coupons which can be allocated to customers. Non allocated coupons have status as 0, while as allocated coupons get status as 1

优惠券表存储可以分配给顾客的预定义优惠券。未分配的优惠券的状态为0,而分配的优惠券的状态为1

Here I am trying to fetch non allocated client wise, plan wise coupon count where either the count is less than 50 or count has reached 0

在这里,我试图获取未分配的客户端明智,计划明智的优惠券计数,其中计数小于50或计数已达到0

For example,

If coupons table as 10 rows of client_id = 1 & plan_id = 1 with status as 1, it should return count as 10, but when the table has 0 rows with client_id = 1 and plan_id = 1 with status as 1, it does not return anything in the above query.

如果优惠券表为10行client_id = 1&plan_id = 1且状态为1,则应将计数返回为10,但当表有0行且client_id = 1且plan_id = 1且状态为1时,它不会返回以上查询中的任何内容。

2 个解决方案

#1

Thank you all for your inputs, this worked.

谢谢大家的投入,这很有用。

select 
    sum(CASE WHEN `coupons`.`status` = 1 THEN 1 ELSE 0 END) as count,     
    clients.title, 
    plans.name 
from 
    `clients` 
left join 
    `coupons` 
on 
    `coupons`.`client_id` = `clients`.`id` 
left join 
    `plans` 
on 
    `coupons`.`plan_id` = `plans`.`id` 
group by 
    `coupons`.`client_id`, 
    `coupons`.`plan_id` 
having 
    count < 50

#2

With the inner joins, the query is not going to return any "zero" counts.

使用内部联接,查询不会返回任何“零”计数。

If you want to return "zero" counts, you are going to need an outer join somewhere.

如果要返回“零”计数,则需要在某处进行外连接。

But it's not clear what you are actually trying to count.

但目前尚不清楚你究竟在想什么。

Assuming that what you are trying to get is a count of rows from coupons, for every possible combination of rows from plans and clients, you could do something like this:

假设您要获取的是来自优惠券的行数,对于计划和客户端的每个可能的行组合,您可以执行以下操作:

SELECT COUNT(`coupons`.`client_id`) AS `count`
     , clients.title
     , plans.name
  FROM `plans`
 CROSS 
  JOIN `clients`
  LEFT
  JOIN `coupons`
    ON `coupons`.`client_id` = `clients`.`id`
   AND `coupons`.`plan_id`   = `plans`.`id`  
   AND `coupons`.`status`    = 1
 GROUP
    BY `clients`.`id`
     , `plans`.`id`
HAVING `count` < 50

This is just a guess at result set you are expecting to return. Absent table definitions, example data, and the expected result, we're just guessing.

这只是对您期望返回的结果集的猜测。缺少表定义,示例数据和预期结果,我们只是猜测。

FOLLOWUP

Based on your comment, it sounds like you want conditional aggregation.

根据您的评论,听起来您需要条件聚合。

To "count" only the rows in coupons that have status=1, you can do something like this:

要仅“计算”状态为1的优惠券中的行,您可以执行以下操作:

SELECT SUM( `coupons`.`status` = 1 ) AS `count`
     , clients.title
     , plans.name
  FROM `coupons`

  JOIN `plans`
    ON `plans`.`id` = `coupons`.`plan_id`

  JOIN `clients`
    ON `clients`.`id` = `coupons`.`client_id`

 GROUP
    BY `clients`.`id`
     , `plans`.`id`

HAVING `count` < 50

There are other expressions you can use to get the conditional "count". For example

您可以使用其他表达式来获取条件“计数”。例如

SELECT COUNT( IF(`coupons`.`status`=1, 1, NULL) ) AS `count`

SELECT SUM( IF(`coupons`.`status`=1, 1, 0) ) AS `count`

or, for a more ANSI standards compatible approach

或者,对于更符合ANSI标准的方法

SELECT SUM( CASE WHEN `coupons`.`status` = 1 THEN 1 ELSE 0 END ) AS `count`

#1