左联接上的SQL查询问题

本文介绍了左联接上的SQL查询问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有两个表，我需要显示只有1个玩具图案的玩具的additionalcolors.范例(FW18，FK97，FK38，LX74).我使用了从Toytypes到Toyadditionalcolor的左外部联接，还执行了子查询来查找仅具有玩具名称的toypattern.但是我仍然无法获得下面的结果查询之类的结果.

I have two tables and I need to show the additionalcolors for toys that have only 1 toypattern. Example (FW18,FK97,FK38,LX74). I have used a left outer join from Toytypes to Toyadditionalcolor and also performed a subquery to find the toypattern that only have a toyname. But I am still not able to get the results like the result query below.

请告诉我我需要修改代码的哪一部分才能获得与下面的query1表相似的结果?

Please tell me which part of my code I need to modify in order to get similar results to the query1 table below?

玩具类型

ToyPattern ToyName mainColor ---------- --------------- --------- F692 Dino pink F692 Elephant pink FK38 Elephant pink FK97 Giraffe purple FW18 Sonic pink LX73 Kangaroo pink LX73 Cow blush LX73 Dog pink LX74 Cat plum

ToyAdditionalColor

ToyPattern ToyName firstColor additionalColor ---------- ---------- ----------- ---------------- FK38 Elephant pink orange FK38 Elephant pink yellow LX74 cat plum dark pink LX74 cat plum pale pink

所需的输出:

ToyPattern ToyName color 1 color 2 color 3 color 4 color 5 ----------- ---------- ------------ ------------ ------------ ------------ ------------ FK38 Elephant pink orange yellow NULL NULL FK97 Giraffe purple NULL NULL NULL NULL FW18 Sonic pink NULL NULL NULL NULL LX74 cat plum dark pink pale pink NULL NULL

下面是我的SQL代码.

Below here is my SQL code.

select distinct toytypes.toypattern, toyname, toytypes.flowerBreed, toytypes.firstColor as 'color 1' from Toytypes left join ToyAdditionalColor on ToyAdditionalColor.toypattern = Toytypes.toypattern where toytypes.toypattern in (select Toypattern from Toytypes group by toypattern having count(toypattern) < 2)

推荐答案

hmmm我希望首先将ToyAdditionalColor标准化为类似的内容:ToyPattern，ToyName，Color，SortOrder.

hmmm I'd prefer to start by normalising ToyAdditionalColor to become something like: ToyPattern, ToyName, Color, SortOrder.

示例数据为:"FK38"，大象"，粉红色"，1.

Example data would be: 'FK38', 'Elephant', 'pink', 1.

第二行:"FK38"，大象"，橙色"，2.

Second row: 'FK38', 'Elephant', 'orange', 2.

之后，您可以使用PIVOT-目前我还没有Management Studio，但我认为它可能像这样:

After that, you can use PIVOT - I haven't got Management Studio with me at the moment, but I think it might look like this:

SELECT ToyPattern , ToyName , 'color 1' = 1 , 'color 2' = 2 , 'color 3' = 3 , 'color 4' = 4 , 'color 5' = 5 FROM ( SELECT ToyPattern , ToyName , SortOrder , Color FROM dbo.ToyAdditionalColor ) AS ToyAdditionalColor PIVOT ( MAX (Color) FOR SortOrder IN (1,2,3,4,5) ) AS PivotTable