我在下面的示例数据库chinook.db（https://www.sqlitetutorial.net/sqlite-sample-database/）进行查询，但是结果很奇怪。
-- SQLite version 3.30.1 2019-10-10 20:19:45
select
Total,TotalSaleValue
from invoices
natural join (
select
InvoiceId,sum(UnitPrice) as TotalSaleValue
from invoice_items
group by InvoiceId
)
where Total != TotalSaleValue;
-- Output
Total TotalSaleValue
---------- --------------
13.86 13.86
13.86 13.86
13.86 13.86
13.86 13.86
13.86 13.86
13.86 13.86
13.86 13.86
13.86 13.86
13.86 13.86
13.86 13.86
...
我使用where Total != TotalSaleValue过滤不相等的值，并且我希望有空行。
但是，结果显示Total和TotalSaleValue具有相同的值吗？！
Total是invoices表中的值，而TotalSaleValue的值是从invoice_items表中计算的，该表是每个UnitPrice的总和发票。
-- invoice_items Table
InvoiceLineId InvoiceId TrackId UnitPrice Quantity
------------- ---------- ---------- ---------- ----------
1 1 2 0.99 1
2 1 4 0.99 1
3 2 6 0.99 1
4 2 8 0.99 1
5 2 10 0.99 1
6 2 12 0.99 1
7 3 16 0.99 1
8 3 20 0.99 1
9 3 24 0.99 1
10 3 28 0.99 1
-- invoices Table
InvoiceId ... CustomerId Total
---------- ---------- ---------- ----------
1 ... 2 1.98
2 ... 4 3.96
3 ... 8 5.94
4 ... 14 8.91
5 ... 23 13.86
6 ... 37 0.99
7 ... 38 1.98
8 ... 40 1.98
9 ... 42 3.96
10 ... 46 5.94
有人知道我的查询出了什么问题吗？
更新：
我发现如果将Total和TotalSaleValue强制转换为TEXT并赋予别名X和Y，结果将是正确的。 / p>
select
cast(Total as text) as X,cast(TotalSaleValue as text) as Y
from invoices
natural join (
select
InvoiceId,sum(UnitPrice) as TotalSaleValue
from invoice_items
group by InvoiceId
)
where X != Y;