mysql中两列之间的区别 [英] Difference between two columns in mysql
问题描述
我有两列(credit和debited_amount),我想计算它们之间的差,仅需要检索大于零的记录或debited_amount字段为Null.没关系,如果值是零.这是我尝试过的sqlquery.请帮助
I have two columns (credit and debited_amount) and I want to calculate the difference between them.Only need to retrieve records greater than zero or if the debited_amount field is Null. Never mind if the value is zero.Here is the sqlquery which I have tried.Please help
SELECT `p_Id`,`user_id`,`doc_id`,`credit` ,`app_date`,`expires_on`,(credit -debited_amount) AS credit
FROM `wp_loyalty_credits` WHERE `expires_on`>now();
推荐答案
您只需要将逻辑添加到where
子句中:
You just need to add the logic into the where
clause:
SELECT `p_Id`,`user_id`,`doc_id`,`credit` ,`app_date`,`expires_on`,
(credit -debited_amount) AS credit
FROM `wp_loyalty_credits`
WHERE `expires_on`>now() and (credit > debited_amount or debited_amount is null);
您的查询在select
中重新定义了credit
.但是,这无关紧要,因为您不能在where
子句中引用列别名.因此,列credit
就是它所使用的.如果添加表别名,则更加清楚:
Your query redefines credit
in the select
. However, that is irrelevant, because you can't refer to a column alias in the where
clause. So, the column credit
is what it used. It is clearer if you add table aliases:
SELECT lc.p_Id, lc.user_id, lc.doc_id, lc.credit, lc.app_date, lc.expires_on,
(lc.credit - lc.debited_amount) AS credit
FROM `wp_loyalty_credits` lc
WHERE lc.expires_on > now() and
(lc.credit > lc.debited_amount or lc.debited_amount is null);
这篇关于mysql中两列之间的区别的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!