如何制定查询以显示一个人的所有课程 [英] How to formulate query to show all courses taken by a person

问题描述

我无法正确制定MySQL查询。我试过的所有东西都不给我所需，或者给出语法错误。

我有三个表格：客户端，课程和CoursesForClients。

客户表中只有一个人的基本坐标：身份证，姓名，地址，电子邮件等。

  + ---------- + ----------------------------- +  - ---- + 
 | ClientID |名称|地址|等等
 + ---------- + ----------------------------- + ---- -  + 
 | 10 |乔史密斯| 1 Main St. | ... | 
 | 20 |鲍勃史密斯| 2 Main St. | ... | 
 | ... | ... ... ... ... ... ... | 
 + ---------- + ----------------------------- + ---- -  + 
  

课程表存储课程名称及其ID。

  + ---------- + --------------------- -  + 
 | CourseID |名称| 
 + ---------- + ----------------------- + 
 | 100 |介绍。到主题| 
 | 200 |中级主题| 
 | 300 |高级主题| 
 | ... | ... ... ... ... 
 + ---------- + ----------------------- + 
  

CoursesForClients表具有CourseID和ClientID。一个给定的客户可以采取多种课程，因此对于客户采取的每个课程，都会有一行，包含该人员的ID和课程编号。

  + ---------- + ---------- + 
 | CourseID | ClientID | 
 + ---------- + ---------- + 
 | 100 | 1 | 
 | 200 | 1 | 
 | 300 | 1 | 
 | 100 | 2 | 
 | 200 | 2 | 
 | ... | ... | 
 + ---------- + ---------- + 
  

现在，我需要的是能够列出客户 - 仅仅一次 - 连同她所参加的所有课程。因此，查询的结果可能如下所示：

  10：Joe Smith 
 1 Main St. 
某处，AL 
 
简介。标题
中级标题
高级标题
 --------------------------- 
 
 20：Bob Smith 
 2主要
某处，AL 
 
简介。 to主题
中级主题
  

---

输出反映了客户和课程之间的关系。这里的关键在于，无论客户采取了多少课程，客户的详细信息只出现一次，然后是所有课程列表。

另外还有一个表格列出了客户端课程的成绩，并且该GradeID也存储在CoursesForClients表格中，还有另一个成绩表格，带有ID和成绩说明。但我现在不会为此担心。现在，我想要的仅仅是显示的基本输出，如上所述。

它看起来应该很容易为此设置查询，使用JOIN也许是一个GROUP BY，但我在这里有一个块，似乎无法做到正确。所以，任何帮助将非常感激。谢谢！

解决方案

表中的SQL交易。根据定义，表格有一堆行，每行都有相同的列。您的查询将产生一个结果集，它为每个课程复制了客户端的信息。

您的表示层将通过注意每个新客户端的第一行并分解客户端标题来格式化该表。您可以在php或Java或Crystal Reports或某些演示技术中执行此操作。

您的查询就是这样。

  SELECT a.id，a.name，a.address，a.etc，
 c.Name 
从客户端a 
 JOIN CoursesForClients b USING（ClientID）
 JOIN课程c USING（CourseID）
 ORDER BY a.id，c.CourseID 
  

@Strawberry对使用 USING（）的缺陷很好。这是对 ON 。

  SELECT a.id， a.name，a.address，a.etc，
 c.Name 
从客户端a 
 JOIN CoursesForClients b ON a.ClientID = b.ClientID 
 JOIN课程c ON b .CourseID = c.CourseID 
 ORDER BY a.id，c.CourseID 
  

I'm having trouble formulating a MySQL query correctly. Everything I've tried doesn't give me what's needed, or gives a syntax error.

I have three tables: Clients, Courses, and CoursesForClients.

The Clients table just has basic coordinates for a person: ID, Name, Address, email, etc.

+----------+-----------------------------+------+
| ClientID | Name        | Address       | etc. |
+----------+-----------------------------+------+
|    10    | Joe Smith   | 1 Main St.    | ...  |
|    20    | Bob Smith   | 2 Main St.    | ...  |
|   ...    | ... ...     | ... ... ...   | ...  |
+----------+-----------------------------+------+

The Courses table stores the course name and its ID.

+----------+-----------------------+
| CourseID | Name                  |
+----------+-----------------------+
|    100   | Intro. to Subject     |
|    200   | Intermediate Subject  |
|    300   | Advanced Subject      |
|    ...   | ... ... ... ...       |
+----------+-----------------------+

The CoursesForClients table has the CourseID and ClientID. A given Client can have taken multiple courses, so for every course that a Client has taken, there's a row, with the person's ID and the Course ID.

+----------+----------+
| CourseID | ClientID |
+----------+----------+
|   100    |     1    |
|   200    |     1    |
|   300    |     1    |
|   100    |     2    |
|   200    |     2    |
|   ...    |    ...   |
+----------+----------+

Now, what I need is to be able to list the Client - just once - together with all the Courses she has taken. So, the result of the query might look like this:

10:Joe Smith
1 Main St.
Somewhere, AL

Intro. to Subject
Intermediate Subject
Advanced Subject
---------------------------

20:Bob Smith
2 Main St.
Somewhere, AL

Intro. to Subject
Intermediate Subject

---

So this output reflects the relationships between the Client and the Course. The key thing here is that, no matter how many Courses a Client has taken, the Client's particulars appear only once, followed by the list of all the courses she's taken.

There's an additional twist in that there's another table that lists the Grade for the Course for the Client, and that GradeID is also stored in the CoursesForClients table, and there's another table of Grades, with ID and Grade Description. But I won't worry about this right now. For now, all I want is just the basic output shown, as described above.

It looks like it should be easy to set up a query for this, with a JOIN and maybe a GROUP BY, but I'm having a block here and can't seem to get it right. So, any help will be hugely appreciated. Thank you!

解决方案

SQL deals in tables. By definition a table has a bunch of rows, each of which has the same columns as each other. Your query is going to yield a result set that duplicates the client's information for each course she took.

Your presentation layer is going to format that table, by noticing the first row of each new client and breaking out the client header. You'll do that in php or Java or Crystal Reports or some such presentation tech.

Your query is something like this.

    SELECT a.id, a.name, a.address, a.etc,
           c.Name
      FROM Clients a
      JOIN CoursesForClients b USING(ClientID)
      JOIN Courses c USING(CourseID)
      ORDER BY a.id, c.CourseID

@Strawberry makes a good point about the pitfall of using USING(). Here is the same query on ON.

    SELECT a.id, a.name, a.address, a.etc,
           c.Name
      FROM Clients a
      JOIN CoursesForClients b ON a.ClientID = b.ClientID
      JOIN Courses c ON b.CourseID = c.CourseID
      ORDER BY a.id, c.CourseID

这篇关于如何制定查询以显示一个人的所有课程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！