主页 / coding / 问题 / 79071869
Accepted
MarkS
Asked: 2024-10-10 03:57:01 +0800 CST

SQLite 中的总计列

  • 772

SQL 新手。 真的很新手……

以下代码产生所需的（并且正确的）输出：

SELECT
  SUM (CASE WHEN Total < 2.00 THEN 1 ELSE 0 END) AS 'A',
  SUM (CASE WHEN Total BETWEEN 2.00 and 6.99 THEN 1 ELSE 0 END) AS 'B',
  SUM (CASE WHEN Total BETWEEN 7.00 AND 15.00 THEN 1 ELSE 0 END) AS 'C',
  SUM (CASE WHEN Total > 15.00 THEN 1 ELSE 0 END) AS 'D'
FROM
 invoices
ORDER BY
 BillingCity;

 A   B   C   D
170 120 111 11

我想添加一个“E”列，其中应包含总计（412）。

不知道该怎么做。是的，我用 Google 搜索过。在这里看到一些帖子，但没有一个能满足我的要求。

sqlite

1 个回答

  1. Best Answer

    我猜你正在寻找一个等于 A + B + C + D 的解决方案。它可能类似于......

    with sub_totals as (
  SELECT
  SUM (CASE WHEN Total < 2.00 THEN 1 ELSE 0 END) AS 'A',
  SUM (CASE WHEN Total BETWEEN 2.00 and 6.99 THEN 1 ELSE 0 END) AS 'B',
  SUM (CASE WHEN Total BETWEEN 7.00 AND 15.00 THEN 1 ELSE 0 END) AS 'C',
  SUM (CASE WHEN Total > 15.00 THEN 1 ELSE 0 END) AS 'D'
FROM
 invoices
)
select 
  A, B, C, D, 
  A + B + C + D as 'E'
from sub_totals

    但是，您的总范围（几乎）涵盖了 的所有可能值Total。也就是说，E 只是行数：count as 'E'。

    SELECT
  SUM (CASE WHEN Total < 2.00 THEN 1 ELSE 0 END) AS 'A',
  SUM (CASE WHEN Total BETWEEN 2.00 and 6.99 THEN 1 ELSE 0 END) AS 'B',
  SUM (CASE WHEN Total BETWEEN 7.00 AND 15.00 THEN 1 ELSE 0 END) AS 'C',
  SUM (CASE WHEN Total > 15.00 THEN 1 ELSE 0 END) AS 'D',
  count() as 'E'
FROM
 invoices

    我说你几乎涵盖了 Total 的所有可能值，但有一个微妙的错误。例如 Total 6.995 不会被计算在内。

    SELECT
  -- Up to, but not including, 2. Or [,2)
  SUM (CASE WHEN Total < 2.00 THEN 1 ELSE 0 END) AS 'A',
  -- From 2 up to 6.99. Or [2,6.99]
  SUM (CASE WHEN Total BETWEEN 2.00 and 6.99 THEN 1 ELSE 0 END) AS 'B',
  -- From 7 up to 15. Or [7,15]
  SUM (CASE WHEN Total BETWEEN 7.00 AND 15.00 THEN 1 ELSE 0 END) AS 'C',
  -- From, but not including, 15. Or (15,]
  SUM (CASE WHEN Total > 15.00 THEN 1 ELSE 0 END) AS 'D',

    这是 的一个常见问题between。要解决这个问题，您需要混合使用包含和排除边界。通常，您需要一个包含下限和一个排除上限。这有时写为[x,y)。

    SELECT
  -- Up to, but not including, 2. Or [,2)
  SUM (CASE WHEN Total < 2.00 THEN 1 ELSE 0 END) AS 'A',
  -- From 2 up to, but not including, 7. Or [2,7)
  SUM (CASE WHEN 2.00 <= Total and Total < 7.00 THEN 1 ELSE 0 END) AS 'B',
  -- From 7 up to, but not including, 15. Or [7,15)
  SUM (CASE WHEN 7.00 <= Total and Total < 15.00 THEN 1 ELSE 0 END) AS 'C',
  -- From 15 up. [15,]
  SUM (CASE WHEN 15.00 <= Total THEN 1 ELSE 0 END) AS 'D'
FROM
  invoices
    • 1

相关问题