mysql - 从三个mysql表中选择数据时出错

Question

我已经运行这个 mysql 查询 2 天了。这是场景:

我有两个表:用户和帐户。结构如下:

$sql="create table if not exists users (
    id bigint(20) not null auto_increment,
    username varchar(255) not null,
    password varchar(255) not null,
    email varchar(255),
    phone varchar(40),
    PRIMARY KEY (id, username))";

$sql="create table if not exists accounts (
    id int not null auto_increment, primary key(id),
    userid int(11) not null,
    type varchar(20) not null, <----- we have two types: bill and pay ------>
    amount varchar(255) not null,
    date_paid datetime not null)";

我想做什么:

我想选择欠款超过 10,000 并在过去 14 天或更长时间之前付款或计费的人员的电话号码。

我们如何找到欠债的人:

您何时计费(假设 50, 000)，在数据库中添加一行，如下所示:

insert into accounts (id, userid, type, amount, date_paid) values ('', 'id of the person', 'OWE', 50000, '$date');

当该人付款时(假设 20, 000)，还会插入一行:

insert into accounts (id, userid, type, amount, date_paid) values ('', 'id of the person', 'PAY', 20000, '$date');

要获取此人所欠的金额:

所有账单 - 所有付款

我想到了什么:

select phone  from users u, accounts a1, accounts a2 where u.id=a1.userid and phone != '' and (((select sum(a1.amount) from a1 where a1.userid=u.id and a1.type='owe') - (select sum(a2.amount) from a2 where a2.userid=u.id and a2.type='pay')) >= 10000) and datediff(NOW(), select max (a1.datetime) as datetime from a1 where a1.userid=u.id) > 14 group by u.id

我已经修改这个查询很长时间了，我得到的最好的结果就是错误。其中一些是:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near select max (a1.datetime) as datetime from a1 where a1.userid=u.id) > 14 group by

当我删除最后一个 and 子句时，它显示:表 db.a2 不存在。

请问我该怎么办？

Answer 1

提示:

• 如果金额是小数或其他数字会更好
• 存储负金额的付款，您只需将金额相加即可获得余额

获取余额

SELECT u.id, SUM(CASE WHEN type = 'OWE' 
                 THEN CAST(amount AS DECIMAL(10,2)) 
                 ELSE CAST(amount AS DECIMAL(10,2))*-1 END ) as balance
FROM users u
INNER JOIN accounts a ON u.id = a.userid
GROUP BY u.id 
HAVING balance > 10000

检查电话号码是否至少有 5 个字符

WHERE CHAR_LENGTH(phone) > 4  

一起+日期

SELECT u.phone, u.id, SUM(CASE WHEN type = 'OWE' 
                 THEN CAST(amount AS DECIMAL(10,2)) 
                 ELSE CAST(amount AS DECIMAL(10,2))*-1 END ) as balance,
             MAX (date_paid) as last_action
FROM users u
INNER JOIN accounts a ON u.id = a.userid
WHERE CHAR_LENGTH(phone) > 4 
GROUP BY u.id 
HAVING balance > 10000 OR DATE_SUB(CURDATE(),INTERVAL 14 DAY) <= last_action