我想过滤掉从两个不同查询中获得的结果,并获得总数。以下是我的两个问题:
Query 1
select count(*)
from table1 oow
where oow.status='completed'--134 records
Query 2
select count(*)
from table2 oow
join #temp re
on oow.order=re.order
where oow.status='completed'--77 records
期望的结果是两个值的相减,即134-77=57。我如何在sybase中实现这一点。
给定表别名oow,我将假设query1中的表1和query2中的表2实际上是同一个表。如果是这样的话,你可以这样做:
select count(*)
from
table2 oow
left join #temp re
on oow.order=re.order
where oow.status='completed'
and re.order is null
我使用了左联接和检查null,而不是执行减法。
试试这个:
select count(*)
from table1
where not exists
(select 1
from #temp re
where re.order = order and status = 'Completed')
此查询仅返回那些存在于表1中但不存在于#temp中的行,基于顺序值和筛选条件。因此,以上相当于得到总计数、过滤计数以及两者之间的差。
使用Cross Join
SELECT fst - scd
FROM (SELECT Count(*) AS fst
FROM table1 oow
WHERE oow.status = 'completed'),--134 records
(SELECT Count(*) AS scd
FROM table2 oow
JOIN #temp re
ON oow.ORDER = re.ORDER
WHERE oow.status = 'completed')
试试这个:
SELECT
(select count(*)
from table1 oow
where oow.status='completed')
-
(select count(*)
from table2 oow
join #temp re
on oow.order=re.order
where oow.status='completed')