这是我的SQL：
WHERE
CASE
WHEN (UC.USER = SESSION_USER() AND UC.full_access = 1)
THEN (FROS.IsVoided = false)
ELSE (FROS.IsVoided = false AND UC.USER = SESSION_USER())
END
我需要那个 IF user = 到(当前会话用户(和 full_access = 1 显示X否则Y，我可以提供更多代码和表格，但从一般的角度来看，有人可以找到我做过的任何常见错误吗？
或者，我有这个想法：
WHERE
IF(UC.USER = SESSION_USER() AND UC.full_access= 1, FROS.IsVoided = false, FROS.IsVoided = false AND UC.USER = SESSION_USER())
在WHERE子句中使用 if 语句的正确方法是什么？
溶液：
WHERE IF(((SELECT full_access FROM `TABLE` WHERE user = SESSION_USER())= 1), (FROS.IsVoided = false), (FROS.IsVoided = false AND UC.USER = SESSION_USER()))
下面是 BigQuery Standard SQL 的示例
#standardSQL
WITH `project.dataset.table` AS (
SELECT 1 col1, 2 col2, 3 col3 UNION ALL
SELECT 4, 5, 6
), users_access AS (
SELECT 'user1@gmail.com' user, 1 full_access UNION ALL
SELECT 'user2@gmail.com', 0
)
SELECT col1, col2, col3
FROM `project.dataset.table`
WHERE 1 = (SELECT full_access FROM users_access WHERE LOWER(user) = LOWER(SESSION_USER()))
OR (0 = (SELECT full_access FROM users_access WHERE LOWER(user) = LOWER(SESSION_USER()))
AND col1 > 2)
user1@gmail.com 的结果：
Row col1 col2 col3
1 1 2 3
2 4 5 6
user2@gmail.com 的结果：
Row col1 col2 col3
1 4 5 6
user3@gmail.com 的结果：
Row col1 col2 col3
Query returned zero records.
上面可以稍微重构如下(对于 full_access = 1 或 full_access = 0 的用户或根本不在列表中的用户具有相同的行为(
#standardSQL
WITH `project.dataset.table` AS (
SELECT 1 col1, 2 col2, 3 col3 UNION ALL
SELECT 4, 5, 6
), users_access AS (
SELECT 'user1@gmail.com' user, 1 full_access UNION ALL
SELECT 'user2@gmail.com', 0
)
SELECT * EXCEPT(access)
FROM `project.dataset.table`,
UNNEST([(SELECT full_access FROM users_access WHERE LOWER(user) = LOWER(SESSION_USER()))]) access
WHERE access = 1
OR (access = 0 AND col1 > 2)
注意：col1 > 2只是虚拟过滤器，实际上应该是您为情况想到的任何过滤器
问题的答案是查询SELECT部分中的一列：
SELECT (CASE WHEN UC.USER = SESSION_USER() AND UC.full_access = 1
THEN 'X' ELSE 'Y'
END) as what_you_asked_for
我不知道这与你提供的代码有什么关系。