时间戳

首页 > 解决方案 > MySQL对多个表的选择查询不显示通信数据

mysql - MySQL对多个表的选择查询不显示通信数据

问题描述

这是我当前的查询，它访问四个表。除此部分外，其他所有内容都显示正确的数据：

IF(tbl_shift.commission IS NULL,'0.00', (tbl_shift.commission*tbl_payment.subtotal)) AS comm,

这似乎没有访问表中的数据。

这是完整的查询：

SELECT tbl_payment.dateTime AS purchasedate, tbl_payment.invoiceNo AS invoice, 
        tbl_payment.subtotal AS total, tbl_payment.staffid AS employee, 
        tbl_users.fname AS firstname, tbl_users.lname AS lastname, 
        IF(tbl_shift.commission IS NULL,'0.00', (tbl_shift.commission*tbl_payment.subtotal)) AS comm, 
        (SELECT COUNT(1) AS qty FROM tbl_purchases 
        WHERE tbl_payment.invoiceNo=tbl_purchases.invoiceNo) AS qty 
FROM `tbl_payment` 
    LEFT JOIN tbl_shift ON tbl_payment.staffid = tbl_shift.employeeId 
        AND tbl_payment.dateTime BETWEEN tbl_shift.startShift 
        AND tbl_shift.endShift 
    INNER JOIN tbl_users ON tbl_payment.staffid = tbl_users.id 
WHERE tbl_payment.staffid = ".$staff." 
AND dateTime BETWEEN '".$from."' AND '".$to."' 
ORDER BY tbl_payment.dateTime DESC

标签： mysqlsqlselectmultiple-tables

解决方案

如果成功

IF(tbl_shift.commission IS NULL,'0.00', (tbl_shift.commission*tbl_payment.subtotal)) AS comm,

如果任何行都为真，这是否意味着您将在同一列中同时拥有字符串值和数字？也许试试这个，看看会发生什么：

IF(ISNULL(tbl_shift.commission)=1,0,(tbl_shift.commission*tbl_payment.subtotal)) AS comm,

推荐阅读