SQL 连接一对多关系

发布于 2024-08-24 22:23:40 字数 846 浏览 3 评论 0原文

好吧，这是我原来的问题；

表一包含

ID|Name  
1  Mary  
2  John

表二包含

ID|Color  
1  Red  
1  Blue
2  Blue  
2  Green  
2  Black

我想最终得到 is

ID|Name|Red|Blue|Green|Black  
1  Mary Y   Y  
2  John     Y     Y     Y

似乎因为表一中有 11 个唯一的颜色值和 1000 条记录，所以没有“好的”方法可以做到这一点。那么，还有两个问题。

有没有一种有效的方法来查询以获得这个结果？然后，我可以在应用程序中创建一个交叉表以获得所需的结果。

ID|Name|Color  
1  Mary  Red  
1  Mary  Blue  
2  John  Blue  
2  John  Green  
2  John  Black

如果我想限制返回的记录数，我该如何执行查询来执行类似的操作？

Where ((color='blue') AND (color<>'red' OR color<>'green'))

因此，使用上面的示例，我将

ID|Name|Color  
1  Mary  Blue  
2  John  Blue  
2  John  Black

通过 ADODB 连接到 Visual FoxPro 表以使用 SQL。谢谢！

原文

Ok, here was my original question;

Table one contains

ID|Name  
1  Mary  
2  John

Table two contains

ID|Color  
1  Red  
1  Blue
2  Blue  
2  Green  
2  Black

I want to end up with is

ID|Name|Red|Blue|Green|Black  
1  Mary Y   Y  
2  John     Y     Y     Y

It seems that because there are 11 unique values for color and 1000's upon 1000's of records in table one that there is no 'good' way to do this. So, two other questions.

Is there an efficient way to query to get this result? I can then create a crosstab in my application to get the desired result.

ID|Name|Color  
1  Mary  Red  
1  Mary  Blue  
2  John  Blue  
2  John  Green  
2  John  Black

If I wanted to limit the number of records returned how could I do a query to do something like this?

Where ((color='blue') AND (color<>'red' OR color<>'green'))

So using the above example I would then get back

ID|Name|Color  
1  Mary  Blue  
2  John  Blue  
2  John  Black

I connect to Visual FoxPro tables via ADODB to use SQL. Thanks!

分享到QQ

分享到微博

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

发布评论

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

彻夜缠绵 2024-08-31 22:23:40

根据您之前的问题，并查询 VFP 表，您可以通过以下方式获得结果
以下 VFP 限定查询...交叉表完成

select
      N.ID,
      N.Name,
      MAX( IIF( C.Color = "Red", "Y", " " )) Red,
      MAX( IIF( C.Color = "Blue", "Y", " " )) Blue,
      MAX( IIF( C.Color = "Green", "Y", " " )) Green,
      MAX( IIF( C.Color = "Black", "Y", " " )) Black
   FROM
      C_Names N,
      Colors C
   WHERE
      N.ID = C.ID
   GROUP BY 
      N.ID,
      N.Name

然后，由于您有其他“颜色”，只需复制相应颜色的 MAX( IIF()) 并将该列作为结果列名称...遵循模式。唯一的问题是，如果您对颜色有不同的区分大小写的拼写，那么您可能需要 UPPER( C.Color ) = "RED" （或类似的其他颜色）

From your prior question, and querying against a VFP table, you could get your results by
the following VFP qualified query... cross-tab complete

select
      N.ID,
      N.Name,
      MAX( IIF( C.Color = "Red", "Y", " " )) Red,
      MAX( IIF( C.Color = "Blue", "Y", " " )) Blue,
      MAX( IIF( C.Color = "Green", "Y", " " )) Green,
      MAX( IIF( C.Color = "Black", "Y", " " )) Black
   FROM
      C_Names N,
      Colors C
   WHERE
      N.ID = C.ID
   GROUP BY 
      N.ID,
      N.Name

Then, as you have other "colors", just copy the MAX( IIF()) for that respective color and have the column as the result column name... follow the pattern. Only issue is if you have different case-sensitve spelling of the colors, then you may need to UPPER( C.Color ) = "RED" (or similar for other colors)

回复收藏 0 原文