筛选器在同一张表上是使用进行筛选时一直报错

#1

在SQL编辑器中使用变量筛选器时，未选择变量则不会报错，但是选择了变量（比如选择了某一个组）就很出现报错，SQL语句：SELECT
a.Date,
a.quantity,
b.组均总销量
FROM
(SELECT Date,quantity FROM ads_temp WHERE group = “3-2-C”) a
INNER JOIN (SELECT Date,round(sum(quantity) / 16, 0) 组均总销量 FROM ads_temp GROUP BY Date) b ON a.Date = b.Date;
[[WHERE a.Date == {{shijian}}]]

报错：You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘WHERE a.Date == ‘2020-12-16’’ at line 6

#2

Hi @elleryliang
You only use single equal operator on MySQL - [[WHERE a.Date = {{shijian}}]]

#3

我将‘==’改为了‘=’，然后运行还是报错的
代码：SELECT
a.Date,
a.quantity,
b.组均总销量
FROM
(SELECT Date,group,quantity FROM ads_temp ) a
INNER JOIN (SELECT Date,group,round(sum(quantity) / 16, 0) 组均总销量 FROM ads_temp GROUP BY Date) b ON a.Date = b.Date and
a.group = b.group
[[WHERE a.group = {{xiaozu}}]]
报错：Unknown column ‘ads_temp.group’ in ‘where clause’
为什么我的变量筛选会在运行时候差别不出来

1 Like
#4

@elleryliang I’m guessing that you are using a Field Filter and not a simple Data filter, which does not allow tables aliases:
https://github.com/metabase/metabase/issues/3324 - upvote by clicking :+1: on the first post

Read more about Field Filters: https://www.metabase.com/learn/building-analytics/sql-templates/field-filters.html

#5

我跟你有相同的问题，就是提示类似Unknown column ‘ads_temp.group’ in ‘where clause’，解决了吗？

问题也是出在链接了之后，如果不链接别的表就没问题。

#6

我的解决了，我的问题是一个表两次连接使用了别名，但是变量筛选器是不能用别名的