根据条件和其他列的值差异添加新列
根据条件和其他列的值差异添加新列
提问于 2022-07-21 13:44:46
我想我可能忽略了一些显而易见的东西。我不是要别人为我写完整的代码，我只需要一个提示或者一个指向类似案例的链接。提前谢谢..。
我的问题是：
select Client , ProductID, M_POS_TYPE AS Keep_or_Keep_in_Transit, Amount
FROM inventory_table inv_table
JOIN inventory_position inv_pos
ON inv_pos.ProductID=inv_table.ProductID
group by Client, ProductID, M_POS_TYPE, Amount输出：
我如何添加一个新列来检查列中值的减法是否与相同的ProductID和Client的值0不同？
第5行和第6行是一个完美的例子。所需：
5-4<>0然后，输出。
增加一个新列：
我曾尝试使用is条件、CASE语句，但如何确保它会计算相同客户端和ProductID的差异？
我正在寻找通用情况下的解决方案，表中有数千种不同的ProductsID和客户端值。我有点困在这个问题上了。
回答 1
Database Administration用户
回答已采纳
发布于 2022-07-23 07:18:35
这里有一个选择。
由于您没有发布测试用例，所以我使用查询的结果作为源(即示例数据)(即test CTE)：
SQL> with test (client, prodid, kkit, amount) as
2 (select 'c1', 'it1234', 'Keep' , 100 from dual union all
3 select 'c1', 'fr1234', 'Keep_Transit', 56 from dual union all
4 select 'c2', 'it1234', 'Keep' , 30 from dual union all
5 select 'c2', 'br1234', 'Keep_Transit', 0 from dual union all
6 select 'c3', 'no1234', 'Keep' , 5 from dual union all
7 select 'c3', 'no1234', 'Keep_Transit', 4 from dual union all
8 select 'c4', 'no1234', 'Keep' , 0 from dual union all
9 select 'c4', 'us1234', 'Keep_Transit', 2 from dual
10 ),diff CTE和amount (“保持”被认为是正，"Keep_Transit“负)每个客户端和产品ID：
11 diff as
12 (select client, prodid,
13 sum(case when kkit = 'Keep' then amount
14 when kkit = 'Keep_Transit' then -amount
15 end) as diff
16 from test
17 group by client, prodid
18 having min(kkit) = 'Keep'
19 and max(kkit) = 'Keep_Transit'
20 )最后，将源数据外部连接到diff，如果存在差异，则使用case表达式显示Y：
21 select t.client, t.prodid, t.kkit, t.amount,
22 case when d.diff is not null then 'Y' end diff
23 from test t left join diff d on d.client = t.client
24 and d.prodid = t.prodid
25 order by t.client, t.prodid;
CLIENT PRODID KKIT AMOUNT DIFF
---------- ------ ------------ ---------- -----
c1 fr1234 Keep_Transit 56
c1 it1234 Keep 100
c2 br1234 Keep_Transit 0
c2 it1234 Keep 30
c3 no1234 Keep 5 Y
c3 no1234 Keep_Transit 4 Y
c4 no1234 Keep 0
c4 us1234 Keep_Transit 2
8 rows selected.
SQL>页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://dba.stackexchange.com/questions/314687
复制相关文章
相似问题
腾讯云开发者
