PostgreSQL，带条件的总和

Question

我有两个代表账单的表格。
帐单 list 和帐单内容及其相关数据应在逻辑上“连接”以获得正确的结果。示例表列名称非常具有 self 描述性。

DROP TABLE IF EXISTS my_list;
CREATE TABLE my_list (indexl int PRIMARY KEY, docnum int, mytime text, rebate double precision, status text);

INSERT INTO my_list    
(indexl, docnum,  mytime,              rebate, status) VALUES 
    (10,      5,  '01.01.2014 15:20:31',    0,    ''), 
    (11,      6,  '02.01.2014 11:10:11',   10,    ''),  
    (12,      7,  '02.01.2014 09:15:01',    0,    ''), 
    (14,      8,  '03.01.2014 12:12:49',   12,    ''),  
    (17,      9,  '04.01.2014 08:19:19',   10,    ''), 
    (18,     10,  '04.01.2014 10:10:10',    0,   'S'), 
    (19,     11,  '04.01.2014 01:04:14',    0,   'B'),
    (21,     12,  '05.01.2014 02:49:14',    0,    ''),
    (22,     13,  '12.01.2014 08:12:17',    0,    '');

DROP TABLE IF EXISTS my_content;
CREATE TABLE my_content (indexc int PRIMARY KEY, docnum int, code int, 
       aname text, price double precision, qty double precision, secondtax double precision, meas text);

INSERT INTO my_content 
(indexc, docnum, code, aname,      price, qty, secondtax, meas) VALUES 
    (10,      5,  587, 'spaghetti',   75,   1,         0, 'kg'), 
    (15,      6, 3432, 'salt',         3,   2,         0, 'kg'), 
    (16,     12,   32, 'olive oil',    4, 1.5,         5, 'kg'), 
    (29,      7, 3432, 'salt',         3,   1,         0, 'kg'), 
    (17,      6,  449, 'sugar',        5,   2,         0, 'kg'), 
    (18,      7, 1582, 'dried eggs',  50, 1.2,         0, 'kg'), 
    (19,      8,  210, 'tomato',      80, 5.5,         0, 'kg'), 
    (20,      9,  211, 'mustard',      5,   3,         5, 'kg'), 
    (22,     10, 2014, 'clove',        1,   1,         0, 'kg'), 
    (23,      9,    8, 'oil',        120,   4,         0, 'lit'), 
    (24,     11,  816, 'laurel',       4,   1,         0, 'kg'), 
    (25,      8, 1582, 'dried eggs',  10, 0.2,         0, 'kg'), 
    (26,     12,   32, 'olive oil',    4,   1,         0, 'kg'), 
    (28,     13,   67, 'corned beef', 40, 0.5,         0, 'kg'); 

为了分析这些账单，我做了一个几乎可以正常工作的查询，但可以肯定它可以写得更好、更短、更优雅和更合适。

SELECT s.code, 
   s.aname, 
   SUM(   s.qty) AS sumused, 
   SUM( s.price * s.qty) AS bruttoprice,
   SUM((          s.price/100 * l.rebate) * s.qty) AS sumrebate, 
   SUM((s.price - s.price/100 * l.rebate) * s.qty) AS clearprice,
   SUM((s.price - s.price/100 * l.rebate) * s.qty/100 * s.secondtax) AS sumsecondtax,  
   SUM((s.price - s.price/100 * l.rebate) * s.qty - (s.price - s.price/100 * l.rebate) * s.qty/100 * s.secondtax) AS sumwithoutsecondtax  
FROM   my_content s, my_list l 
WHERE  s.docnum = l.docnum 
       AND NOT l.status='S' 
       AND l.mytime BETWEEN '02.01.2014 00:00:00' AND '05.01.2014 23:59:59' 
GROUP  BY s.code, s.aname, l.status 
ORDER  BY sumused 

1) 是否可以用变量替换查询 '(s.price/100 * l.rebate) * s.qty' 中的表达式，这样它就不必一直写了？理想情况下，如果我可以写例如 SUM(clearprice * s.secondtax)

2) 如果 bill 在列表中的状态为“S”，则必须跳过他在内容中的行，条件是 AND NOT l.status='S'。但是，如果状态为“B”，则意味着必须将具有该文档的值减去(而不是添加)到 SUM。也许更优雅的解决方案是将这些行中的数量乘以值 0-qty。
在显示的查询状态中，“B”被忽略，因为我不知道如何应用它。如何做到这一点？

3) 我实际上只需要按 s.code 对结果进行分组，但我无法从 GROUP BY 中删除 s.aname 和 l.status，因为这样查询就不起作用了。在实际情况下，如果我更改某些代码的名称，它将单独显示不需要的内容。
是否有可能仅按代码对结果进行分组，但 s.aname(比如最后一个)将显示在结果中？

我尽我所能以简单/即时的方式创建示例表和查询。
请通过建议或/和示例帮助解决具体问题。
谢谢。

编辑我在“kordirko”的帮助下解决了我的查询...

SELECT code, 
   MAX(aname)            AS aname, 
   SUM(newqty)           AS sumused, 
   SUM(price   * newqty) AS bruttoprice,
   SUM(crebate * newqty) AS sumrebate, 
   SUM(cprice  * newqty) AS clearprice,
   SUM(cprice  * newqty/100 * secondtax) AS sumsecondtax,  
   SUM(cprice  * newqty - cprice * newqty/100 * secondtax) AS sumwithoutsecondtax 
