mysql - 特定参数的产品数量

Question

嗨，我有这些表格:

CREATE TABLE IF NOT EXISTS `products` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `f_category_id` int(11) DEFAULT NULL,
  `product_title` varchar(100) CHARACTER SET utf8 COLLATE utf8_czech_ci DEFAULT NULL,
  `product_enabled` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`product_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=24 ;

INSERT INTO `products` (`product_id`, `f_category_id`, `product_title`,
`product_enabled`) VALUES (1, 1, 'New product', 1);

CREATE TABLE IF NOT EXISTS `categories` (
  `category_id` int(11) NOT NULL AUTO_INCREMENT,
  `category_title` varchar(255) CHARACTER SET utf8 COLLATE utf8_czech_ci DEFAULT NULL,
  `f_parent_id` int(11) DEFAULT NULL,
  `category_lft` smallint(6) DEFAULT NULL,
  `category_rgt` smallint(6) DEFAULT NULL,
  `category_depth` smallint(6) DEFAULT NULL,
  `category_enabled` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`category_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=16 ;

INSERT INTO `categories` (`category_id`, `category_title`, `f_parent_id`,
`category_lft`, `category_rgt`, `category_depth`, `category_enabled`) VALUES
(1, 'New category', NULL, 20, 21, 0, 1);


CREATE TABLE IF NOT EXISTS `parameters` (
  `parameter_id` int(11) NOT NULL AUTO_INCREMENT,
  `parameter_title` varchar(100) CHARACTER SET utf8 COLLATE utf8_czech_ci DEFAULT NULL,
  `parameter_enabled` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`parameter_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

INSERT INTO `parameters` (`parameter_id`, `parameter_title`, `parameter_enabled`) VALUES
(2, 'Capacity', 1),
(4, 'Interface', 1),
(5, 'Colors', 1);


CREATE TABLE IF NOT EXISTS `parametervalues` (
  `parameterValue_id` int(11) NOT NULL AUTO_INCREMENT,
  `f_parameter_id` int(11) NOT NULL,
  `parameterValue_title` varchar(100) CHARACTER SET utf8 COLLATE utf8_czech_ci NOT NULL,
  `parameterValue_enabled` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`parameterValue_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=15 ;

INSERT INTO `parametervalues` (`parameterValue_id`, `f_parameter_id`, `parameterValue_title`, `parameterValue_enabled`) VALUES
(1, 2, '0.5 TB', 1),
(2, 2, '1 TB', 1),
(3, 4, 'USB 2.0', 1),
(4, 4, 'USB 3.0', 1),
(5, 4, 'eSata', 1),
(6, 4, 'Ultra ATA', 1),
(7, 4, 'SCSI', 1),
(8, 4, 'PCIe', 1),
(9, 5, 'White', 1),
(10, 5, 'Red', 1);

CREATE TABLE IF NOT EXISTS `productparametervalues` (
  `f_product_id` int(11) NOT NULL AUTO_INCREMENT,
  `f_parameter_id` int(11) NOT NULL DEFAULT '0',
  `f_parameterValue_id` int(11) NOT NULL DEFAULT '0',
  `productParameter_value` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`f_product_id`,`f_parameter_id`,`f_parameterValue_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=24 ;

INSERT INTO `productparametervalues` (`f_product_id`, `f_parameter_id`, `f_parameterValue_id`, `productParameter_value`) VALUES
(1, 0, 2, NULL),
(1, 0, 5, NULL);

并查询:

SELECT `parametervalues`.*, (SELECT COUNT(productparametervalues.f_product_id) 
FROM `productparametervalues`
LEFT JOIN `products` ON products.product_id = productparametervalues.f_product_id
LEFT JOIN `categories` ON categories.category_id = products.f_category_id
WHERE (productparametervalues.f_parameterValue_id = parametervalues.parameterValue_id) 
AND (category_lft >= '20') AND (category_rgt <= '21') 
AND (products.product_price >= '1000') AND (products.product_price <= '1000') 
AND (products.product_enabled = 1) 
GROUP BY `productparametervalues`.`f_parameterValue_id`) AS countProducts 
FROM `parameters`
LEFT JOIN `parametervalues` ON parametervalues.f_parameter_id = parameters.parameter_id 
WHERE (parameter_id = '4') AND (parameters.parameter_enabled = 1) 
AND (parametervalues.parameterValue_enabled = 1) 
ORDER BY `parameterValue_title` ASC

每个产品都属于某个类别，并且可能有一些参数(表产品参数值)。我需要获取参数列表以及具有每个参数的产品数量。查询后“countProducts”列始终为空。我该如何改变它？

Answer 1

并不是说它一定是您所追求的，但我认为该查询可以重写如下:

SELECT pv.*
     , x.ttl
  FROM parameters p 
  JOIN parametervalues pv 
    ON pv.f_parameter_id = p.parameter_id 
  JOIN 
     ( SELECT ppv.f_parameterValue_id 
            , COUNT(ppv.f_product_id) ttl
         FROM productparametervalues ppv
         JOIN products p
           ON p.product_id = ppv.f_product_id
         JOIN categories c
           ON c.category_id = p.f_category_id
          AND category_lft >= 20 
          AND category_rgt <= 21
          AND p.product_price = 1000
          AND p.product_enabled = 1
        GROUP   
           BY ppv.f_parameterValue_id
     ) x
    ON x.f_parameterValue_id = pv.parameterValue_id
 WHERE parameter_id = 4
   AND p.parameter_enabled = 1
   AND pv.parameterValue_enabled = 1
 ORDER 
    BY parameterValue_title ASC;