switch语句在mysql中的条件内部连接

o4hqfura  于 2021-06-20  发布在  Mysql
关注(0)|答案(0)|浏览(193)

我需要创建更多的内部联接,以便在列中有一个值的表(而不是许多记录)。
所以不是这样:

SELECT U.id,
       U.name,
       FV1.value AS Azienda,
       FV2.value AS AreaManager,
       FV3.value AS Tipologia
FROM `kizgv_users` U
JOIN `kizgv_fields_values` FV1
ON FV1.item_id = U.id AND FV1.field_id = 8
JOIN `kizgv_fields_values` FV2
ON FV2.item_id = U.id AND FV2.field_id = 29
JOIN `kizgv_fields_values` FV3
ON FV3.item_id = U.id AND FV3.field_id = 33

我试图这样做是为了提高性能,因为它在500错误

SELECT U.id, U.name, FV1.value as Azienda, FV2.value as AreaManager, FV3.value as Tipologia
FROM kizgv_users U
CASE 
'kizgv_fields_values'.'field_id'
WHEN 
8
THEN
(JOIN kizgv_fields_values FV1 on FV1.item_id = U.id)
WHEN 
29
THEN
(JOIN kizgv_fields_values FV2 on FV2.item_id = U.id)
WHEN 
33
THEN
(JOIN kizgv_fields_values FV3 on FV3.item_id = U.id)

@谢谢,我试过了:stackoverflow.com/questions/7674786/mysql-pivot-table

create view omni as (
  select
    kizgv_fields_values.*,
    case when field_id = "8" then field_id end as Azienda,
    case when field_id = "29" then field_id end as AreaManager,
    case when field_id = "33" then field_id end as Tipo
  from kizgv_fields_values
);

select * from kizgv_users;

create view pivot as (
  select
    item_id,
    sum(Azienda) as Azienda,
    sum(AreaManager) as AreaManager,
    sum(Tipo) as Tipo
  from omni
  group by item_id
);

select * from pivot;

create view finale as (
  select 
    item_id,
    coalesce(Azienda, 0) as Azienda, 
    coalesce(AreaManager, 0) as AreaManager, 
    coalesce(Tipo, 0) as Tipo
  from pivot 
);

select * from finale;

不过,与多个查询相比,我在性能方面没有改进

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题