mysql中两列的区别

2022-01-05 00:00:00 sql mysql phpmyadmin

我有两列(credit 和 debited_amount)，我想计算它们之间的差异.

I have two columns (credit and debited_amount) and I want to calculate the difference between them.

只需要检索大于零或 debited_amount 字段为 Null 的记录.

Only need to retrieve records greater than zero or if the debited_amount field is Null.

不要介意值是否为零.

这是我尝试过的 SQL 查询.请帮忙

Here is the SQL query 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);

相关文章