从规范化数据库中进行选择时重复行

Question

我一直在研究规范化的数据库，但是，我似乎遇到了一些错误，例如重复的行。

这是我使用的查询

SELECT N.Product_Name, C.Category , S.sub_categories,SSC.Sub_Sub_Categories
FROM ( SELECT DISTINCT Product_Code, Name_Code FROM Product_Table ) P 
JOIN ( SELECT DISTINCT Name_Code, Product_Name FROM Product_Name_Table ) N on P.Name_Code = N.Name_Code 
JOIN ( SELECT DISTINCT Product_Code, Category FROM category_table ) C on P.Product_Code = C.Product_Code
join ( SELECT DISTINCT Product_Code,sub_categories From sub_categories ) S on P.Product_Code =  S.Product_Code
join ( SELECT DISTINCT Sub_Sub_Categories,Product_Code From sub_sub_categories ) SSC on P.Product_Code =  SSC.Product_Code

这是表输出。如您所见，lindt 90% chocalate，有巧克力和深色作为子猫和sub_sub_cat，但它显示为单独的行。

+---------------------------------------------------------------------------+-------------------------+----------------------------------+
| Product_Name                  Category           sub_cat     sub_sub_cat |                                            |               
| Chocolate Bar-90%(Dark) Food Cupboard     Chocolates      Blocks    |                         |                                  |
| Chocolate Bar-70%(Dark) Food Cupboard     Chocolates      Blocks    |                         |                                  |
| Chocolate Bar-70%(Dark) Food Cupboard     Dark            Blocks    |                         |                                  |
| Chocolate Bar-90%(Dark) Food Cupboard     Chocolates     Chocolates |                         |                                  |
| Chocolate Bar-70%(Dark) Food Cupboard     Chocolates     Chocolates |                         |                                  |
| Chocolate Bar-70%(Dark) Food Cupboard     Dark           Dark       |                         |                                  |
|                                                                           |                         |                                  |
+---------------------------------------------------------------------------+-------------------------+----------------------------------+

我想要的输出

Product_Name              Category            sub_cat     sub_sub_cat |     |
Chocolate Bar-90%(Dark) Food Cupboard     Chocolates,dark  Blocks,Chocolates    
Chocolate Bar-70%(Dark) Food Cupboard     Chocolates,dark  Blocks,Chocolates        

此问题的任何提示，建议或替代解决方案将不胜感激。

Answer 1

如果您使用的是SQL Server，请尝试使用string_agg()。

select
Product_Name,
category,
string_agg(sub_cat, ',') within group (order by category) as sub_cat,
string_agg(sub_sub_cat, ',')  within group (order by category) as sub_sub_cat
from myTable
group by
Product_Name,
category

Answer 2

尝试在第一个select上添加DISTINCT：

SELECT DISTINCT N.Product_Name, C.Category , S.sub_categories,SSC.Sub_Sub_Categories
FROM ( SELECT DISTINCT Product_Code, Name_Code FROM Product_Table ) P 
JOIN ( SELECT DISTINCT Name_Code, Product_Name FROM Product_Name_Table ) N on P.Name_Code = N.Name_Code 
JOIN ( SELECT DISTINCT Product_Code, Category FROM category_table ) C on P.Product_Code = C.Product_Code
join ( SELECT DISTINCT Product_Code,sub_categories From sub_categories ) S on P.Product_Code =  S.Product_Code
join ( SELECT DISTINCT Sub_Sub_Categories,Product_Code From sub_sub_categories ) SSC on P.Product_Code =  SSC.Product_Code