SQL查询获取数据 [英] SQL Query to get the data

问题描述

我是SQL开发的新手。所以我想从SO那里获得一些帮助。

I am new to SQL development. So I would like to get some help from SO.

我有三个表：学生，学生地址， student_phones 。

它们的模式大致如下：

student
-------
student_id (Primary Key)
student_name
father_name
mother_name

student_addresses
-----------------
student_address_id (Primary Key)
student_id (Foreign Key)
address
zip_code

student_phones
--------------
student_phone_id (Primary Key)
student_id (Foreign Key)
phone_type
phone_number

学生地址和学生电话是has_many关系。因此，我想从 student 的所有字段中选择特定的 student_id 的字段，但仅从该学生地址和 student_phones 中匹配的计数（总计） student_id 。我怎么得到的？

Both student_addresses and student_phones are a has_many relation. So I would like to SELECT all the fields from student for a particular student_id but only the matching counts(total) from student_addresses and student_phones for that student_id. How do I get that ?

我尝试了此查询，但返回错误：

I have tried this query, but it returns an error :

SELECT students.student_id,student_name,father_name,mother_name,
       COUNT(student_addresses.student_id) AS total_addresses,    
       COUNT(student_phones.student_id) AS total_phones
 FROM students,student_phones,student_addresses
 WHERE students.student_id = student_phones.student_id AND
       students.student_id = student_addresses.student_id AND
       students.student_id = 7;

PS：目前，我正在PostgreSQL上使用它。但是，我也想在MySQL上工作。那是否意味着我需要两个不同的查询？为此，AFAIK只能对单个查询进行查询（因为就此查询要求而言，因为MySQL和PostgreSQL都遵循相同的SQL实现）。

PS : Currently I am using this on PostgreSQL. However, I would like to work it on MySQL also. So does that mean I need to have two different queries ? AFAIK, for this purpose, just a single query will work on both (since both MySQL and PostgreSQL follow the same SQL implementation, as far as this query requirement is concerned).

我想知道是否可以不使用GROUP BY而做到这一点。因为，假设学生表有更多字段，例如12，那么我将必须将所有字段名称都同时放入SELECT和GROUP BY（AFAIK）中，这似乎有点不雅致。

I am wondering, if I can do it without using GROUP BY. Because, suppose the student table has more fields, say 12, then I will have to put all of the field names both to SELECT as well as to GROUP BY(AFAIK), which seems a bit inelegant.

推荐答案

在MySQL和PostgreSQL上应该可以使用：

This should work on MySQL and PostgreSQL:

SELECT s.student_id,
       max(s.student_name) student_name,
       max(s.father_name) father_name,
       max(s.mother_name) mother_name,
       COUNT(distinct a.student_address_id) total_addresses,    
       COUNT(distinct p.student_phone_id) total_phones
FROM students s
LEFT JOIN student_phones p ON s.student_id = p.student_id
LEFT JOIN student_addresses a ON s.student_id = a.student_id
WHERE s.student_id = 7
GROUP BY s.student_id

这篇关于SQL查询获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！