开发给出的语句如下：2014/7/4 15:55:33
select 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
改写后SELECT
MAX(info_id1)
name,
MAX(attributeValue1),
MAX(attributeValue2),
MAX(attributeValue3)
FROM (SELECT
info.name,
CASE WHEN temp.product_id = 111 THEN TEMP.INFO_VALUE END AS attributeValue1,
CASE WHEN temp.product_id = 111 THEN INFO.ID END AS info_id1,
CASE WHEN temp.product_id = 112 THEN TEMP.INFO_VALUE END AS attributeValue2,
CASE WHEN temp.product_id = 112 THEN INFO.ID END AS info_id2,
CASE WHEN temp.product_id = 113 THEN TEMP.INFO_VALUE END AS attributeValue3,
CASE WHEN temp.product_id = 113 THEN INFO.ID END AS info_id3
--row_number() over(PARTITION BY product_id ORDER BY info.id) AS cnt
FROM (SELECT product_id, info_value, product_field_id FROM tb_product_data_nexus WHERE product_id IN (111, 112, 113)) TEMP, TB_PRODUCT_FIELD_INFO INFO
WHERE TEMP.PRODUCT_FIELD_ID = INFO.ID ORDER BY info.id)
GROUP BY name
ORDER BY 1
185
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
