以下查询将返回所有产品，并将它们加入到category-product关系表中.还将对category_id上的记录和类别中的位置进行排序.

The following query would return all products and join them against the category-product relation table. It also sorts the records on category_id and position within the category.

SELECT `e`.* 
FROM `catalog_product_entity` AS `e` 
LEFT JOIN catalog_category_product ON(entity_id=product_id)
ORDER BY category_id AND position;

唯一可能发生的事情是您遇到重复的产品，因为产品可能属于女士和女士产品类别.尽管可以通过从查询中删除x.catetgory_id并添加DISTINCT来过滤重复项来简单地防止这种情况.

The only thing that can happen is that you run into duplicate products, since products might be in the ladies and woman products category. Though this can simply be prevented by removing the x.catetgory_id from the query and add a DISTINCT to filter duplicates.

下面只是该集合外观的快速模型.您仍然需要对此进行测试.希望这对您有良好的指导意义.

The underneath is just a quick mockup of how the collection should look like. You still need to test this. Hope this is a guidance in the good direction for you.

$collection = Mage::getResourceModel('catalog/product_collection')
    ->addAttributeToSelect('*')
    ->joinTable('catalog/category_product', 'entity_id=product_id', array('product_id' => 'product_id'), null, 'left')
    ->addAtributeToSort('category_id');