sql

piaoling  2012-09-20 10:02:36
SELECT `e`.*, IF(_table_status.value_id > 0, _table_status.value, _table_status_default.value) AS `status`, IF(_table_visibility.value_id > 0, _table_visibility.value, _table_visibility_default.value) AS `visibility`, IF(_table_price.value_id > 0, _table_price.value, _table_price_default.value) AS `price`, IF(_table_name.value_id > 0, _table_name.value, _table_name_default.value) AS `name`, IF(_table_short_description.value_id > 0, _table_short_description.value, _table_short_description_default.value) AS `short_description`, `stock`.`qty`, `stock`.`is_in_stock`, `url`.`request_path`, `categories`.*, GROUP_CONCAT(categories.category_id) AS `categories_ids` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_product_website` AS `product_website` ON product_website.product_id=e.entity_id AND product_website.website_id='1'
 INNER JOIN `catalog_product_entity_int` AS `_table_status_default` ON (`_table_status_default`.`entity_id` = `e`.`entity_id`) AND (`_table_status_default`.`attribute_id` = '84') AND `_table_status_default`.`store_id` = 0
 LEFT JOIN `catalog_product_entity_int` AS `_table_status` ON (`_table_status`.`entity_id` = `e`.`entity_id`) AND (`_table_status`.`attribute_id` = '84') AND (`_table_status`.`store_id` = '1')
 INNER JOIN `catalog_product_entity_int` AS `_table_visibility_default` ON (`_table_visibility_default`.`entity_id` = `e`.`entity_id`) AND (`_table_visibility_default`.`attribute_id` = '91') AND `_table_visibility_default`.`store_id` = 0
 LEFT JOIN `catalog_product_entity_int` AS `_table_visibility` ON (`_table_visibility`.`entity_id` = `e`.`entity_id`) AND (`_table_visibility`.`attribute_id` = '91') AND (`_table_visibility`.`store_id` = '1')
 INNER JOIN `catalog_product_entity_decimal` AS `_table_price_default` ON (`_table_price_default`.`entity_id` = `e`.`entity_id`) AND (`_table_price_default`.`attribute_id` = '64') AND `_table_price_default`.`store_id` = 0


 LEFT JOIN `catalog_product_entity_decimal` AS `_table_price` ON (`_table_price`.`entity_id` = `e`.`entity_id`) AND (`_table_price`.`attribute_id` = '64') AND (`_table_price`.`store_id` = '1')
 INNER JOIN `catalog_product_entity_varchar` AS `_table_name_default` ON (`_table_name_default`.`entity_id` = `e`.`entity_id`) AND (`_table_name_default`.`attribute_id` = '60') AND `_table_name_default`.`store_id` = 0
 LEFT JOIN `catalog_product_entity_varchar` AS `_table_name` ON (`_table_name`.`entity_id` = `e`.`entity_id`) AND (`_table_name`.`attribute_id` = '60') AND (`_table_name`.`store_id` = '1')
 INNER JOIN `catalog_product_entity_text` AS `_table_short_description_default` ON (`_table_short_description_default`.`entity_id` = `e`.`entity_id`) AND (`_table_short_description_default`.`attribute_id` = '62') AND `_table_short_description_default`.`store_id` = 0
 LEFT JOIN `catalog_product_entity_text` AS `_table_short_description` ON (`_table_short_description`.`entity_id` = `e`.`entity_id`) AND (`_table_short_description`.`attribute_id` = '62') AND (`_table_short_description`.`store_id` = '1')
 LEFT JOIN `cataloginventory_stock_item` AS `stock` ON stock.product_id=e.entity_id
 LEFT JOIN `core_url_rewrite` AS `url` ON url.product_id=e.entity_id AND url.category_id IS NULL AND is_system=1 AND options<> "RP" AND url.store_id=1
 LEFT JOIN `catalog_category_product` AS `categories` ON categories.product_id=e.entity_id
 INNER JOIN `catalog_category_product_index` AS `categories_index` ON categories_index.category_id=categories.category_id AND  categories_index.product_id=categories.product_id AND categories_index.store_id=1

WHERE
    (IF(_table_status.value_id > 0, _table_status.value, _table_status_default.value) = '1')
AND
    (e.type_id in ('simple', 'configurable', 'bundle', 'virtual', 'downloadable'))
AND
    (IF(_table_visibility.value_id > 0, _table_visibility.value, _table_visibility_default.value) in ('1'))
AND
    (IF(_table_price.value_id > 0, _table_price.value, _table_price_default.value) > '0')
        AND
        (e.sku is NOT NULL)
        AND
        (IF(_table_name.value_id > 0, _table_name.value, _table_name_default.value) is NOT NULL)
        AND
        (IF(_table_short_description.value_id > 0, _table_short_description.value, _table_short_description_default.value) is NOT NULL)
GROUP BY `e`.`entity_id`
类别 :  默认(706)  |  浏览(2175)  |  评论(0)
发表评论(评论将通过邮件发给作者):

Email: