从sql中的值1减去值2 [英] Subtract value 2 from value 1 in sql

问题描述

查询编号1

select sum(income) as pairs from Table2 where user_id='100001'

查询编号2

Query No. 2

with cte as
(
  select userid, parentid from  Table1 where userid = '100001'
  union all select t.userid, t.parentid
  from  Table1 t inner join cte on cte.userid = t.parentid
 )
   select count(*) from cte 

我想要的是以这种方式将这两个查询作为单个查询加入：

减去查询2从中检索的值query1检索值..



即query1_value - query2_value



i我尝试如下。但是它给出了错误..

what i want is that join these two queries as a single query in such a way :
subtract query2 retrieved value from query1 retrieved value..

i.e query1_value - query2_value

i am trying like below. but it is giving error..

with cte as
(
  select count(*) as pair_count,userid, parentid from  user_detail where userid = '100002'
  union all select COUNT(*) as pair_count,t.userid, t.parentid
  from  user_detail t inner join cte on cte.userid = t.parentid
 )
  
   SELECT (B.AMOUNT - A.pair_count) AS DUE FROM cte AS A
   CROSS APPLY (SELECT SUM(Pairs) AS AMOUNT FROM payout WHERE user_id = '100002') AS B
   WHERE A.userid = '100002'

错误是：

Error is :

Msg 8120, Level 16, State 1, Line 3
Column 'user_detail.userid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

推荐答案

提供的答案在重新发布的问题从sql中的值1减去值2 [ ^ ]

Answer supplied at the reposted question Subtract value 2 from value 1 in sql[^]

这篇关于从sql中的值1减去值2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！