SQL使用相同字段两次，但条件不同

Question

我有一个count的头（voucher_credits.voucher_id），我已经在使用WHERE语句将其添加到我的报告中，但我想使用不同的条件将其作为另一列再次添加，我该如何做到这一点？

SELECT vouchers.code, vouchers.credit, count(voucher_credits.voucher_id) as Activated
FROM vouchers, voucher_credits, users
WHERE vouchers.id = voucher_credits.voucher_id
    AND voucher_credits.remaining_credit = 0
    AND users.id = voucher_credits.user_id AND users.state = 'active'
    AND vouchers.is_customer_service = FALSE
GROUP by vouchers.code, vouchers.credit
ORDER by count(voucher_credits.voucher_id) DESC
LIMIT 100

第二列是users.state='cancelled'，没有.remaining_credit=0

Answer 1

尝试UNION ALL:

SELECT vouchers.code, vouchers.credit, count(voucher_credits.voucher_id) as Activated
FROM vouchers, voucher_credits, users
WHERE vouchers.id = voucher_credits.voucher_id
  AND voucher_credits.remaining_credit = 0
  AND users.id = voucher_credits.user_id AND users.state = 'active'
  AND vouchers.is_customer_service = FALSE
GROUP by vouchers.code, vouchers.credit
ORDER by count(voucher_credits.voucher_id) DESC
UNION ALL
SELECT vouchers.code, vouchers.credit, count(voucher_credits.voucher_id) as Activated
FROM vouchers, voucher_credits, users
WHERE vouchers.id = voucher_credits.voucher_id
  AND users.state = 'cancelled' 
  AND users.id = voucher_credits.user_id AND users.state = 'active'
  AND vouchers.is_customer_service = FALSE
GROUP by vouchers.code, vouchers.credit
ORDER by count(voucher_credits.voucher_id) DESC

Answer 2

如果所有数据都来自同一行，则可以使用"条件和"计数，这基本上是根据表达式添加0或1：

SELECT ...,
  SUM(CASE WHEN voucher_credits.remaining_credit = 0 AND users.state = 'active'
       THEN 1 ELSE 0 END) Activated,
  SUM(CASE WHEN users.state = 'cancelled' THEN 1 ELSE 0 END) CanceledUser,
FROM ...
...

使用您的查询，这将是：

SELECT vouchers.code, vouchers.credit,
  SUM(CASE WHEN voucher_credits.remaining_credit = 0 AND users.state = 'active'
       THEN 1 ELSE 0 END) Activated,
  SUM(CASE WHEN users.state = 'cancelled' THEN 1 ELSE 0 END) CanceledUser,
FROM vouchers
INNER JOIN voucher_credits ON vouchers.id = voucher_credits.voucher_id
INNER JOIN users ON users.id = voucher_credits.user_id
WHERE vouchers.is_customer_service = FALSE
GROUP by vouchers.code, vouchers.credit
ORDER by Activated DESC
LIMIT 100

Answer 3

您可以加入该表两次。我使用了FULL OUTER JOIN，以防您从2个子查询中获得不同的记录。请随意更改联接类型。

select t1.code, t1.credit, t1.Activated, t2.code, t2.credit, t2.Cancelled
from
(SELECT vouchers.code, vouchers.credit, count(voucher_credits.voucher_id) as Activated
FROM vouchers, voucher_credits, users
WHERE vouchers.id = voucher_credits.voucher_id
    AND voucher_credits.remaining_credit = 0
    AND users.id = voucher_credits.user_id AND users.state = 'active'
    AND vouchers.is_customer_service = FALSE
GROUP by vouchers.code, vouchers.credit) t1
FULL OUTER JOIN
(SELECT vouchers.code, vouchers.credit, count(voucher_credits.voucher_id) as Cancelled
FROM vouchers, voucher_credits, users
WHERE vouchers.id = voucher_credits.voucher_id
    AND users.state = 'cancelled'
    AND users.id = voucher_credits.user_id AND users.state = 'active'
    AND vouchers.is_customer_service = FALSE
GROUP by vouchers.code, vouchers.credit) t2
ON t1.code = t2.code and t1.credit = t2.credit