查询商品扩展多属性

第一种

SELECT COUNT(*) FROM t_product p WHERE p.productTypeId=1
 AND  EXISTS
  (SELECT 'x' FROM t_productext ext WHERE ext.ProductID=p.ProductID AND ext.ItemCode='system' AND ext.ItemValue='win7')
AND EXISTS
  (SELECT 'x' FROM t_productext ext WHERE ext.ProductID=p.ProductID AND ext.ItemCode='cpu' AND ext.ItemValue='aaaaa')
  AND EXISTS
  (SELECT 'x' FROM t_productext ext WHERE ext.ProductID=p.ProductID AND ext.ItemCode='cd-rom' AND ext.ItemValue='cd-rom')
  LIMIT 0,10

 

第二种

SELECT 
  p.ProductID AS productID,
  p.AssetCode AS assetCode,
  p.ProductTypeId AS productTypeId,
  p.ProductName AS productName,
  p.Brand AS brand,
  p.BuyPrice AS buyPrice,
  p.BuyTime AS buyTime,
  p.AddressID AS addressID,
  p.Operator AS operator,
  p.InsertTime AS insertTime,
  p.ProductStatus AS productStatus,
  p.ProductDesc AS productDesc 
FROM
  t_product p 
  LEFT JOIN t_productext ext 
    ON p.ProductID = ext.ProductID 
WHERE p.ProductTypeId = 1
  AND (
    ext.ItemCode = 'cpu' 
    AND ext.ItemValue = 'aaaaa'
  ) 
  OR (
    ext.ItemCode = 'system' 
    AND ext.ItemValue = 'win7'
  ) 
GROUP BY ext.productId 
HAVING COUNT(*) = 2