左联接上的SQL查询问题 [英] SQL query issue on left join

问题描述

我有两个表，我需要显示只有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

这篇关于左联接上的SQL查询问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！