如何在Google表格中使用SQL查询按子字符串分组 [英] How to use a SQL query in Google Sheets to group by a substring
问题描述
在我的表格的Google表格中,我有一个列B,称为描述",我正尝试编写一个SQL查询以按列B的子字符串分组.B中的值类似于"Sell 1 Jan11 300.0/307.5扼杀",我只想按"Jan11"分组.我在Jan11上有几行,然后切换到Jan18,依此类推.
In my table, in Google Sheets, I have a column B called "description" and I'm trying to write a SQL query to group by substrings of column B. Values in B are like "Sell 1 Jan11 300.0/307.5 Strangle" and I just want to group by 'Jan11'. I have a few rows with Jan11 then it switches to Jan18, etc.
我尝试了子字符串,char索引,中点,但没有任何效果我尝试过:
I've tried substring, char index, mid, and nothing is working I've tried:
=QUERY('spgsh1378'!A1:AP,"select B, mid(B,7,5), sum(M) group by mid(B,7,5)"
=QUERY('spgsh1378'!A1:AP,"select B, substring(B,7,5), sum(M) group by substring(B,7,5)")
=QUERY('spgsh1378'!A1:AP,"select B, substring(B, 2, CHARINDEX(' ', B) - 2), sum(M) group by substring(B, 2, CHARINDEX(' ', B) - 2)"
我遇到了一个解析错误,提示
I've gotten a parse error that says
无法解析函数QUERY参数2的查询字符串:PARSE_ERROR:在第14行的第14列遇到"("("",期望其中之一:"where" ..."group" ...枢轴" ...订单" ...跳过" ...限制" ...偏移" ...标签" ...格式" ...选项" ...,"..." "..." +"..."-"..."/"..."%"..." "..."/"...%" ..."+" ...-" ..."
"Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "(" "( "" at line 1, column 14. Was expecting one of: "where" ... "group" ... "pivot" ... "order" ... "skipping" ... "limit" ... "offset" ... "label" ... "format" ... "options" ... "," ... "" ... "+" ... "-" ... "/" ... "%" ... "" ... "/" ... "%" ... "+" ... "-" ..."
推荐答案
您的尝试无效的语法.尝试这样:
your tries are not valid syntaxes. try like this:
=ARRAYFORMULA(QUERY({MID(spgsh1378!B1:B, 7, 5), spgsh1378!A1:AP},
"select Col3,Col1,sum(Col14)
where Col1 is not null
group by Col1,Col3
label sum(Col14)''", 0))
=ARRAYFORMULA(QUERY({QUERY({MID('grouping project'!A1:A, 8, 5), 'grouping project'!A1:AO},
"select Col1,sum(Col3)
where Col1 is not null
group by Col1
label Col1'month',sum(Col3)'net'",1),
IF(LEN(QUERY(QUERY({MID('grouping project'!A1:A, 8, 5), 'grouping project'!A1:AO},
"select Col1,sum(Col3)
where Col1 is not null
group by Col1
label Col1'month',sum(Col3)'net'",1),"select Col1",0)), {"";MONTH(LEFT(
QUERY(QUERY({MID('grouping project'!A1:A, 8, 5), 'grouping project'!A1:AO},
"select Col1,sum(Col3)
where Col1 is not null
group by Col1
label Col1'month',sum(Col3)'net'",1),"select Col1 offset 1", 0), 3)&1)&RIGHT(
QUERY(QUERY({MID('grouping project'!A1:A, 8, 5), 'grouping project'!A1:AO},
"select Col1,sum(Col3)
where Col1 is not null
group by Col1
label Col1'month',sum(Col3)'net'",1),"select Col1 offset 1", 0), 2)}, )*1},
"select Col1,Col2 order by Col3"))
这篇关于如何在Google表格中使用SQL查询按子字符串分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!