将奇怪的Mysql查询更改为使用group_concat的Postgresql [英] Changing a weird Mysql query to Postgresql that uses group_concat

问题描述

我正在将后端数据库从mysql迁移到postgres，并且正在迁移所有旧查询/函数。他们中的大多数人都是微不足道的，但是今天我遇到了一个让我挠头的事情。这里是：

I'm moving our backend database from mysql to postgres and am in the process of migrating all of our old queries / functions. Most of them are trivial to do, but today I ran across one that has me scratching my head. Here it is:

UPDATE
  k_coderound AS cr, k_coderound AS cr_m
SET
  cr.is_correct = IF(
    (SELECT GROUP_CONCAT(option_id ORDER BY variable_id) AS `values` FROM k_value    
         WHERE code_round_id=cr.id GROUP BY code_round_id) = 
    (SELECT GROUP_CONCAT(option_id ORDER BY variable_id) AS `values` FROM k_value 
         WHERE code_round_id=cr_m.id GROUP BY code_round_id),
    1,
    0
  )
WHERE     
 cr.is_master=0 
 AND cr_m.is_master=1 
 AND cr_m.object_id=cr.object_id 
 AND cr_m.content_type_id =cr.content_type_id       

我知道Postgres没有group_concat，应该使用array_agg。我的问题是我无法弄清楚到底是怎么回事-这个查询是很久以前由与我们不再在一起的人写的。使这一困难更加复杂的是Postgres中缺少IF语句。如果有人能够提供反馈或建议，我将不胜感激！

I know that Postgres has no group_concat and one should instead use array_agg. My problem is that I can't figure out what is going on exactly- this query was written ages ago by someone who isn't with us anymore. Also compounding this difficult is the lack of the IF statement in Postgres. If anyone is able to provide feedback or advice I'd greatly appreciate it!

推荐答案

很难说出意图是什么。我的方法是首先找到一个返回目标数据的SELECT语句。

It's hard to tell what the intention is. My approach to this would be to first find a SELECT statement that returns the "target" data.

类似这样的东西：

select cr.is_correct,
       array_agg(case when kv1.code_round_id = cr.id then kv1.option_id else null end, ',' order by kv1.variable_id) as kv_values1,
       array_agg(case when kv2.code_round_id = cr_m.id then kv2.option_id else null end, ',' order by kv2.variable_id) as kv_values2
from k_coderound cr
  join k_value kv1 on kv1.code_round_id = cr.id
  join k_coderound cr_m 
       on cr_m.object_id=cr.object_id 
      and cr_m.content_type_id =cr.content_type_id 
  join k_value kv2 on kv2.code_round_id = cr_m.id
where cr.is_master=0 
  and cr_m.is_master=1 

这很可能是不正确的，但是我认为它表明了如何将非标准的MySQL表达式转换为标准的SQL（从而转换为PostgreSQL）

This is most probably not correct, but I think it shows how the non-standard MySQL expressions could be translated to standard SQL (and thus to PostgreSQL)

一旦这似乎s做正确的事，我将其包装到UPDATE语句中：

Once this seems to do the right thing, I'd wrap that into the UPDATE statement:

update k_coderound cru
  set cr.is_correct = case 
                        when t.kv_values1 = t.kv_values2 then 1 
                        else 0 
                      end
from (select cr.ctid, 
             array_agg(case when kv1.code_round_id = cr.id then kv1.option_id else null end, ',' order by kv1.variable_id) as kv_values1,
             array_agg(case when kv2.code_round_id = cr_m.id then kv2.option_id else null end, ',' order by kv2.variable_id) as kv_values2
    from k_coderound cr
      join k_value kv1 on kv1.code_round_id = cr.id
      join k_coderound cr_m 
           on cr_m.object_id=cr.object_id 
          and cr_m.content_type_id =cr.content_type_id 
      join k_value kv2 on kv2.code_round_id = cr_m.id
    where cr.is_master=0 
      and cr_m.is_master=1 
) t
where t.ctid = cru.ctid

我敢肯定我错过了一些语法方面的东西，但是希望这可以帮助您入门。

I'm pretty sure I have missed some syntax things, but hopefully this gets you started.

这篇关于将奇怪的Mysql查询更改为使用group_concat的Postgresql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！