SQL Server SELECT 复合表

发布于 2024-12-17 05:24:29 字数 542 浏览 1 评论 0原文

我有以下查询：

SELECT  p.id as prod_id, * FROM products AS p 
        LEFT JOIN Product_UPC AS UPC ON UPC.ProductID = p.id
        LEFT JOIN Brands AS b ON p.brand = b.id
        LEFT JOIN productCategoryLink AS c ON c.ProductID = p.id 
        WHERE (p.id = '$this->prod_id')

一个产品可以分配给多个类别，因此我有一个由产品和类别 ID 组成的复合表。我想修改上面的查询，以便它只显示一行数据，因为目前它根据复合表中有多少个类别显示多行数据。我想以某种方式将类别 ID 的行取出并添加到一行中。

例如。

id | name | desc | category1| category2| category3 | price

这可能吗？如果是这样怎么办？

原文

I have the below query:

SELECT  p.id as prod_id, * FROM products AS p 
        LEFT JOIN Product_UPC AS UPC ON UPC.ProductID = p.id
        LEFT JOIN Brands AS b ON p.brand = b.id
        LEFT JOIN productCategoryLink AS c ON c.ProductID = p.id 
        WHERE (p.id = '$this->prod_id')

A product can be assigned to multiple categories and therefore I have a composite table consisting of product and category IDs. I want to amend the above query so that it only brings out one row of data as at the moment it bring out multiple depending on how many categories there are in the composite table. I would like to somehow have the rows of category IDs brought out and added to the one row.

eg.

id | name | desc | category1| category2| category3 | price

Is this possible? If so how?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

话少情深 2024-12-24 05:24:29

您可以尝试此方法来获取一列中的类别（稍后可以拆分它们）：

SELECT  p.id as prod_id, * FROM products AS p
        GROUP_CONCAT(c.category), 
        LEFT JOIN Product_UPC AS UPC ON UPC.ProductID = p.id
        LEFT JOIN Brands AS b ON p.brand = b.id
        LEFT JOIN productCategoryLink AS c ON c.ProductID = p.id 
        WHERE (p.id = '$this->prod_id')

如果您想获取所有产品，请使用此更改最后一行

    GROUP BY p.id

You could try this to obtain categories in one column (you can split them later):

SELECT  p.id as prod_id, * FROM products AS p
        GROUP_CONCAT(c.category), 
        LEFT JOIN Product_UPC AS UPC ON UPC.ProductID = p.id
        LEFT JOIN Brands AS b ON p.brand = b.id
        LEFT JOIN productCategoryLink AS c ON c.ProductID = p.id 
        WHERE (p.id = '$this->prod_id')

If you want to get all products, change last row with this