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

本文介绍了如何将两个sql语句的结果连接到一个表和不同的列中的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

I have three select queries which return total records , succesful records and failure records from same table based on different where clauses . I want to join the result of all these statements into one table in order to make my stored procedure but the resulting table shall have three different columns for 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' )

Union 在一列中给出结果，因此它不起作用.任何其他想法

Union gives out the result in one column so it won't work . any other ideas

推荐答案

只需将每个 select 语句用括号括起来，给每个 select 语句一个别名，并在顶部使用 SELECT :

Just wrap each select statement in parentheses, give each select statement an alias, and use SELECT at the top:

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

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

Basically you're treating each query as an individual column.

这篇关于如何将两个sql语句的结果连接到一个表和不同的列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！