FROM (
   SELECT s.*, l.*,
          s.price/100 * l.rebate AS crebate,
          s.price - s.price/100 * l.rebate AS cprice, 
          CASE WHEN l.status = 'B' THEN 0 - s.qty ELSE s.qty END AS newqty  
   FROM   my_content s, my_list l 
   WHERE  s.docnum = l.docnum 
   AND NOT l.status='S' 
   AND l.mytime BETWEEN '02.01.2014 00:00:00' AND '05.01.2014 23:59:59'
 ) AS someAliasWhichhavetobehere
GROUP BY code 
ORDER BY sumused;

一切似乎都很好，只是我的结果值为 -0，但我认为这不会对进一步的计算造成问题。如果我怎样才能摆脱它？

Answer 1

问题一

---

是的，可以使用子查询。
在子查询中计算出一个变量some_variable，在外层查询中是这样使用的:

SELECT code, 
   aname, 
   SUM(     qty) AS sumused, 
   SUM(   price *  qty) AS bruttoprice,
   SUM((          some_variable) * qty) AS sumrebate, 
   SUM((price - some_variable) * qty) AS clearprice,
   SUM((price - some_variable) * qty/100 * secondtax) AS sumsecondtax,  
   SUM((price - some_variable) * qty - (price - some_variable) * qty/100 * secondtax) AS sumwithoutsecondtax 
FROM (
  SELECT s.*, l.*,
         s.price/100 * l.rebate some_variable
  FROM   my_content s, my_list l 
  WHERE  s.docnum = l.docnum 
         AND NOT l.status='S' 
         AND l.mytime BETWEEN '02.01.2014 00:00:00' AND '05.01.2014 23:59:59'
) as Alias
GROUP  BY code, aname, status 
ORDER  BY sumused ;

请看这里的第一个查询 --> demo

---

问题二

---

请提供更多详细信息。目前尚不清楚必须减去哪些值。您是指 price、qty 还是整个表达式，还是其他什么？

一般来说，这可以使用 CASE ... WHEN .. THEN 表达式来完成，例如:

SELECT ....
     SUM (   CASE WHEN status = 'B' 
                  THEN - price * qty
                  ELSE  price * qty
             END
          ) As column_alias,
     ....
FROM ....

或者可能以这种方式:(当 status = 'B' 则乘以 -1，否则乘以 1):

SUM (   price * qty * CASE WHEN status = 'B' THEN -1 ELSE 1 END )

---

问题三

---

我会使用MAX 或MIN 函数，这是最简单的方法。他们检索一个随机名称(最大或最小)。

SELECT code, 
   max( aname ) aname, 
   SUM(     qty) AS sumused, 
   SUM(   price *  qty) AS bruttoprice,
   SUM((          some_variable) * qty) AS sumrebate, 
   SUM((price - some_variable) * qty) AS clearprice,
   SUM((price - some_variable) * qty/100 * secondtax) AS sumsecondtax,  
   SUM((price - some_variable) * qty - (price - some_variable) * qty/100 * secondtax) AS sumwithoutsecondtax 
FROM (
  SELECT s.*, l.*,
         s.price/100 * l.rebate some_variable
  FROM   my_content s, my_list l 
  WHERE  s.docnum = l.docnum 
         AND NOT l.status='S' 
         AND l.mytime BETWEEN '02.01.2014 00:00:00' AND '05.01.2014 23:59:59'
) as Alias
GROUP  BY code, status 
ORDER  BY sumused ;

请看一下这里的第二个查询 --> demo

---

问题 4 - 它是什么:s.* 和 l.*

---

基本上这是我来自 Oracle SQL 的(坏？)习惯 :)

每个人都知道这个语法:
SELECT * FROM sometable

* 这意味着:给我表格中的所有列。

假设我们想要获取所有列，但我们还想在结果集中显示一个计算值，显而易见的方法是:

SELECT *, 
       price * qty AS amount
FROM table

这在 PostGeSQL 中工作得很好，但不幸的是在 Oracle 中这会产生语法错误。
Oracle 强制我们使用表名或别名:

SELECT table.*, 
       price * qty AS amount
FROM table;

SELECT t.*, 
       price * qty AS amount
FROM table t

使用连接时类似 - 这个工作非常好:

SELECT *
FROM table1 JOIN table2 ON ....

但这在 Oracle 中给出了一个语法错误:

SELECT *,
       price * qty AS amount
FROM table1 JOIN table2 ON ....

我们必须在这里使用表名或别名:

SELECT table1.*,
       table2.*,
       price * qty AS amount
FROM table1 JOIN table2 ON ....

SELECT t1.*,
       t2.*,
       price * qty AS amount
FROM table1 t1  JOIN table2 AS t2  ON ....

幸运的是，它符合 ANSI SQL 语法，应该适用于所有数据库:)

---

问题5 为什么将子查询结果命名为AS Alias

---

因为PostgeSQL需要给子查询一个名字(别名)。
类似的 MySql 也需要别名:

SELECT ....
FROM(
   subquery
) some_name

... or ....

SELECT ....
FROM(
   subquery
) AS some_name

只有在 Oracle 中，子查询不需要别名(但可能是)，在 Oracle 中，这工作正常:

SELECT ....
FROM (
  subquery ....
)