如何合并两个案例语句列

Question

我创建了 2 个案例语句来从单个表中提取数据(它被创建为 2 个单独的案例语句，因为我想重复计算值，并且作为单个案例语句，它没有这样做(。

2 个案例陈述产生了 2 个新数据列(案例陈述 A 和案例陈述 B(。 但是，我希望在一列中报告2个案例语句值，因此我正在尝试找到一种方法将这2列合并在一起。

这是我的查询。 我对SQL很陌生(刚刚一个多星期！(，所以请原谅我的查询中的任何愚蠢语法。

select b.part_month, count(a.pay_code), sum(a.amount),
CASE
when a.pay_code like 'CC%' THEN 'Credit Card Payment'
when a.pay_code like 'DC%' then 'Debit Card Payment'
Else 'N/A'
End as Payment_Type,
CASE
when a.pay_code like '%IP%' then 'Internet Payment'
when a.pay_code like '%TT%' then 'Touchtone Payment'
Else 'N/A'
End as Payment_Type
from table_a A
inner join table_b B on A.form_ID = B.ID
where a.trans_type > 0
and b.st_id = 2
group by B.PART_MONTH,
CASE
when a.pay_code like 'CC%' THEN 'Credit Card Payment'
when a.pay_code like 'DC%' then 'Debit Card Payment'
Else 'N/A'
End,
CASE
when a.pay_code like '%IP%' then 'Internet Payment'
when a.pay_code like '%TT%' then 'Touchtone Payment'
Else 'N/A'
End
order by b.part_month asc;

Answer 1

为您提供CASE语句别名，然后包装查询以GROUP BY别名。

还合并了您的CASE语句，假设这是您想要的。

SELECT * FROM (
SELECT b.part_month, 
COUNT(a.pay_code) AS PayCodeCount,
SUM(a.amount) AS AmountSum,
CASE a.pay_code 
WHEN 'CC%' THEN 'Credit Card Payment'
WHEN 'DC%' THEN 'Debit Card Payment'
WHEN '%IP%' THEN 'Internet Payment'
WHEN '%TT%' THEN 'Touchtone Payment'
ELSE 'N/A'
END AS Payment_Type
FROM table_a A
INNER JOIN table_b B ON A.form_ID = B.ID
WHERE a.trans_type > 0
AND b.st_id = 2) Z
GROUP BY PART_MONTH, Payment_Type
ORDER BY part_month ASC;

Answer 2

我猜你想要：

select b.part_month, count(a.pay_code), sum(a.amount),
(case when a.pay_code like 'CC%' then 'Credit Card Payment'
when a.pay_code like 'DC%' then 'Debit Card Payment'
when a.pay_code like '%IP%' then 'Internet Payment'
when a.pay_code like '%TT%' then 'Touchtone Payment'
else 'N/A'
end) as Payment_Type
from table_a a inner join
table_b b
on a.form_ID = b.ID
where a.trans_type > 0 and b.st_id = 2
group by b.part_month,
(case when a.pay_code like 'CC%' then 'Credit Card Payment'
when a.pay_code like 'DC%' then 'Debit Card Payment'
when a.pay_code like '%IP%' then 'Internet Payment'
when a.pay_code like '%TT%' then 'Touchtone Payment'
else 'N/A'
end)
order by b.part_month asc;

重要的是：某些数据库将允许您在group by中使用别名，接受group by b.part_month, payment_type。

Answer 3

谢谢你的帮助。 戈登·林诺夫，我已经尝试过您的查询，但这让我得到了我最初在将 case 语句拆分为 2 之前开始的结果。

pay_code %IP% 和 %TT% 也可以匹配 CC% 和 DC% 语句，当匹配时，它不计算 IP 或 TT 值。 但是，我需要计算与 %IP%， %TT%， CC% 和 DC% 匹配的所有值，即使这意味着重复计算某些数据行。

因此，pay_code列中的值可能是"DCIP"、"CCTT"、"CCIP"等。 从这些值中，我想知道有 1 个 DC 付款、2 个 CC 付款、2 个 IP 付款和 1 个 TT 付款。

这就是我的表格最终使用单个案例陈述的方式。 请注意，没有互联网支付或按键支付条目。

Credit Card Payment
Debit Card Payment
N/A
Credit Card Payment
Debit Card Payment
N/A
Credit Card Payment
Debit Card Payment
N/A
Credit Card Payment
Debit Card Payment
N/A
Credit Card Payment
Debit Card Payment
N/A
Credit Card Payment
Debit Card Payment
N/A
Credit Card Payment
Debit Card Payment
N/A
Credit Card Payment
Debit Card Payment
N/A
Credit Card Payment
Debit Card Payment
N/A
Credit Card Payment
Debit Card Payment
N/A
Credit Card Payment
Debit Card Payment
N/A
Credit Card Payment
Debit Card Payment
N/A
Credit Card Payment
Debit Card Payment
N/A
Credit Card Payment
Debit Card Payment
N/A
Credit Card Payment
Debit Card Payment
N/A
Credit Card Payment
Debit Card Payment
N/A
Credit Card Payment
Debit Card Payment

Answer 4

答案是

Select * from (select b.part_month, count(a.pay_code), sum(a.amount),
CASE
when a.pay_code like '%IP%' then 'Internet Payment'
when a.pay_code like '%TT%' then 'Touchtone Payment'
Else 'N/A'
End as Payment_Type
from table_a A
inner join table_b B on A.form_ID = B.ID
where a.trans_type > 0
and b.st_id = 2
group by B.PART_MONTH,
CASE
when a.pay_code like '%IP%' then 'Internet Payment'
when a.pay_code like '%TT%' then 'Touchtone Payment'
Else 'N/A'
End
Union
select b.part_month, count(a.pay_code), sum(a.amount),
CASE
when a.pay_code like 'CC%' THEN 'Credit Card Payment'
when a.pay_code like 'DC%' then 'Debit Card Payment'
Else 'N/A'
End as Payment_Type
from table_a A
inner join table_b B on A.form_ID = B.ID
where a.trans_type > 0
and b.st_id = 2
group by B.PART_MONTH,
CASE
when a.pay_code like 'CC%' THEN 'Credit Card Payment'
when a.pay_code like 'DC%' then 'Debit Card Payment'
Else 'N/A'
End)
Order by 1,4;