在查询功能中使用Case

本文介绍了在查询功能中使用Case的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

有人知道Google Spreadsheets中的Query函数是否支持使用Case语句吗?如果不是，是否还有另一种方法可以帮助产生所需的输出，例如Oracle中使用的If语句或Decode?

Does anyone know if the Query function in Google Spreadsheets supports the use of Case statements? If not, is there another method that would help produce the desired output like an If statement or Decode used in Oracle?

例如，我要查询D列，它是一个日期:

For example, I am querying column D which is a date:

=QUERY(FPA!B:Q,"SELECT Sum( Case Month(D) when 1 then 1 else 0 end) as ‘JAN’,
                       Sum( Case Month(D) when 2 then 1 else 0 end) as ‘FEB’,
                       Sum( Case Month(D) when 3 then 1 else 0 end) as ‘MAR’",1)

电子表格:

Employee   |   Start Dt   | Location
-------------------------------------
John Smith |  03-Mar-2015 | New York
Mary Jane  |  27-Jan-2015 | Los Angeles
Kim Wagner |  13-Feb-2015 | Chicago

输出:

Location    | JAN | FEB | MAR
-----------------------------
New York    |     |     |   1
Chicago     |     |   1 |
Los Angeles |   1 |     |

推荐答案

要模拟该结果，您可能必须尝试类似

To mimic that result you may have to try something like

=ArrayFormula(query({UPPER(text(B2:B, "mmm")),B2:C}, "select Col3, count(Col2) where Col2 is not null group by Col3 pivot Col1"))

这篇关于在查询功能中使用Case的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

spreadsheets