mysql - 使用 in 命令创建 SQL View 命令

Question

我正在用 SQL 编写一个创建 View 程序，而我的最后一个创建 View 语句给我带来了一些麻烦。对于这个 View 声明，我想检索哥伦比亚市所有大学的所有 uid。然后我想使用子查询来获取该人的名字和姓氏的信息。官方说明如下

Write a query that returns only the uid value for all universities in the city Columbia. Then use 
that query with an IN sub-query expression to retrieve the first and last names for all people that  
go to school in Columbias

我现在将发布为创建表语句编写的代码，然后发布将用于 View 语句的两个表。感谢您提前提供的帮助。

CREATE VIEW inn AS
SELECT a.uid
FROM letsdoit.university as a
WHERE b.fname, b.lname IN(SELECT b.fname, b.lname FROM letsdoit.person as b WHERE (a.city = "Columbia"));

表格是

               Table "letsdoit.university"
     Column      |         Type          |                        Modifiers                         
-----------------+-----------------------+--------------------------------------
 uid             | integer               | not null default nextval('university) uid_seq'::regclass)
 university_name | character varying(50) | 
 city            | character varying(50) | 



            Table "letsdoit.person"
 Column |         Type          |                      Modifiers                
--------+-----------------------+-----------------------------------------------
 pid    | integer               | not null default nextval('person_pid_seq'::reg class)
 uid    | integer               | 
 fname  | character varying(25) | not null
 lname  | character varying(25) | not null

Answer 1

根据问题陈述，看起来您被指示创建的第一个查询应该是 IN 的子查询，并且应该将其用作查找该大学人员的名字和姓氏的条件(因为 UID 是在 PERSON 和 UNIVERSITY 上)，所以你的观点将是这样的:

create view inn as
select fname, lname
  from letsdoit.person
 where uid in (select uid from letsdoit.university where city = 'Columbia')