你能为我重写这个吗？左连接表和子查询
CREATE TABLE TR(STUDENT_ID int, SUBJECT_ID int, grade int);
INSERT INTO tr SELECT * FROM results where results.STUDENT_ID=3;
SELECT * FROM subjects LEFT JOIN tr ON subjects.SUBJECT_ID=tr.SUBJECT_ID;
DROP TABLE TR;
是否可以将上述重写为一个查询？我在网上找遍了，还是做不到。我正在使用德比数据库。
我的想法是，我想将具有一定行数的表主题与表结果中的匹配结果（如果有针对特定学生的任何结果）连接起来。如果没有结果，我希望输出行仅包含来自表主题的数据。因此，输出行数将始终等于主题中的行数。
上面的代码运行良好，但它给我的程序带来了麻烦，因为必须创建一个表。我想避免这种情况。
CREATE TABLE TR(STUDENT_ID int, SUBJECT_ID int, grade int);
INSERT INTO tr SELECT * FROM results where results.STUDENT_ID=3;
SELECT * FROM subjects LEFT JOIN tr ON subjects.SUBJECT_ID=tr.SUBJECT_ID;
DROP TABLE TR;
Is it possible to rewrite the above as one query? I've searched all over the net and still can't do it. I'm using derby database.
The idea is that I want to join table subjects, which has a certain number of rows, with matching results from table results, if there are any for a specific student. If there are no results, I want the ouput rows to contain only data from table subjects. So, the number of output rows will always equal the number of rows in subjects.
The above code works well, but it gives me trouble in my program, because a table has to be created. I would like to avoid that.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
根据上面给出的示例查询，它可以重写，因为
我没有使用过 derby。但我认为，这应该有效。
Based on the example query given above, it can be rewritten as
I haven't worked with derby. But I think, this should work.
SELECT * FROM subject LEFT JOIN results ON subject.SUBJECT_ID=results.SUBJECT_ID AND results.STUDENT_ID=3;SELECT * FROM subjects LEFT JOIN results ON subjects.SUBJECT_ID=results.SUBJECT_ID AND results.STUDENT_ID=3;这应该相当于
假设您在某个特定学生的结果中最多有一个条目。
That should be equivalent to
Assuming you have at most one entry in results for a specific student.