sql - 多条记录时如何返回特定文本

Question

我是 SQL 的新手，我会努力使术语正确。我有 2 个表，包含一般交易详细信息的 tbl_Trans 和包含交易详细信息的 tbl_TransData。

我想显示每个 Trans 记录和 tbl_TransData 中保存的 trans 的类别。如果每个 Trans 有多个类别，我希望文本为“多个”，否则返回类别字段

TRANS 表内容

int_Trans_ID    dtm_TransDate   txt_Type    txt_Description txt_Bank dbl_Amount
1               17/12/2018      REC         Sales           Current 1000
2               20/12/2018      PAY         Expenses paid   Current -155
3               21/12/2018      PAY         MW Repairs      Current -250

TRANSDATA 表内容

int_TransData_ID    int_TransID txt_Category    dbl_Amount
1                   1           Sales A         -600
2                   1           Sales B         -400
3                   2           Travel          100
4                   2           Meal            55
5                   3           MW Repairs      250

这是到目前为止的代码，但如果我用 ELSE txt_Category 替换 ELSE 'Single'，它就不起作用。

SELECT
int_Trans_ID,
dtm_TransDate AS Date,
txt_Type AS Type,
txt_Description AS Description,
    (SELECT 
            CASE
                WHEN count(int_TransID) > 1
                THEN 'Multiple'
                ELSE 'Single'
            END
    FROM
        dbo.tbl_TransData TD
    WHERE
        TD.int_TransID = T.int_Trans_ID) AS Category
FROM
   tbl_Trans T
GROUP BY
   int_Trans_ID, dtm_TransDate, txt_Type, txt_Description

这是我希望看到的。

int_Trans_ID    Date        Type    Description   Category
1               2018-12-17  REC     Sales         Multiple
2               2018-12-20  PAY     Expenses paid Multiple
3               2018-12-21  PAY     Car Repairs   MW Repairs

抱歉表格的格式。

非常感谢任何帮助，甚至为我指明其他帖子的方向。

Answer 1

我会这样做:

with
x as (
  select ins_trans_id, count(*) as cnt, max(txt_category) as cat 
  from transdata
  group by ins_trans_id
)
select
  t.int_trans_id,
  t.dtm_transdate,
  t.txt_type,
  t.txt_description,
  case when x.cnt = 0 then 'no category'
       when x.cnt = 1 then x.cat
       else 'Multiple' end as Category
from trans t
left join x on x.int_trans_id = t.int_trans_id