按类别获取产品的全部信息 - SQL？

Question

由于这是我遇到过这种问题时发现的最好的网页，我想在这里提出这个问题。我在这里有点困惑。我是SQL语句的初学者，所以我需要你的帮助。我有三个表：

• 产品
• PRODUCT_CATEGORY
• product_to_category

我告诉你导出的SQL文件，所以你可能会得到这样的：

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; 
SET time_zone = "+00:00"; 

/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */; 
/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */; 
/*!40101 SET @[email protected]@COLLATION_CONNECTION */; 
/*!40101 SET NAMES utf8 */; 

CREATE TABLE IF NOT EXISTS `product` (
    `productID` int(6) NOT NULL AUTO_INCREMENT, 
    `productTitle` varchar(255) NOT NULL, 
    `productDescription` text NOT NULL, 
    `productPrice` double NOT NULL DEFAULT '0', 
    `productQuantity` int(5) NOT NULL, 
    PRIMARY KEY (`productID`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ; 

INSERT INTO `product` (`productID`, `productTitle`, `productDescription`, `productPrice`, `productQuantity`) VALUES 
(1, 'Chlor 5L', 'Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. \r\n\r\nDuis autem vel eum iriure dolor in hendrerit in vulputate velit esse molestie consequat, vel illum dolore eu feugiat nulla facilisis at vero eros et accumsan et iusto odio dignissim qui blandit praesent luptatum zzril delenit augue duis dolore te feugait nulla facilisi. Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat. \r\n\r\nUt wisi enim ad minim veniam, quis nostrud exerci tation ullamcorper suscipit lobortis nisl ut aliquip ex ea commodo consequat. Duis autem vel eum iriure dolor in hendrerit in vulputate velit esse molestie consequat, vel illum dolore eu feugiat nulla facilisis at vero eros et accumsan et iusto odio dignissim qui blandit praesent luptatum zzril delenit augue duis dolore te feugait nulla facilisi. \r\n\r\nNam liber tempor cum soluta nobis eleifend option congue nihil imperdiet doming id quod mazim placerat facer possim assum. Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat. Ut wisi enim ad minim veniam, quis nostrud exerci tation ullamcorper suscipit lobortis nisl ut aliquip ex ea commodo consequat. \r\n\r\nDuis autem vel eum iriure dolor in hendrerit in vulputate velit esse molestie consequat, vel illum dolore eu feugiat nulla facilisis. ', 14.95, 50), 
(2, 'Chlor 15L', 'Mit diesem Kanister kommen Sie etwa 27.000 Liter aus.', 50, 13), 
(3, 'Chlor 20L', 'Mit diesem Kanister kommen Sie etwa 37.000 Liter aus.', 60, 2), 
(4, 'Chlor 25L', 'Mit diesem Kanister kommen Sie etwa 47.000 Liter aus.', 79, 11), 
(5, 'Kieselgur 50kg', 'Eine menge Kieselgur zum säubern.', 69.99, 9); 


CREATE TABLE IF NOT EXISTS `product_category` (
    `categoryID` int(3) NOT NULL AUTO_INCREMENT, 
    `categoryName` varchar(255) NOT NULL, 
    `categoryDescription` text NOT NULL, 
    PRIMARY KEY (`categoryID`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; 


INSERT INTO `product_category` (`categoryID`, `categoryName`, `categoryDescription`) VALUES 
(1, 'Schwimmbecken', 'Hier finden Sie alle Produkte rund um das Thema Schwimmbecken.'), 
(2, 'Whirlpool', 'Hier finden Sie alle Produkte rund um das Thema Whirlpools.'), 
(3, 'Sauna', 'Hier finden Sie alle Produkte rund um das Thema Sauna.'), 
(4, 'Infrarot', 'Hier finden Sie alle Produkte rund um das Thema Infrarotkabinen.'); 


CREATE TABLE IF NOT EXISTS `product_to_category` (
    `categoryID` int(3) NOT NULL, 
    `productID` int(6) NOT NULL, 
    `productAddedTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
    PRIMARY KEY (`categoryID`,`productID`), 
    KEY `productID` (`productID`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

INSERT INTO `product_to_category` (`categoryID`, `productID`, `productAddedTime`) VALUES 
(1, 1, '2011-11-27 13:57:12'), 
(1, 2, '2011-11-27 13:57:12'), 
(1, 3, '2011-11-27 13:57:12'), 
(1, 4, '2011-11-27 13:57:12'), 
(1, 4, '2011-11-27 13:57:12'), 
(2, 1, '2011-11-27 13:57:12'); 

ALTER TABLE `product_to_category` 
    ADD CONSTRAINT `product_to_category_ibfk_2` FOREIGN KEY (`categoryID`) REFERENCES `product_category` (`categoryID`), 
    ADD CONSTRAINT `product_to_category_ibfk_3` FOREIGN KEY (`productID`) REFERENCES `product` (`productID`); 

/*!40101 SET [email protected]_CHARACTER_SET_CLIENT */; 
/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */; 
/*!40101 SET [email protected]_COLLATION_CONNECTION */; 

那么，是什么我想要做的是：我想获得所有产品的所有信息。我想按照这些分类在一页上对它们进行分组。另外我想显示每个类别的产品数量。我不知道，如何做到这一点。我在这个声明中获得了每个类别的正确数量的产品，但仅仅是产品。但我想要了解所有产品中所有产品的信息，希望我能说出我的观点，否则你可能会再问。我试图解释它。

SELECT COUNT(ptc.productID) AS productCount, pc.categoryName, p.productTitle, p.productPrice 
FROM product_category pc 
JOIN product_to_category ptc ON pc.categoryID = ptc.categoryID 
JOIN product p ON ptc.productID = p.productID 
GROUP BY pc.categoryName 

我希望你对我有一个答案......

想我想要做的是这样的一个概述：

Schwimmbecken (5 products)  
- Chlor 5L (14.95) 
- Chlor 15L (50.00) 
- Chlor.... 

Whirlpool (1 product) 
- Chlor 5L (14.95) 

希望这是足以表明亚...

Answer 1

我认为你很接近，但我会'开始'无论是产品或类别。
我会先从产品开始，链接到product_category，然后再到例如catergory等。例如：

SELECT COUNT(p.productID) AS productCount, c.categoryName, p.productTitle, p.productPrice 
FROM product p 
JOIN product_to_category ptc ON ptc.categoryID = p.productID 
JOIN category c ON ptc.catgeory_id = c.categoryID 
ORDER BY c.categoryName 

从此开始，检查结果，然后根据需要添加分组。

Answer 2

使用（修正版）

SELECT DISTINCT 
c.categoryName || '(' || c.cnt || ' products)' title, 
x.productTitle || '(' || x.productPrice || ')' productinfo 
FROM 
(
select 
pc.categoryid, 
pc.categoryName, 
count(distinct p2c.productID) cnt 
from product_category pc 
INNER JOIN product_to_category p2c ON p2c.categoryid = pc.categoryid 
group by pc.categoryid, pc.categoryName 
) c 
INNER JOIN 
(
SELECT DISTINCT 
ptc.categoryid, 
p.productTitle, 
p.productPrice 
FROM product p 
INNER JOIN product_to_category ptc ON p.productID = ptc.productID 
) x ON x.categoryid = c.categoryid 
ORDER BY 1, 2 

这会给你问什么，除了一两件事 - title将尽可能经常有产品在各自的类别重复......这部分能”牛逼通过SQL本身来处理，你将不得不处理它在你的代码...

编辑 - 按评论：

以上选择提出了两个选间内加入...冷杉st每个类别获得一行加上该类别的产品数......第二个获得所有产品的每个类别...这些通过类别ID连接...

刚刚试过它与您的示例数据，并得到以下结果：

TITLE     PRODUCTINFO 

Schwimmbecken(4 products) Chlor 15L(50) 
Schwimmbecken(4 products) Chlor 20L(60) 
Schwimmbecken(4 products) Chlor 25L(79) 
Schwimmbecken(4 products) Chlor 5L(14,95) 
Whirlpool(1 products)  Chlor 5L(14,95) 

BTW：您的样本数据，似乎离......你有两次 1，而productID 5是不是在任何地方使用...