从查询创建表

Question

Here is my 2 sql query on the first query i want to use it as a table and on the 2nd query i want to join the 1st query which i assume a table

e.g.

First query:

SELECT sale.salesManID, sale.companyName, sale.sellingPrice 
from sale Inner Join carModel 
On (sale.companyName = carModel.companyName) AND (carModel.size>10) ;

Second Query:

SELECT salesMan.salesmanID, salesMan.name, SUM(firstQuery.sellingPrice)
FROM salesMan 
INNER JOIN firstQuery 
ON salesMan.salesmanID = firstQuery.salesManID
GROUP BY salesmanID, name;

How can i do it ? Thanks in advance.

Answer 1

If I understand the question correctly (although I'm not sure why you'd want to do it this way...):

IF object_id('tempdb..#firstQuery) IS NOT NULL DROP TABLE #firstQuery;

SELECT sale.salesManID, sale.companyName, sale.sellingPrice INTO #firstQuery FROM sale Inner Join carModel On (sale.companyName = carModel.companyName) AND (carModel.size>10) ;

SELECT salesMan.salesmanID, salesMan.name, SUM(firstQuery.sellingPrice) FROM salesMan INNER JOIN #firstQuery ON salesMan.salesmanID = firstQuery.salesManID GROUP BY salesmanID, name;