在MySQL中的独特价值和总和其他

Question

我正在尝试获取 unique> unique od_grp_id的order_payment_total一次，但是在使用sum时会添加它。

CREATE TABLE IF NOT EXISTS `subscription` (
  `id` int(11) unsigned NOT NULL, 
  `od_grp_id` int(11) unsigned NULL, 
  `user_id` int(11) NOT NULL, 
  `order_discount` decimal(10, 2) null, 
  PRIMARY KEY (`id`)
) DEFAULT CHARSET = utf8;

INSERT INTO `subscription` (
  `id`, `od_grp_id`, `user_id`, `order_discount`
) 
VALUES 
  (123994, NULL, 115, null), 
  (124255, NULL, 115, null), 
  (124703, 1647692222, 115, null), 
  (125788, 1647692312, 115, '25.00'), 
  (125789, 1647692312, 115, '5.00');

CREATE TABLE IF NOT EXISTS `online_payment_against_subscription` (
  `subscription_od_grp_id` int(11) unsigned NOT NULL, 
  `order_payment_total` decimal(10, 2) unsigned NOT NULL, 
  `user_id` int(11) NOT NULL
) DEFAULT CHARSET = utf8;

INSERT INTO `online_payment_against_subscription` (
  `subscription_od_grp_id`, `order_payment_total`, `user_id` 
) 
VALUES 
  (1643695200, '45.00', 115), 
  (1647692312, '250.00', 115), 
  (1647692222, '30.00', 115);

SELECT 
  sum(y.order_payment_total), 
  sum(s.order_discount) 
FROM 
  subscription s 
  LEFT JOIN(
    SELECT 
      SUM(order_payment_total) as order_payment_total, 
      user_id, 
      subscription_od_grp_id 
    FROM 
      online_payment_against_subscription 
    GROUP BY 
      subscription_od_grp_id
  ) y ON y.subscription_od_grp_id = s.od_grp_id 
WHERE 
  find_in_set(
    s.id, '123994,124255,124703,125788,125789'
  ) 
group by 
  s.user_id

Current Output:
| sum(y.order_payment_total) |sum(s.order_discount)  |
|----------------------------|-----------------------|
|                        530 |                    30 |


Expected Ouput:
| sum(y.order_payment_total) |sum(s.order_discount)  |
|----------------------------|-----------------------|
|                       280  |                    30 |

SQL小提琴： http://sqlfiddle.com/#！9/5628f5/5628f5/1

Answer 1

如果我正确理解，问题是由一些重复od_grp_id来自subscription表引起的，因此您可以在加入之前删除重复od_grp_id，所以我们可以在子查询中这样做。

查询1 ：

SELECT 
   SUM(order_payment_total),
   SUM(order_discount)
FROM (
   SELECT od_grp_id,SUM(order_discount) order_discount
   FROM subscription
   WHERE find_in_set(id, '123994,124255,124703,125788,125789') 
   GROUP BY od_grp_id
 ) s 
LEFT JOIN online_payment_against_subscription y ON y.subscription_od_grp_id=s.od_grp_id

结果 强>：

| SUM(order_payment_total) | SUM(order_discount) |
|--------------------------|---------------------|
|                      280 |                  30 |

Answer 2

我认为您收到此错误是因为每个订阅都没有订单付款，即您收到 NULL 值。

您可以尝试使用此方法删除它们 -

SELECT y.order_payment_total
    FROM subscription s
             LEFT JOIN(SELECT SUM(order_payment_total) AS order_payment_total, user_id, subscription_od_grp_id
                       FROM online_payment_against_subscription
                       GROUP BY subscription_od_grp_id) y ON y.subscription_od_grp_id = s.od_grp_id
    WHERE FIND_IN_SET(s.id, '11258,22547,18586')
      AND y.order_payment_total IS NOT NULL;

或者如果需要，您可以将 NULL 值设置为 0 -

SELECT COALESCE(y.order_payment_total, 0) AS order_payment_total
    FROM subscription s
             LEFT JOIN(SELECT SUM(order_payment_total) AS order_payment_total, user_id, subscription_od_grp_id
                       FROM online_payment_against_subscription
                       GROUP BY subscription_od_grp_id) y ON y.subscription_od_grp_id = s.od_grp_id
    WHERE FIND_IN_SET(s.id, '11258,22547,18586');