mysql - SQL 获取小于/介于十进制值 (13,6) 之间

Question

我正在尝试为我正在制作的图表获取 0 到 10,000 之间的值但首先我需要让它在 MySQL 中工作，该列名为totalExcl，类型=decimal(13,6)

正常情况下，当我执行AND o.total_paid_tax_excl BETWEEN (1.00) AND (10.0000)时，它会起作用，但这不适用于decimal(13,6)

这就是我的表格的样子，但有更多结果!

╔══════════════ ╦═════════╗
║   count       ║ lastname║
╠══════════════ ╬═════════╣
║ 798.500000    ║ Honny   ║
╚══════════════ ╩═════════╝
╠══════════════ ╬═════════╣
║ 126108.710000 ║ Bont    ║
╚══════════════ ╩═════════╝

我想从列名 'count' 获取一个介于 1 和 10.000 之间的值

谢谢

SELECT 
    SUM(o.total_paid_tax_excl) AS count,
    IFNULL(CONCAT(e.firstname, ' ', e.lastname),
            'Web order') AS lastname
FROM
    ex.ps_orders o
        LEFT JOIN
    ex.ps_oxo_quotation q ON o.id_order = q.idOrder
        LEFT JOIN
    ex.ps_employee e ON q.idEmployee = e.id_employee
WHERE
    current_state IN (3 , 4, 5, 9, 13, 15, 20, 22, 23, 24, 25, 121) 
GROUP BY q.idEmployee
ORDER BY count DESC

Answer 1

试试这个:

SELECT
exe.*
FROM
(
SELECT 
    SUM(o.total_paid_tax_excl) AS count,
    IFNULL(CONCAT(e.firstname, ' ', e.lastname),
            'Web order') AS lastname
FROM
    ex.ps_orders o
        LEFT JOIN
    ex.ps_oxo_quotation q ON o.id_order = q.idOrder
        LEFT JOIN
    ex.ps_employee e ON q.idEmployee = e.id_employee
WHERE
    current_state IN (3 , 4, 5, 9, 13, 15, 20, 22, 23, 24, 25, 121) 
GROUP BY q.idEmployee
ORDER BY count DESC
) exe
where exe.count between 1 and 10000;