Sql创建新行并更新关联值

Question

我正在与oracle设计不良的表工作。表结构如下:

| Id| Dtransaction |Hotel|
| --|-----         | ----|
| 1 | 100          |Hotel1|
| 2 |2000          |Hotel2|
| 3 |-100          |Hotel2|
| 4 | 500          |Hotel3|
| 5 | 1000         |Hotel5|
| 6 |500           |Hotel12.Hotel1|
| 7 |700           | Hotel5|

我必须创建一个查询，这将返回交易总额，组的酒店。但如果酒店名称中包含"然后必须创建两行，字符串的第一部分将包含负事务，第二部分将是正事务。例500 |酒店1。Hotel2|应分成两行

| Id| Dtransaction |Hotel|
| 1 | 500          |Hotel1|
| 2 | -500         |Hotel12|

创建如下查询

Select Hotel, sum(Dtransaction) from 
HotelTransaction
group by Hotel

但是我无法打破包含逗号分隔的酒店名称的酒店，并根据字符串

中的位置分配交易值我尽力解释这个问题。

Answer 1

如果你的Oracle版本支持APPLY/LATERAL，我们可以有条件地添加一行。

SELECT calc.Hotel, SUM(calc.Dtransaction)
from HotelTransaction h
CROSS APPLY (
SELECT
CASE WHEN h.Hotel LIKE '%.%' THEN
SUBSTR(h.Hotel, INSTR(h.Hotel, '.') + 1)
ELSE h.Hotel
END AS Hotel,
h.Dtransaction
FROM dual    -- just generate a single row on the fly
UNION ALL
SELECT
SUBSTR(h.Hotel, 1, INSTR(h.Hotel, '.') - 1),
-h.Dtransaction
FROM dual
WHERE h.Hotel LIKE '%.%'    --conditionally generate another row
) calc
GROUP BY calc.Hotel;

Answer 2

嗯…你想要"最后一个"'.'之后的部分名称为正，第一部分(如果有的话)为负。一种方法是:

select hotel, sum(Dtransaction)
from ((select regexp_substr(hotel, '[^.]+$') as hotel, Dtransaction
from HotelTransaction
) union all
(select regexp_substr(hotel, '^[^.]+'), -Dtransaction
from HotelTransaction
where hotel like '%.%'
)
) h
group by hotel;

Answer 3

在一次传递中，使用一个交叉连接到一个非常小的" helper ";动态创建的表，以及基本聚合:

create table hoteltransaction (id, dtransaction, hotel) as
select 1,  100, 'Hotel1'         from dual union all
select 2, 2000, 'Hotel2'         from dual union all
select 3, -100, 'Hotel2'         from dual union all
select 4,  500, 'Hotel3'         from dual union all
select 5, 1000, 'Hotel5'         from dual union all
select 6,  500, 'Hotel12.Hotel1' from dual union all
select 7,  700, 'Hotel5'         from dual
;
select case q.n when 1 then substr(h.hotel, 1, instr(h.hotel, '.') - 1)
else substr(h.hotel, instr(h.hotel, '.') + 1) end 
as hotel
, sum(case q.n when 1 then -h.dtransaction else h.dtransaction end)
as dtransaction
from   hoteltransaction h
inner join 
(select level as n from dual connect by level <= 2) q
on q.n = 2 or instr(h.hotel, '.') > 0
group  by case q.n when 1 then substr(h.hotel, 1, instr(h.hotel, '.') - 1)
else substr(h.hotel, instr(h.hotel, '.') + 1) end
;
HOTEL          DTRANSACTION
-------------- ------------
Hotel1                  600
Hotel3                  500
Hotel2                 1900
Hotel12                -500
Hotel5                 1700

不幸的是，SQL(至少是Oracle方言)不允许我们使用"hotel"这个名字。对于group by中的case表达式—我们必须在两个地方都写完全相同的东西(或者使用子查询—外部查询结构)，但是计算实际上只执行一次。查询看起来不"整洁"，但性能不受影响，因为计算只进行一次，结果在两个地方都使用。

评论你的数据模型:这是双重的糟糕，因为它使用了自由形式的酒店描述，而不是酒店id。你是否有权更改模型?为了说明这个问题——您的样本数据在"hotel5"之前有一个错误的空格。在示例输入(最后一行)中。我以为这是一个错别字，但如果现实数据中确实包含这样的错误空格呢?现在您必须编写所有的查询来处理这些坏数据—假设您甚至知道一开始就有坏数据。这对你的"老板"来说应该是一个很好的理由。让您进行所需的更改。