涉及不同列数查询的 SQL UNION 困难以及其他问题
我是 Toastmasters 俱乐部的一员，刚刚被任命负责安排哪些成员在每周的会议中扮演哪些角色。我正在编写一个小应用程序来帮助管理这个问题，我的想法是数据库可以帮助我公平地确定哪个成员最应该担任每个角色。
为了简化问题，假设我的数据库有两个表... MEMBER 和 ROLE_PERFORMED：
MEMBER
------
ID int
NAME varchar
ROLE_PERFORMED
--------------
MEMBER_ID int (half of primary key, and also foreign key)
DATE date (other half of primary key)
ROLE int (0-11, mapped to an enumeration at the application layer)
我的目标是为每种角色类型编写一个 SQL 查询，这将为我提供：
MEMBER.NAME，对于MEMBER表中的所有行第二列，包含与该成员和角色匹配的最新
ROLE_PERFORMED.DATE值...或者NULL（或其他占位符），如果该成员从未担任过该角色
ROLE_PERFORMED.DATE 值与该成员和角色匹配...或者如果该成员从未担任过该角色， 然后可以按日期/占位符列进行排序，并按照谁没有执行该角色的时间最长的顺序分配角色。像这样的事情：
ROLE NAME MOST_RECENTLY_PERFORMED
--------------------------------
1 John <null>
1 Joe 2010-02-25
1 Bob 2010-09-14
我当前采用的方法是尝试在一个捕获已执行该角色的成员的 SELECT 和第二个 SELECT 之间使用 UNION...抓住从未扮演过该角色的成员。像这样的事情：
SELECT m.name, r.date FROM member m, role_performed r WHERE m.id = r.member_id and r.role = 1
UNION
SELECT m.name, (NULL?????) FROM member m, role_performed r WHERE (?????)
但是，这存在三个问题，我希望有人能找到解决方案：
第一个
SELECT抓取所有日期成员执行了该角色，而不仅仅是最近的。第二个
SELECT导致UNION失败，因为我找不到用于第二个日期列的可接受的占位符值...并且UNION要求每个查询返回相同数量的列。第二个
WHERE子句完全超出了我有限的 SQL 技能范围。如何查找所有MEMBER行，这些行不具有与该成员和该角色匹配的ROLE_PERFORMED行？请记住，可能仍然有ROLE_PERFORMED行与该成员和某些其他角色匹配。
就像我说的，我不是世界上最伟大的 SQL 专家……所以如果我让这个事情变得比需要的更复杂，我会欢迎完全不同的方法。哎呀，我希望有一个更好的问题标题来帮助有意义地表达我的要求！预先非常感谢。
I'm part of a Toastmasters club, and just got put in charge of scheduling which members serve which roles in the weekly meetings. I'm writing a small app to help manage this, with the idea that a database can help me determine fairly which member is most overdue to serve in each role.
To simplify matters, let's say that my database has two tables... MEMBER and ROLE_PERFORMED:
MEMBER
------
ID int
NAME varchar
ROLE_PERFORMED
--------------
MEMBER_ID int (half of primary key, and also foreign key)
DATE date (other half of primary key)
ROLE int (0-11, mapped to an enumeration at the application layer)
My goal is to write a SQL query for each role type, which will give me:
MEMBER.NAME, for all rows in theMEMBERtableA second column, containing the most recent
ROLE_PERFORMED.DATEvalue matching that member and role... or else aNULL(or some other placeholder) if the member has never served in that role
I could then order by the date/placeholder column, and assign roles in order of who has gone the longest time without performing that role. Something like this:
ROLE NAME MOST_RECENTLY_PERFORMED
--------------------------------
1 John <null>
1 Joe 2010-02-25
1 Bob 2010-09-14
The approach I'm currently taking is to try a UNION... between one SELECT that grabs those members who have performed the role, and a second SELECT grabbing members who have never performed the role. Something like this:
SELECT m.name, r.date FROM member m, role_performed r WHERE m.id = r.member_id and r.role = 1
UNION
SELECT m.name, (NULL?????) FROM member m, role_performed r WHERE (?????)
There are three problems with this, however, for which I'm hoping someone may have solutions:
The first
SELECTgrabs all dates for which the member performed the role, not just the most recent.The second
SELECTcauses theUNIONto fail, because I can't find an acceptable placeholder value to use for the second date column... andUNION's require each query to return the same number of columns.The second
WHEREclause is simply beyond my limited SQL skills. How do you find allMEMBERrows, which do not have aROLE_PERFORMEDrows matching that member and that role? Bear in mind that there can still beROLE_PERFORMEDrows matching that member and some other role.
Like I said, I'm not the greatest SQL guru in the world... so I would welcome altogether different approaches if I'm making this more complicated than it needs to be. Heck, I would welcome a better question title to help meaningfully present what I'm asking! Thanks a lot in advance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
像这样的东西？
something like this?
本质上，您必须像这样使用 CROSS JOIN：
SELECT
R.RoleId,
最大（R.日期），
M.姓名
从
会员M
内连接
（
选择
R1.角色 ID,
R1.会员ID,
日期
从
Role_Performed R 角色_执行 R
右外连接
（
选择
R2.角色 ID,
R1.会员ID,
R2.日期
从
角色_执行 R1
交叉连接 Role_Performed R2
) R1 ON R1.Date = R.Date AND R1.MemberId = R.MemberId
）
R ON M.MemberId = R.MemberId
按 R.RoleId、M.Name 分组
订购依据
R.角色
去
Essentially you will have to use CROSS JOIN like this:
SELECT
R.RoleId,
Max(R.Date),
M.Name
FROM
Member M
INNER JOIN
(
SELECT
R1.RoleId,
R1.MemberId,
R.Date
FROM
Role_Performed R
RIGHT OUTER JOIN
(
SELECT
R2.RoleId,
R1.MemberId,
R2.Date
FROM
Role_Performed R1
CROSS JOIN Role_Performed R2
) R1 ON R1.Date = R.Date AND R1.MemberId = R.MemberId
)
R ON M.MemberId = R.MemberId
GROUP BY R.RoleId, M.Name
ORDER BY
R.RoleId
GO
其中一些问题非常接近我需要的一切，我非常感谢每个回复的人。然而，最终我最终通过应用程序层中的一些逻辑完成了我需要的一切。其中一个 SQL 查询可能可以在更完整的数据库平台上完成工作，但嵌入式 Derby 数据库可能无法支持足够的标准。
Some of these queries come really close to everything I need, and I really appreciate everyone who responded. However, ultimately I ended up accomplishing everything I needed with some logic up in the application layer. One of these SQL queries might get the job done on a more complete database platform, but perhaps the embedded Derby database just doesn't support enough of the standard.
我会这样做希望这有助于
SELECT member.name ，
最大（角色执行日期）
来自会员
LEFT JOIN role_performed on role_perfomed.member_id = member.id
按角色分组，成员.name
按角色排序，成员.name
I would do it like this hope this helps
SELECT member.name ,
Max(role_performed.date)
FROM member
LEFT JOIN role_performed on role_perfomed.member_id = member.id
GROUP BY ROLE,member.name
ORDER BY ROLE,member.name