是否可以封装sql-select结果？ [英] Is it possible to encapsulate sql-select result?

问题描述

简单SQL查询：

（选择 e.lastName  as 名称，计数（o.orderid） as  numOrd 来自 
员工e  join 订单o   e.employeeid = o.employeeid 
  group   by  e.lastName）

和结果

 Buchanan  42  
 Callahan  104  
 Davolio  123  
 Dodsworth  43  

我的问题是如何在SQL中实现类似的东西：

 let queryResult = 
（ select  e.lastName  as 名称，计数（o.orderid） as  numOrd 来自 
员工e  join 订单o  on  e.employeeid = o.employeeid 
  group   by  e.lastName ）

之后写这样的东西，这将是输出：

 选择 AVG（qr.numOrd）来自 queryResult qr 

是否可以不创建任何新表？

解决方案

您要求的方式无法在SQL中运行。你必须选择：

1.声明一个表变量并将第一个选择的结果插入其中，而不是在第二个选择中使用

2.使用第一个在from部分中选择 - select * from（从表中选择字段）

答案是CTE的

http://technet.microsoft.com/en-us/library/ms190766（v = SQL.105）的.aspx [ ^

Simple SQL query:

(select e.lastName as Name, count(o.orderid) as numOrd from
Employees e join Orders o on e.employeeid=o.employeeid
group by e.lastName)

and result

Buchanan    42
Callahan    104
Davolio     123
Dodsworth   43

My question is how to achieve in SQL something like that:

let queryResult = 
(select e.lastName as Name, count(o.orderid) as numOrd from
Employees e join Orders o on e.employeeid=o.employeeid
group by e.lastName)

and after that to write something like this, which will be the output:

select AVG(qr.numOrd) from queryResult qr

Is it possible without creating any new tables?

解决方案

The way you ask for can't work in SQL. You have to options:
1. Declare a table variable and insert the results of the first select into it, than use it in the second select
2. Use the first select in the from part - select * from (select field from table)

The answer was CTE's
http://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx[^]

这篇关于是否可以封装sql-select结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！