我有一个包含这些值的表：“user_id,text,text_lang,user_lang”.
字段text_lang包含表示文本语言的区域设置快捷方式(‘en’,’de’).我现在想要通过选择每个user_id具有最大出现次数的text_lang来设置(unknown / empty)’user_lang’.
我有一个工作的select语句,它返回每个user_id出现次数最多的text_lang,但我无法弄清楚如何根据ID更新每行的user_lang.
SELECT user_id,text_lang
FROM (
SELECT user_id,max(text_lang_count) OVER (PARTITION BY user_id) max_count,text_lang_count
FROM (
SELECT user_id,COUNT(text_lang) AS text_lang_count
FROM test
GROUP BY user_id,text_lang
) AS xx
) AS xy
WHERE tweet_lang_count = max_count
我假设(但不确定)MySQL/SQL: Update with correlated subquery from the updated table itself包含与我的问题密切相关的内容,但我无法使该解决方案适应这个特定问题.
解决方法
SQL Fiddle
with lang as (
select distinct on (user_id)
user_id,text_lang_count
from (
select
user_id,count(text_lang) as text_lang_count
from t
group by user_id,text_lang
) s
order by 1,3 desc,text_lang != 'en' -- in a tie english wins
)
update t
set user_lang = lang.text_lang
from lang
where t.user_id = lang.user_id
计算CTE中的主要语言(with)并在from子句中使用它
