用case when 改写优化 sql
2014/7/4 15:55:33select a.attributeId,
a.attributeName,
a.attributeValue,
b.attributeValue,
c.attributeValue
from (select b.attributeId,
b.attributeName,
a.attributeValue,
a.product_id
from (SELECT TEMP.PRODUCT_ID,
TEMP.ID,
TEMP.INFO_VALUE AS attributeValue,
INFO.ID AS attributeId
FROM TB_PRODUCT_DATA_NEXUS TEMP, TB_PRODUCT_FIELD_INFO INFO
where TEMP.PRODUCT_FIELD_ID = INFO.ID
and temp.product_id = 111) a
right join (SELECT distinct INFO.id as attributeId,
INFO.NAME as attributeName
FROM TB_PRODUCT_DATA_NEXUS TEMP,
TB_PRODUCT_FIELD_INFO INFO
where TEMP.PRODUCT_FIELD_ID = INFO.ID
and temp.product_id in (111, 112, 113)) b
on a.attributeId = b.attributeId
order by b.attributeId
) a,
(select b.attributeId,
b.attributeName,
a.attributeValue,
a.product_id
from (SELECT TEMP.PRODUCT_ID,
TEMP.ID,
TEMP.INFO_VALUE AS attributeValue,
INFO.ID AS attributeId
FROM TB_PRODUCT_DATA_NEXUS TEMP, TB_PRODUCT_FIELD_INFO INFO
where TEMP.PRODUCT_FIELD_ID = INFO.ID
and temp.product_id = 112) a
right join (SELECT distinct INFO.id as attributeId,
INFO.NAME as attributeName
FROM TB_PRODUCT_DATA_NEXUS TEMP,
TB_PRODUCT_FIELD_INFO INFO
where TEMP.PRODUCT_FIELD_ID = INFO.ID
and temp.product_id in (111, 112, 113)) b
on a.attributeId = b.attributeId
order by b.attributeId
) b,
(select b.attributeId,
b.attributeName,
a.attributeValue,
a.product_id
from (SELECT TEMP.PRODUCT_ID,
TEMP.ID,
TEMP.INFO_VALUE AS attributeValue,
INFO.ID AS attributeId
FROM TB_PRODUCT_DATA_NEXUS TEMP, TB_PRODUCT_FIELD_INFO INFO
where TEMP.PRODUCT_FIELD_ID = INFO.ID
and temp.product_id = 113) a
right join (SELECT distinct INFO.id as attributeId,
INFO.NAME as attributeName
FROM TB_PRODUCT_DATA_NEXUS TEMP,
TB_PRODUCT_FIELD_INFO INFO
where TEMP.PRODUCT_FIELD_ID = INFO.ID
and temp.product_id in (111, 112, 113)) b
on a.attributeId = b.attributeId
order by b.attributeId
) c
where a.attributeId = b.attributeId
and a.attributeId = c.attributeId
页:
[1]