普适性在一个SQL查询中组合多个和

Question

我有一个库存数据库，其中包含文章编号和库存变化。我想要一个查询，将有列文章编号，总和(1周前的变化(，总和(2周前的改变(，总和，等

我有一个可以包含一个和的查询，但如何在同一个查询中获得多个和？我试过了，但总是以错误的结果告终。有人有解决方案吗？

查询：

SELECT Article, ROUND(SUM ("Changes" ),0) AS Usage01
FROM "Stock"
WHERE "Date" BETWEEN (REPLACE ((CONVERT ((CURDATE ()-14),SQL_CHAR )),'-' ,'' ))AND (REPLACE ((CONVERT ((CURDATE ()-8),SQL_CHAR )),'-' ,'' )) 
GROUP BY "Stock" ."Article" 
ORDER BY "Stock" ."Article"

SELECT Article, ROUND(SUM ("Changes" ),0) AS Usage02
FROM "Stock"
WHERE "Date" BETWEEN (REPLACE ((CONVERT ((CURDATE ()-21),SQL_CHAR )),'-' ,'' ))AND (REPLACE ((CONVERT ((CURDATE ()-15),SQL_CHAR )),'-' ,'' )) 
GROUP BY "Stock" ."Article"
ORDER BY "Stock" ."Article"

SELECT Article, ROUND(SUM ("Changes" ),0) AS Usage03
FROM "Stock"
WHERE "Date" BETWEEN (REPLACE ((CONVERT ((CURDATE ()-28),SQL_CHAR )),'-' ,'' ))AND (REPLACE ((CONVERT ((CURDATE ()-22),SQL_CHAR )),'-' ,'' )) 
GROUP BY "Stock" ."Article"
ORDER BY "Stock" ."Article"

Answer 1

SELECT ARTICLE,
SUM
(
CASE 
WHEN DATE BETWEEN FIRST_DAY_OF_WEEK AND LAST_DAY_OF WEEK THEN CHANGES
ELSE NULL
END
)AS WEEK,
SUM
(
CASE 
WHEN DATE BETWEEN FIRST_DAY_OF_PREVIOUS_WEEK AND LAST_DAY_OF_PREVIOUS_WEEK THEN CHANGES
ELSE NULL
END
)AS WEEK_PREVIOUS
FROM YOUR_TABLE
GROUP BY ARTICLE

我想你可以用"；条件聚合"；。很抱歉，我从来没有见过普适sql，所以不能写查询，但这个想法看起来像上面的查询

Answer 2

您可以使用这个：

SELECT Article, 
ROUND(SUM (CASE WHEN "Date" BETWEEN (REPLACE ((CONVERT ((CURDATE () - 14), SQL_CHAR )), '-' , '' )) AND (REPLACE ((CONVERT ((CURDATE () - 8), SQL_CHAR )), '-' , '' )) THEN "Changes" END ), 0) AS Usage01,
ROUND(SUM (CASE WHEN "Date" BETWEEN (REPLACE ((CONVERT ((CURDATE () - 21), SQL_CHAR )), '-' , '' )) AND (REPLACE ((CONVERT ((CURDATE () - 15), SQL_CHAR )), '-' , '' ))  THEN "Changes" END ), 0) AS Usage02, 
ROUND(SUM (CASE WHEN "Date" BETWEEN (REPLACE ((CONVERT ((CURDATE () - 28), SQL_CHAR )), '-' , '' )) AND (REPLACE ((CONVERT ((CURDATE () - 22), SQL_CHAR )), '-' , '' ))  THEN "Changes" END ), 0) AS Usage03
FROM "Stock" s
GROUP BY s."Article" 
ORDER BY s."Article";