Mysql 将列的总和与另一个表中的列进行比较

Question

如何根据左表中的列的总和从另一个表中选择一行

SELECT Group_concat(c.cartid SEPARATOR ',') AS CartIDs, 
       Sum(c.grandtotal)                    AS Sum, 
       r.percentage 
FROM   carts c 
       LEFT JOIN rebates r 
              ON Sum(c.grandtotal) >= r.fromamountpurchased 
                 AND Sum(c.grandtotal) <= r.toamountpurchased 
WHERE  c.ispaid = '1' 
       AND c.addedtorebates = '0' 
GROUP  BY c.customerid 

但这行不通。我也尝试过 HAVING 也不起作用。

是否可以在一个查询中实现？

谢谢!

更新:

CREATE TABLE IF NOT EXISTS `carts` (
  `CartID` bigint(20) NOT NULL AUTO_INCREMENT,
  `CustomerID` bigint(20) NOT NULL,
  `GrandTotal` decimal(10,2) NOT NULL,
  `IsPaid` enum('0','1','2') NOT NULL,
  `AddedToRebates` enum('0','1') NOT NULL,
  PRIMARY KEY (`CartID`)
)

    INSERT INTO `carts` (`CartID`, `CustomerID`, `GrandTotal`,  `IsPaid`,
    `AddedToRebates`, ) VALUES
    (71, 28,  '57450.00', '1', '0' ),
    (73, 28,  '57450.00', '1', '0');



CREATE TABLE IF NOT EXISTS `rebates` (
  `RebateID` bigint(20) NOT NULL AUTO_INCREMENT,
  `Percentage` varchar(255) NOT NULL COMMENT 'in %',
  `FromAmountPurchased` decimal(10,2) NOT NULL,
  `ToAmountPurchased` decimal(10,2) NOT NULL,
  `CashEquivalent` decimal(10,2) NOT NULL,
  PRIMARY KEY (`RebateID`)
)



 INSERT INTO `rebates` (`RebateID`, `Percentage`, `FromAmountPurchased`, 
`ToAmountPurchased`, `CashEquivalent`) VALUES
    (1, '5', '50000.00', '69999.00', '3000.00'),
    (2, '10', '70000.00', '79999.00', '5000.00'),
    (3, '15', '80000.00', '89999.00', '6000.00'),
    (4, '20', '90000.00', '99999.00', '7000.00'),
    (5, '25', '100000.00', '150000.00', '8000.00'),
    (6, '0', '0.00', '49999.00', '0.00');

Answer 1

试试这个:

select q1.CartIDs, q1.total, r.percentage
  from
    (select group_concat(c.cartid) as CartIDs, sum(c.grandtotal) as total
      from carts c
        where c.ispaid = '1'
        and c.addedtorebates = '0'
      group by c.customerid ) q1
    left join rebates r
      on q1.total >= r.fromamountpurchased 
        and q1.total <= r.toamountpurchased

这里有一个演示 fiddle :http://sqlfiddle.com/#!9/d27f5/3

您不能在连接谓词中使用诸如 SUM() 之类的聚合函数，因此在这种情况下，子查询很有用