如何将两个sql语句的结果联接到一个表和不同的列中

小编典典

如何将两个sql语句的结果联接到一个表和不同的列中

sql

我有三个选择查询，它们基于不同的where子句从同一张表中返回总记录，成功记录和失败记录。我想将所有这些语句的结果合并到一个表中以创建存储过程，但结果表应具有cdr，success，failure的三个不同的列

SELECT Count(*) AS cdr 
FROM   ABC AS c WITH (NOLOCK) 
WHERE  APPID IN( 1, 2 ) 
       AND CALLDATE = '2012-10-09'

SELECT Count(*) AS success 
FROM   ABC AS d WITH (NOLOCK) 
WHERE  APPID IN( 44, 45 ) 
       AND CALLDATE = '2012-10-09' 
       AND HANGUPCODE IN ( 'man', 'mach' )

SELECT Count(*) AS fail 
FROM   ABC WITH (NOLOCK) 
WHERE  APPID IN( 44, 45 ) 
       AND CALLDATE = '2012-10-09' 
       AND HANGUPCODE NOT IN ( 'man', 'mach' )

联合会在一列中给出结果，因此它无效。任何其他想法

阅读 208

2021-05-16

共1个答案

小编典典

只需将每个选择语句括在括号中，为每个选择语句指定一个别名，然后SELECT在顶部使用：

SELECT 
  (select count(*) as cdr  
   from abc as c with (nolock) 
   where appid in(1,2)  and calldate = '2012-10-09'
  ) AS Column1,  
  (select count(*) as success  
   from abc as d with (nolock) 
   where appid in(44,45) and calldate = '2012-10-09' 
       and hangupcode in ('man', 'mach')
  ) AS Column2, 
  (select count(*) as fail  
   from abc  with (nolock) 
   where appid in(44,45) and calldate = '2012-10-09' 
       and hangupcode not in  ('man', 'mach')
  ) AS Column3

基本上，您将每个查询视为一个单独的列。

2021-05-16