当前位置：文江博客话题详情

帮助处理复杂的 SQL 查询

发布于 2024-09-07 19:11:34 字数 675 浏览 0 评论 0原文

在 SQL 查询方面我还是个新手，所以我希望有人能帮我一把。

假设我有 3 个表：Skill、Set 和 Contact，以及 2 个链接表：SkillSet 和 ContactSet。（这 3 个表有一个“ID”列用作主键）

联系人可以有任意数量的集合，反之亦然（多对多）

集合可以有任意数量的技能，反之亦然（也多对多）

我想要的是，当提供技能的 ID 时，返回包含该技能的集合的每个联系人。

还在我身边吗？

这是我到目前为止所得到的：

SELECT        Set.ID as expr1
FROM SkillSet
WHERE Skill.ID = @SkillID
//this selects every set containing the Skill.


SELECT         Contact.ID
FROM           ContactSet
WHERE SET.ID = ?
//this is where I run into problems. I want to use the records returned from
//the previous SELECT query (expr1) but I'm unsure how. 
//Would it be expr1.ID or expr1.Set.ID?

谢谢。

原文

I'm still a neophyte when it comes to SQL queries, so I was hoping someone could lend me a hand.

Assume I have 3 tables, Skill, Set, and Contact, and 2 linking tables, SkillSet and ContactSet. (the 3 tables have an "ID" column that is being used as a primary key)

Contacts can have any number of sets, and vice versa (many-to-many)

Sets can have any number of skills, and vice versa (also many-to-many)

What I want is, when presented with a skill's ID, to return every contact with a set containing that skill.

Still with me?

Here's what I've got so far:

SELECT        Set.ID as expr1
FROM SkillSet
WHERE Skill.ID = @SkillID
//this selects every set containing the Skill.


SELECT         Contact.ID
FROM           ContactSet
WHERE SET.ID = ?
//this is where I run into problems. I want to use the records returned from
//the previous SELECT query (expr1) but I'm unsure how. 
//Would it be expr1.ID or expr1.Set.ID?

Thank you.

分享到QQ

分享到微博

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

发布评论

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

芯好空 2024-09-14 19:11:34

这应该可以正常工作，或者至少可以引导您找到最终的解决方案，您只需要一个查询：

declare @skillid int 
set @skillid = 1

SELECT C.*
  FROM  contact c
    inner join SetContact SC on (sc.contactId = C.contactId)
    inner join SkillSet SS on (ss.SetId = SC.SetId)
  WHERE (SS.SkillId = @SkillId)

this should work just fine or at least lead you to the final solution, you only need the one query:

declare @skillid int 
set @skillid = 1

SELECT C.*
  FROM  contact c
    inner join SetContact SC on (sc.contactId = C.contactId)
    inner join SkillSet SS on (ss.SetId = SC.SetId)
  WHERE (SS.SkillId = @SkillId)

回复收藏 0 原文

~没有更多了~