SQL 使用 WITH 关键字创建视图语句 [英] SQL Create view statement using WITH keyword

问题描述

我在 SQL 中编写此创建视图语句时遇到问题.我想从表格中为去科罗拉多大学 (uid = 2) 的人获取 personID、名字和姓氏.然后我想使用 WITH 子句将该表与我的 body_composition 表结合起来，并打印出 body_composition 表中的所有内容.这是我正在执行的查询的确切定义.

I am having issues writing this Create view statement in SQL. I want to get personID, first name, and last name from a table for people that go to the University of Colorado (uid = 2). Then I want to use the WITH clause to combine this table with my body_composition table and print out everything in the body_composition table as well. Here is the exact definition of the query I am making.

首先，编写一个查询，返回此人的 id (pid)、名字(fname) 和姓氏 (lname) 来自所有来自去科罗拉多大学的人.然后，将该查询放入一个 WITH 子句并将其用作公用表表达式 (CTE) 来组合通过内连接得到身体成分表的结果参加大学的人的身体成分科罗拉多州.

First, write a query that returns the person’s id (pid), first name (fname) and last name (lname) from all people who are from the people who go to the University of Colorado. Then, place that query in a WITH clause and use it as a common table expression (CTE) to combine the result with the body composition table via an inner join to get the body compositions for people who attend the University of Colorado.

我尝试运行我的创建视图语句，但出现此错误

I try and run my create view statement I get this error

错误:what"处或附近的语法错误第 7 行:WITHwhat.body_composition 作为 c

ERROR: syntax error at or near "what" LINE 7: WITH what.body_composition as c

这是我创建视图语句的代码以及我正在使用的表.

Here is my code for this create view statement along with the tables that I am using.

CREATE VIEW withclause AS
SELECT a.pid, a.fname, a.lname
FROM what.person AS a
INNER JOIN what.university AS b
on a.uid = b.uid
WHERE uid = 2
WITH what.body_composition AS c
SELECT *
FROM what.body_composition;

这是我使用的三个表

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


 Table "what.body_composition"
 Column |  Type   | Modifiers 
--------+---------+-----------
 pid    | integer | not null
 height | integer | not null
 weight | integer | not null
 age    | integer | not null


    Table "what.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

推荐答案

根据问题描述，我很确定这是您想要的:

Based on the problem description I'm pretty sure this is what you want:

CREATE VIEW withclause AS

WITH cte AS (
  SELECT p.pid, p.fname, p.lname
  FROM what.person as p
  INNER JOIN what.university as u
  ON p.uid = u.uid
  WHERE p.uid = 2
)

SELECT cte.pid, cte.fname, cte.lname, c.age, c.height, c.weight
FROM cte
INNER JOIN what.body_composition c on c.pid = cte.pid;

示例 SQL Fiddle(基于 Postgres，我假设你重新使用基于 psql 标签).

Sample SQL Fiddle (based on Postgres which I'm assuming you're using based on the psql tag).

这篇关于SQL 使用 WITH 关键字创建视图语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！