Mysql 选择总和低于给定金额的记录

Question

我对mysql有点陌生，遇到了一个问题。我有两张桌子。一个包含客户信息，第二个包含帐单信息。我需要对每个客户的所有账单成本求和，然后仅选择总和为 <1000 的客户信息/账单信息。

这是我正在使用的两个表

客户表

CustId  last_name  first_name  address       phone        email
-----------------------------------------------------------------------
C1      Johnson    Cathryn     123 This Wy.  555-555-5555 [e-mail]
C2      Bova       Vanessa     234 This Wy.  555-555-1111 [e-mail]
C3      Kuney      Albert      356 This Wy.  555-555-2222 [e-mail]

计费表

end_date    total_cost    billing_id    CustID    Rental_ID
-----------------------------------------------------------
2014-05-25  249.95        CR011         C1        R1
2014-07-30  49.99         CR012         C2        R2
2014-08-25  809.91        CR013         C3        R#

为这两个表创建表语句是：

create table customer
(
CustID varchar(10) not null,
last_name varchar(50) not null,
first_name varchar(50),
address varchar(50),
phone varchar(15),
email varchar(50),
constraint pk_CustID primary key (CustID)
);

和

create table billing
(
end_date date not null,
total_cost decimal(6,2) not null,
billing_id varchar(10) not null,
CustID varchar(10) not null,
Rental_ID varchar(10) not null,
constraint pk_billing_id primary key (billing_id),
constraint fk_CustID foreign key (CustID) references customer (CustID),
constraint fk_Rental_ID foreign key (Rental_ID) references rentals 
(Rental_ID));

我尝试使用的语句是这样的

select c.last_name, c.first_name, sum(b.total_cost) 'from customer c left 
join billing b on c.custid = b.custid 
where b.total_cost < 1000 
group by c.last_name, c.first_name 
order by c.last_name, c.first_name;

我遇到的问题是它仍然返回总和超过 1000>的记录。任何帮助将不胜感激！

Answer 1

这是因为在当前查询中，您正在对单独具有 total_cost <1000 的项目求和，请尝试如下操作：

SELECT * FROM (
    select c.last_name, c.first_name, sum(b.total_cost) as tc
    from customer c left join billing b  
    on c.custid = b.custid
    group by c.last_name, c.first_name
    order by c.last_name, c.first_name ) AS A
WHERE tc < 1000

请注意，由于您尚未发布表格，因此我无法测试上述内容