mysql - 查询总数减少百分比

Question

大家好，我有一些查询问题。我想用百分比减少我的总数，但这段代码给了我一些错误:

SELECT `id_sewa`,`nama_villa`,DATE_FORMAT(`tgl_transaksi`, '%d-%m-%Y'),`nama_tamu`,`cek_in`,`cek_out`, datediff(`cek_out`,`cek_in`) as rn,format(`harga`, 0),format(datediff(`cek_out`,`cek_in`) *`harga`, 0)as total,`ref`,
(
CASE
WHEN ref = "direct" THEN format(datediff(`cek_out`,`cek_in`) * `harga`, 0) - 10%
END) AS total_bersih

from sewa_villa
where tgl_transaksi BETWEEN '2017-11-12' and '2017-11-14';

我的期望:

<table border =1>
<tr>
<th>total</th>
<th>ref</th>
<th>total bersih</th>
</tr>
<tr>
<th>2,000,000</th>
<th>direct</th>
<th>1,800,000</th>
</table>

这是错误:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') AS total_bersih
from sewa_villa where tgl_transaksi BETWEEN '2017-11-12' and ' at line 6

Answer 1

您不能直接在 MySQL 上使用百分比计算(使用类似 - 10% 的东西)!您必须将 - 10% 替换为 * 0.9，正如我在 the comments 中提到的那样:

SELECT 
    `id_sewa`,
    `nama_villa`,
   DATE_FORMAT(`tgl_transaksi`, '%d-%m-%Y'),
   `nama_tamu`,
   `cek_in`,
   `cek_out`,
   DATEDIFF(`cek_out`,`cek_in`) AS rn,
   FORMAT(`harga`, 0),
   FORMAT(DATEDIFF(`cek_out`,`cek_in`) * `harga`, 0) AS total,
   `ref`, (
       CASE
           WHEN ref = "direct" THEN FORMAT(DATEDIFF(`cek_out`, `cek_in`) * `harga`, 0) * 0.9
       END) AS total_bersih
FROM sewa_villa
WHERE tgl_transaksi BETWEEN '2017-11-12' AND '2017-11-14';

---

% 可以如下使用:

• 通配符(col_name LIKE '%test%')
• 在 modulo operation 上(col_name % 5).

在您的情况下:MySQL 尝试计算模运算，但缺少 % 之后的数字。这就是 MySQL 抛出错误的原因。