时间戳

首页 > 解决方案 > 在 SQL Server 2008 中使用子查询时如何在单行中获取结果？

sql-server-2008 - 在 SQL Server 2008 中使用子查询时如何在单行中获取结果？

问题描述

我正在尝试获取一个包含 stage_name 及其在相应贷款产品中的计数的表。就像下面的例子 stage_name 是 RCO 并且有三个贷款产品，汽车贷款，消费贷款和信用卡。虽然我使用了逻辑并获得了正确的输出，但在输出中，我为每个 stage_name 和贷款产品案例获得了单独的行。我只想要一行所有三个结果。请看下面我的代码，实际输出和期望输出：

SELECT 
    'RCO',
    CASE 
       WHEN sq2.loan_type = 'Consumer loan' 
          THEN SUM(ISNULL(sq2.user_count, 0)) 
    END AS Consumer_Loan,
    CASE 
       WHEN sq2.loan_type = 'Auto Loan' 
          THEN SUM(ISNULL(sq2.user_count, 0))  
    END AS Auto_Loan,
    CASE 
       WHEN sq2.loan_type = 'Credit Card' 
          THEN SUM(ISNULL(sq2.user_count, 0)) 
    END AS Credit_Card
FROM
    (SELECT 
         'RC0' AS ws_name, 'Consumer loan' AS loan_type,
         COUNT(DISTINCT a.bpm_referenceno) AS user_count, 
         a.takenby AS user_id
     FROM
         BM_RLOS_DecisionHistoryForm a
     INNER JOIN
         (SELECT 
              m.bpm_referenceno
          FROM 
              BM_RLOS_EXTTABLE m
          WHERE 
              m.loan_type = 'Consumer Loan') sq1 ON a.bpm_referenceno = sq1.bpm_referenceno
    WHERE 
        a.winame='RCO'
    GROUP BY 
        a.takenby

UNION
SELECT 'RC0','Auto loan',
count (DISTINCT a.bpm_referenceno), a.takenby
from
BM_RLOS_DecisionHistoryForm a
INNER JOIN
(SELECT 
m.bpm_referenceno
FROM BM_RLOS_EXTTABLE m
WHERE m.loan_type='Auto Loan')sq1
ON a.bpm_referenceno = sq1.bpm_referenceno
WHERE a.winame='RCO'
GROUP BY a.takenby

UNION
SELECT 'RC0','Credit Card',
count (DISTINCT a.bpm_referenceno), a.takenby
from
BM_RLOS_DecisionHistoryForm a
INNER JOIN
(SELECT 
m.bpm_referenceno
FROM BM_RLOS_EXTTABLE m
WHERE m.loan_type='Credit Card')sq1
ON a.bpm_referenceno = sq1.bpm_referenceno
WHERE a.winame='RCO'
GROUP BY a.takenby) sq2
GROUP BY sq2.ws_name,sq2.loan_type

实际输出：

    |--------------|-------------|-------------|-------------|
    |   Stg_nm     |   Cons_ln   |   Auto_lan  | Credit_card |
    |--------------|-------------|-------------|-------------|
    |    RCO       |     NULL    |     NULL    |     8       |
    |--------------|-------------|-------------|-------------|
    |    RCO       |     NULL    |     55      |     NULL    |
    |--------------|-------------|-------------|-------------|
    |    RCO       |      81     |     NULL    |     NULL    |
    |--------------|-------------|-------------|-------------|

所需输出

|--------------|-------------|-------------|-------------|
|      Stg_nm  |     Cons_ln |   Auto_lan  | Credit_card |
|--------------|-------------|-------------|-------------|
|    RCO       |     81      |     55      |     8       |
|--------------|-------------|-------------|-------------|

标签： sql-server-2008join

解决方案

上半部分应该是这样 - 颠倒 and 的用法，SUM并完全CASE删除最后一个GROUP BY

SELECT 
    'RCO',
    SUM(CASE 
          WHEN sq2.loan_type = 'Consumer loan' 
          THEN sq2.user_count 
          ELSE 0 END
        ) 
    AS Consumer_Loan,
    SUM(CASE 
          WHEN sq2.loan_type = 'Auto loan' 
          THEN sq2.user_count 
          ELSE 0 END
        ) 
    AS Auto_Loan,
    SUM(CASE 
          WHEN sq2.loan_type = 'Credit Card' 
          THEN sq2.user_count 
          ELSE 0 END
        ) 
    AS Credit_Card
FROM
<your existing query, with the final GROUP BY removed>

但是你需要GROUP BY从最后完全删除

推荐阅读