从两个查询中减去结果 [英] Substract result from two querys

问题描述

我有两个查询，我需要减去，第一个查询我得到这样的结果：

 ROOMS | ROOM_Type 
  3  | JRS 
  7  | HPT 

第二个返回

房间| ROOM_Type 
  13  | JRS 
  11  | HPT 

i需要获得1个结果，其中两个查询相互减去，总计数如此：

房间| ROOM_Type 
  10  | JRS 
  4  | HPT 

我的尝试：



我尝试了EXCEPT方法和（选择（...） - 选择（...）没有结果我不知道如何使它们减去它们的第一个结果

这是我的2个查询< br $>


查询1

< pre> SELECT SUM（ROOMS），[ROOMS_Type]  FROM  
 [预留] 
  WHERE （SALIDA> N '  2016-10-03'） AND （ESTATUS = N '  RV'） AND （LLEGADA< = N '  2016-10-03'）
  GROUP   BY  [ROOMS_Type] 
  ORDER   BY  ROOMS_Ty pe 

查询2：

< pre> SELECT SUM（ ROOMS），[ROOMS_Type] 
  FROM  [CHECKINS] 
  WHERE （SALIDA> ; N '  2016-10-03'） AND （ESTATUS = N '  IN'）
  GROUP   BY  [ROOMS_Type] 

解决方案

更新使用CTE。在Common Table Expression中包装两个查询。



这应该这样做。我后来看到了Query1和2，但概念仍然相同，在你的情况下，假设RESERVATION =＃test1，CHECKINS =＃test2



< pre lang =SQL> WITH cteReservation AS （ SELECT SUM（ROOMS）ROOMS，[ROOMS_Type] FROM
[预订]
WHERE （SALIDA> N ' 2016-10-03'） AND （ESTATUS = N ' RV'） AND （LEGADA< = N ' 2016-10-03'）
GROUP BY [ROOMS_Type]
- ORDER BY ROOMS_Type
），cteCheckins AS （
SELECT SUM（ROOMS）房间，[ROOMS_Type]
FROM [CHECKINS]
WHERE （SALIDA> N ' 2016-10-03'） AND （ESTATUS = N ' IN'）
GROUP BY [ROOMS_Type]）

SELECT t2 .ROOMS - t1.ROOMS ' ROOMS'，t1。[ROOMS_Type] FROM
cteCheckins t2 JOIN cteReservation t1
ON t2。[ROOMS_Type] = t1。[ROOMS_Type]
ORDER BY t1。[ROOMS_Type] DESC

输出：

 ROOMS ROOMS_Type 
 10 JRS 
 4 HPT 

I have two querys that i need to substract, i the first query i get the results like this :

ROOMS | ROOM_Type
    3 | JRS
    7 | HPT

And the Second one returns

ROOMS | ROOM_Type
    13 | JRS
    11 | HPT

i need to get 1 result in wich both querys substract each other making a total count like this:

ROOMS | ROOM_Type
   10 | JRS
    4 | HPT

What I have tried:

I have tryed EXCEPT method and (Select(...) - Select (...) with no results i dont know how to make them substract their first result's
This are my 2 query's

Query 1

<pre>SELECT SUM(ROOMS), [ROOMS_Type] FROM   
  [RESERVATION]
WHERE  (SALIDA > N'2016-10-03') AND (ESTATUS = N'RV') AND (LLEGADA <= N'2016-10-03')
GROUP BY [ROOMS_Type]
ORDER BY ROOMS_Type

Query 2:

<pre>SELECT SUM(ROOMS) , [ROOMS_Type] 
FROM     [CHECKINS]
WHERE  (SALIDA > N'2016-10-03') AND (ESTATUS = N'IN')
GROUP BY [ROOMS_Type]

解决方案

Updated to use CTE. Wrap both the queries in Common Table Expression.

This should do it. I saw the Query1 and 2 late, but the concept still the same, In your case, assume RESERVATION = #test1, CHECKINS=#test2

WITH cteReservation AS (SELECT SUM(ROOMS) ROOMS, [ROOMS_Type] FROM   
  [RESERVATION]
WHERE  (SALIDA > N'2016-10-03') AND (ESTATUS = N'RV') AND (LEGADA <= N'2016-10-03')
GROUP BY [ROOMS_Type]
--ORDER BY ROOMS_Type
), cteCheckins AS (
SELECT SUM(ROOMS) Rooms, [ROOMS_Type] 
FROM     [CHECKINS]
WHERE  (SALIDA > N'2016-10-03') AND (ESTATUS = N'IN')
GROUP BY [ROOMS_Type] )

SELECT t2.ROOMS - t1.ROOMS 'ROOMS', t1.[ROOMS_Type]   FROM 
cteCheckins t2 JOIN cteReservation t1
ON t2.[ROOMS_Type] = t1.[ROOMS_Type]
ORDER BY t1.[ROOMS_Type] DESC

Output:

ROOMS	ROOMS_Type
  10	JRS
   4	HPT

这篇关于从两个查询中减去结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！