英文:
How do I get a row of the same type from one table or another table along with the information about from which table it was
问题
假设我有以下表格：
create table people (
human_id bigint auto_increment primary key,
birthday datetime );
create table students (
id bigint auto_increment primary key,
human_id bigint unique key not null,
group_id bigint not null );
create table teachers (
id bigint auto_increment primary key,
human_id bigint unique key not null,
academic_degree varchar(20) );
create table library_access (
access_id bigint auto_increment primary key,
human_id bigint not null,
accessed_on datetime );
现在我想以一种习惯用语的方式显示关于图书馆访问的信息，同时显示它是学生还是教师（然后是对应表的id）（假设我想要类似于 SELECT access_id,id,true_if_student_false_if_teacher FROM library_access 的结果）。
如果数据库已经部署，我该如何形成查询？如果数据库尚未部署，有哪些更好、更习惯的解决方法呢？
使用MariaDB 5.5，通过Go访问数据库，没有其他要求。
提前感谢。
英文:
Let's say I have tables:
create table people (
human_id bigint auto_increment primary key,
birthday datetime );
create table students (
id bigint auto_increment primary key,
human_id bigint unique key not null,
group_id bigint not null );
create table teachers (
id bigint auto_increment primary key,
human_id bigint unique key not null,
academic_degree varchar(20) );
create table library_access (
access_id bigint auto_increment primary key,
human_id bigint not null,
accessed_on datetime );
Now I want to display information about a library access, along with the information whether it was a student or a teacher (and then the id corresponding to the table) (let's say I want something like SELECT access_id,id,true_if_student_false_if_teacher FROM library_access), in an idiomatic way.
How do I form the query (in case such database was already deployed) and what are better and more idiomatic ways to solve that problem (in case it wasn't deployed so far).
MariaDB 5.5, database accessed by Go and nothing else.
Thanks in advance.
答案1
得分: 0
不查看您的表格或对您希望在SELECT语句中返回的内容有任何了解的情况下：
选择 *
从 people a,
students b,
teachers c,
library_access d
其中 a.human_id = b.human_id
并且 a.human_id = c.human_id
并且 a.human_id = d.human_id
英文:
Without looking at your tables or any idea as to what you want returned in the select statement:
SELECT *
FROM people a,
students b,
teachers c,
library_access d
WHERE a.human_id = b.human_id
AND a.human_id = c.human_id
AND a.human_id = d.human_id
答案2
得分: 0
你说你需要知道数据来自哪个表。你可以使用union all来实现：
select la.access_id, s.id, 'Students' as source_table
from library_access la
join students s on la.human_id = s.human_id
union all
select la.access_id, t.id, 'Teachers' as source_table
from library_access la
join teachers t on la.human_id = t.human_id
这段代码将从library_access表中获取数据，并将其与students表和teachers表进行连接。然后使用union all将两个结果集合并，并添加一个名为source_table的列来标识数据来自哪个表。
英文:
You said you need to know which table the data comes from. You can use union all for this:
select la.access_id, s.id, 'Students' as source_table
from library_access la
join students s on la.human_id = s.human_id
union all
select la.access_id, t.id, 'Teachers' as source_table
from library_access la
join teachers t on la.human_id = t.human_id
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论