我需要创建更多的内部联接,以便在列中有一个值的表(而不是许多记录)。
所以不是这样:
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;
不过,与多个查询相比,我在性能方面没有改进
暂无答案!
目前还没有任何答案,快来回答吧!