如何编写SQL查询实现Excel SUMIFS函数 [英] How to write a SQL query implementing Excel SUMIFS function

问题描述

我有这样的表：

 日期销售部门商店
 02/01/2012 4.09玩具A 
 03/01/2012 5.90玩具A 
 02/01/2012 6.64玩具B 
 03/01/2012 4.71玩具B 
 02/01/2012 16.72玩具C 
 03/01/2012 1.37玩具C 
 02/01/2012 13.22电影A 
 03/01/2012 18.06电影A 
 02/01/2012 5.97电影B 
 03/01/2012 16.71电影B 
 02/01/2012 15.38电影C 
 03/01/2012 19.75电影C 
  

想要一个像这样的表，只考虑商店A和B：

 日期玩具电影
 02/01/2012 10.73 19.19 
 03/01/2012 10.61 34.77 
  

这里我们将在EXCEL中使用SUMIFS函数：

  = SUMIFS（Value; Date; $ H4; Department ; $ 3; Store;<> C）
  

我们可以写什么e在SQL中？

考虑到这是一个简短的exaple，数据库表有超过30个部门和更多的日期。我正在使用脚本

  SELECT DISTINCT Date，
 ISNULL（MAX（CASE WHEN Department ='Toys'AND St​​ore ='A'THEN Sales END），0）+ 
 ISNULL（MAX（CASE WHEN Department ='Toys'AND St​​ore ='B'THEN Sales END），0）[玩具]，
 ISNULL MAX（CASE WHEN Department ='Movies'AND St​​ore ='A'THEN Sales END），0）+ 
 ISNULL（MAX（CASE WHEN Department ='Movies'AND St​​ore ='B'THEN Sales END），0 ）[电影] 
从表$ 
 GROUP BY日期
 ORDER BY日期
  

...但效率不高。感谢任何提示。

解决方案

您的查询可以，但可以改进一点：

  SELEC Date，
 MAX（CASE WHEN Department ='Toys'THEN Sales else 0 END）as [Toys]，
 MAX（ （'A'，'B'）
 GROUP BY日期
订单日期; 
  

这将删除不必要的不同一个组由组成。它将 store 中的条件移动到其中子句，因为它适用于所有行。而且，通过在案例中包含 else 0 来删除 ISNULL（） c $ c>语句 - 所以在部门没有销售的商店将返回一个 0 而不是 NULL 。 / p>


I have a table like this:

Date        Sales   Department Store
02/01/2012   4.09    Toys       A
03/01/2012   5.90    Toys       A
02/01/2012   6.64    Toys       B
03/01/2012   4.71    Toys       B
02/01/2012   16.72   Toys       C
03/01/2012   1.37    Toys       C
02/01/2012   13.22   Movies     A
03/01/2012   18.06   Movies     A
02/01/2012   5.97    Movies     B
03/01/2012   16.71   Movies     B
02/01/2012   15.38   Movies     C
03/01/2012   19.75   Movies     C

And want a table like this, where only Store A and B are considered:

Date        Toys    Movies
02/01/2012   10.73   19.19 
03/01/2012   10.61   34.77 

Here the SUMIFS function we would use in EXCEL:

=SUMIFS(Value;Date;$H4;Department;I$3;Store;"<>C")

What can we write in SQL?

Take into consideration this is a short exaple, the database table has over 30 Departments and more many Dates. I was using the script

SELECT DISTINCT Date,
ISNULL(MAX(CASE WHEN Department = 'Toys'  AND Store = 'A' THEN Sales END),0) +
ISNULL(MAX(CASE WHEN Department = 'Toys'  AND Store = 'B' THEN Sales END),0) [Toys],
ISNULL(MAX(CASE WHEN Department = 'Movies'  AND Store = 'A' THEN Sales END),0) +
ISNULL(MAX(CASE WHEN Department = 'Movies'  AND Store = 'B' THEN Sales END),0) [Movies]
FROM Table$
GROUP BY Date
ORDER BY Date

... but it is not efficient. Thanks for any tips.

解决方案

Your query is ok, but it can be improved a bit:

SELEC Date,
      MAX(CASE WHEN Department = 'Toys' THEN Sales else 0 END) as [Toys],
      MAX(CASE WHEN Department = 'Movies' THEN Sales else 0 END) as [Movies]
FROM Table$
WHERE store in ('A', 'B')
GROUP BY Date
ORDER BY Date;

This removes the distinct, which is unnecessary with a group by. It moves the condition on store to the where clause, because it applies to all the rows. And, it removes the ISNULL() by including else 0 in the case statement -- so stores with no sales in the department will return a 0 instead of NULL.

这篇关于如何编写SQL查询实现Excel SUMIFS函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！