编写SQL查询而不进行子选择 [英] Writing SQL query without subselect
问题描述
我被困在仍在运行MYSQL版本3的主机上。
我需要将与AND的一系列关系弄平。
简而言之
用户
用户ID
UserEmail
UserOther
UserSkills
用户ID
SkillCode
任何用户可能拥有零，一或许多UserSkills。
我需要为拥有SkillCodes A和SkillCode的用户找到一个UserID列表
B.
如何编写查询（查询）？
I''m stuck on a host that is still running MYSQL version 3.
I need to flatten out a relationship to AND a set of criteria.
In a nutshell
User
UserID
UserEmail
UserOther
UserSkills
UserID
SkillCode
Any user may have zero, one, or many UserSkills.
I need to find a list of UserIDs for Users who have SkillCodes A and SkillCode
B.
How do I write the query (queries)?
推荐答案
Steven Stern写道：
Steven Stern wrote:
我被困在仍在运行MYSQL版本3的主机上。
即使你有4.0.x的主机，你仍然无法做
子查询。它们在4.1.x发布之前就不可用了，并且它仍然是测试版中的b $ b（或者它现在是伽马吗？）即便如此我也怀疑没有多少主机会启动
托管它，直到至少另外几个小修订已经过去。
我需要平衡与AND的一系列标准的关系。
简而言之
用户
用户ID
UserEmail
UserOther
用户技能
用户ID
SkillCode
<任何用户可能拥有零个，一个或多个UserSkills。
我需要为拥有SkillCodes A和
SkillCode B的用户找到UserID列表。
如何编写查询（查询）？
I''m stuck on a host that is still running MYSQL version 3.
Well even if you had a host with 4.0.x you still wouldn''t be able to do
subqueries. They won''t be available until 4.1.x is released, and it''s still
in beta (or is it gamma now?) Even then I suspect not many hosts will start
to host it until at least another couple of minor revisions have passed.
I need to flatten out a relationship to AND a set of criteria.
In a nutshell
User
UserID
UserEmail
UserOther
UserSkills
UserID
SkillCode
Any user may have zero, one, or many UserSkills.
I need to find a list of UserIDs for Users who have SkillCodes A and
SkillCode B.
How do I write the query (queries)?
您需要将表连接到自身（这通常比
$ b更有效） $ b，使用子查询）这一行：
SELECT用户ID
来自用户u
INNER JOIN UserSkills us1 ON u.UserID = us1.UserID
INNER JOIN UserSkills u s2 ON u.UserID = us2.UserID
WHERE us1.SkillCode =''A''
AND us2.SkillCode =''B''
我没有*测试过这个但它应该可以工作。
-
克里斯希望 - 电动工具箱 - http://www.electrictoolbox.com/
>用户
UserID
UserEmail
UserOther
UserSkills
用户ID
SkillCode
<任何用户可能拥有零个，一个或多个UserSkills。
我需要为拥有SkillCodes A和SkillCode的用户找到一个UserID列表
B.
如何编写查询（查询）？
UserID
UserEmail
UserOther
UserSkills
UserID
SkillCode
Any user may have zero, one, or many UserSkills.
I need to find a list of UserIDs for Users who have SkillCodes A and SkillCode
B.
How do I write the query (queries)?
未经测试，但这应该有效。
SELECT u .UserID
来自用户u，UserSkills a，UserSkills b
WHERE u.UserID = a.UserID和a.UserID = b.UserID and
a.SkillCode =''A''和bS killCode =''B'';
当技能代码的数量可变时，这会变得更加混乱 -
你最终可能会让PHP构造一个查询循环播放。
Gordon L. Burditt
Untested, but this should work.
SELECT u.UserID
FROM User u, UserSkills a, UserSkills b
WHERE u.UserID = a.UserID and a.UserID = b.UserID and
a.SkillCode = ''A'' and b.SkillCode = ''B'';
This gets much messier when the number of skill codes is variable -
you probably end up having PHP construct a query in a loop.
Gordon L. Burditt
Gordon Burditt写道：
Gordon Burditt wrote:
用户
用户ID
UserEmail
UserOther
用户技能
用户ID
SkillCode
任何用户都可能拥有零个，一个或多个UserSkills。
我需要为拥有SkillCodes A和
SkillCode B的用户找到UserID列表。 />
如何编写查询（查询）？
User
UserID
UserEmail
UserOther
UserSkills
UserID
SkillCode
Any user may have zero, one, or many UserSkills.
I need to find a list of UserIDs for Users who have SkillCodes A and
SkillCode B.
How do I write the query (queries)?
未经测试，但这应该有效。
SELECT u.UserID FROM User u，UserSkills a，UserSkills b
WHERE u.UserID = a.UserID and a.UserID = b.UserID and
a.SkillCode =''A''和b.SkillCode =' 'B'';
当技能代码的数量变化时，这会变得更加混乱能够 -
你可能最终让PHP在循环中构造一个查询。
Untested, but this should work.
SELECT u.UserID
FROM User u, UserSkills a, UserSkills b
WHERE u.UserID = a.UserID and a.UserID = b.UserID and
a.SkillCode = ''A'' and b.SkillCode = ''B'';
This gets much messier when the number of skill codes is variable -
you probably end up having PHP construct a query in a loop.
这与我发布的内连接查询基本相同。唯一的
差异是我发现内连接类型查询更容易阅读
因为它更清楚表格之间的关系在哪里。
我以前总是按照Gordon的方式写我的查询然后
在使用SQL Server的Microsoft商店开始工作了大约一年，并且
他们有一些很棒的标准文档，并且总是使用内部联接编写他们的查询
。我发现开始时有点奇怪，但很快就会采用我所有数据库工作的风格，因为它使得更复杂的
查询更容易阅读。
-
Chris Hope - 电动工具箱 - http://www.electrictoolbox.com/
这篇关于编写SQL查询而不进行子选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
