合并结果Cosmosdb中的两个查询

Question

SELECT 
count(c.Transaction_Id) as Legacy FROM c where c.OverallStatus = 'COMPLETED' and c.KeyData2 like 'WLS_%'
and contains (c.Created_Date, '2021-08-11') and c.Transaction_Set_Code = '210'

SELECT 
count(c.Transaction_Id) as NonLegacy FROM c where c.OverallStatus = 'COMPLETED' and c.KeyData2 not like 'WLS_%'
and contains (c.Created_Date, '2021-08-11') and c.Transaction_Set_Code = '210'

必答:

[
{
"Legacy": 18753,
"NonLegacy": 121862
}
]

Answer 1

Select t.cnt , t.keyd from (
SELECT count(1) as cnt, c.keydata2 as keyd 
FROM c 
where c.OverallStatus = 'COMPLETED' 
and contains (c.Created_Date, '2021-08-11')
and c.Transaction_Set_Code = '210'
group by c.KeyData2)
t
where t.keyd like 'wls_%' or t.keyd not like 'wls_%'

Answer 2

我们将讨论以下解决方案

SELECT 
COUNT(c.KeyData2 like 'WLS_%' ? 1 : undefined) AS NonLegacy, 
COUNT(c.KeyData2 not like 'WLS_%' ? 1 : undefined) AS Legacy
from c where contains (c.Created_Date, '2022-01-12') and c.OverallStatus = 'COMPLETED' and c.Transaction_Set_Code = '210'

"Legacy": 402,
"NonLegacy": 320