时间戳

首页 > 解决方案 > 在 mysql 中使用 IN 运算符收到 1292 截断不正确的 DOUBLE 值的警告

mysql - 在 mysql 中使用 IN 运算符收到 1292 截断不正确的 DOUBLE 值的警告

问题描述

我正在尝试获取依赖于发票表的列的总数。我已经成功编写了整个查询，但问题是如果我写静态值，它会给出完美的结果，但如果我写列名，它会给出错误的结果并发出警告（警告：#1292 截断不正确的 DOUBLE 值...... ）。

导致警告的查询是

SELECT
    job.customer_id,
    parties.name_of_establishment AS NAME,
        ((select IFNULL(SUM(job_details.sale_total),0) from job_details WHERE job_details.id IN (sale_invoice.items))) AS sale,
    (
    SELECT
        IFNULL(SUM(payments.amount),
        0) AS paid
    FROM
        payments
    WHERE
        payments.p_type = 'get' AND payments.p_c_id = sale_invoice.customer_id AND payments.company_id = '1'
) AS paid
FROM
    job
INNER JOIN sale_invoice ON sale_invoice.job_id = job.id 
INNER JOIN parties ON parties.id = sale_invoice.customer_id
WHERE
    company_id = '1' AND parties.name_of_establishment LIKE '%%'
GROUP BY
    job.customer_id
ORDER BY
    parties.name_of_establishment

查询结果完美的是

SELECT
    job.customer_id,
    parties.name_of_establishment AS NAME,
        ((select IFNULL(SUM(job_details.sale_total),0) from job_details WHERE job_details.id IN (8,1,5,7))) AS sale,
    (
    SELECT
        IFNULL(SUM(payments.amount),
        0) AS paid
    FROM
        payments
    WHERE
        payments.p_type = 'get' AND payments.p_c_id = sale_invoice.customer_id AND payments.company_id = '1'
) AS paid
FROM
    job
INNER JOIN sale_invoice ON sale_invoice.job_id = job.id 
INNER JOIN parties ON parties.id = sale_invoice.customer_id
WHERE
    company_id = '1' AND parties.name_of_establishment LIKE '%%'
GROUP BY
    job.customer_id
ORDER BY
    parties.name_of_establishment

错误在哪里？我已经浪费了 2 个小时来弄清楚这一点。

标签： mysqlsql

解决方案

我怀疑这sale_invoice.items是一个包含逗号分隔整数值的字符串。
如果是这种情况，则IN不起作用。
使用功能FIND_IN_SET()：

WHERE FIND_IN_SET(job_details.id, sale_invoice.items)

如果sale_invoice.items项目之间包含空格，则必须删除它们：

WHERE FIND_IN_SET(job_details.id, REPLACE(sale_invoice.items, ' ', ''))

推荐阅读