我读到了以下句子,应该使用UNNEST,但我不知道是如何使用的
select
(
select
case
when lin = 4 then 1
when lin != 4 then null
end as FLAG,
from table_1
WHERE
table_1.date = table_2.date
AND
table_1.acc = table_2.acc
) as FLAG
FROM
table_2
我有很多子查询,这就是为什么我不能使用LEFT JOIN。目前,我的table_2有1300万条记录,table_1有4亿条记录,我想要的是能够为每个帐户显示FLAG,因为我知道数据世界是不同的。
我不能使用LEFT JOIN
标量子查询生成了多个元素。。。
只需使用以下版本的查询-在逻辑上与原始查询等效,但消除了的问题
select *,
(
select 1 as FLAG
from table_1
WHERE
table_1.date = table_2.date
AND
table_1.acc = table_2.acc
AND lin = 4
LIMIT 1
) as FLAG
FROM
table_2
这表示您希望从子查询中只看到一个值,但当您加入table_1
时,date
和acc
字段上会出现重复。如果您聚合值或从table_1
中删除重复项,应该可以解决您的问题,尽管此时为什么不使用更高效的JOIN
呢?
-- This will solve your immediate problem, use any aggregation
-- technique, I picked MAX because why not. I do not know your use case.
select
(
select
MAX(case
when lin = 4 then 1
when lin != 4 then null
end) as FLAG
from table_1
WHERE
table_1.date = table_2.date
AND
table_1.acc = table_2.acc
) as FLAG
FROM
table_2
更好的方法是
select
case
when t1.lin = 4 then 1
when t1.lin != 4 then null
end as FLAG
FROM
table_2 t2
LEFT JOIN
table_1 t1 ON (
t1.date = t2.date
AND t1.acc = t2.acc
)
正如您所说,左联接对您不起作用。如果希望多个结果嵌套在同一子查询中,那么只需将子查询包装在ARRAY()
函数中,以允许重复值。
select
ARRAY(
select
case
when lin = 4 then 1
when lin != 4 then null
end as FLAG
from table_1
WHERE
table_1.date = table_2.date
AND
table_1.acc = table_2.acc
-- Now you must filter out results that will return
-- NULL because NULL is not allowed in an ARRAY
AND
table_1.lin = 4
) as FLAG
FROM
table_2