按复杂查询分组
按复杂查询分组
提问于 2018-08-16 20:44:29
STATUS DATE MARKET COUNTRY ACC INCT VOL TOTAL
INC 7/2/2018 CHICAGO US 123456 AB 3 0.06
DEL 7/3/2018 CHICAGO US 123456 AB 3 0.06
INC 7/3/2018 CHICAGO US 67890 AB 3 0.06
INC 7/6/2018 CHICAGO US 700000 CBO 25 0.5
INC 7/11/2018 EUROPE WG 253235 EFDX 1 0.02
INC 7/18/2018 NEWYORK US 700000 RTY 2 0.04
INC 7/24/2018 CHICAGO US 700000 CBO 2 0.04
INC 7/25/2018 EUROPE WG 253235 EFDX 1 0.02 在(公司，del)下，如果我发现市场、国家、ACC、INCT、VOL完全相同，那么我只需要拿出第三项记录，消除前两项记录(7/3/2018年芝加哥美国67890 AB 3 0.06 )
我的预期产出是
STATUS DATE MARKET COUNTRY ACC INCT VOL TOTAL <br>
INC 7/3/2018 CHICAGO US 67890 AB 3 0.06 <br>
INC 7/6/2018 CHICAGO US 700000 CBO 25 0.5<br>
INC 7/11/2018 EUROPE WG 253235 EFDX 1 0.02<br>
INC 7/18/2018 NEWYORK US 700000 RTY 2 0.04<br>
INC 7/24/2018 CHICAGO US 700000 CBO 2 0.04<br>
INC 7/25/2018 EUROPE WG 253235 EFDX 1 0.02<br>我只需要在SQL server & Oracle中使用SQL来完成这个任务。我会感谢所有的帮助。非常感谢。
回答 3
Stack Overflow用户
回答已采纳
发布于 2018-08-16 21:06:57
使用公共表表达式(CTE)来确定哪些行有偏移的"INC“和"DEL”记录。然后，从CTE中选择并排除那些。如下所示：
WITH tmp as (
SELECT id.*,
sum(decode(status,'INC',total,'DEL',-total))
over ( partition by market, country, acc, inct, vol, total) incdel
FROM input_data id )
SELECT status, trx_date, market, country, acc, inct, vol, total
FROM tmp
WHERE incdel != 0;下面是一个完整的示例，包含测试数据：
with input_data (STATUS, TRX_DATE, MARKET, COUNTRY, ACC, INCT, VOL, TOTAL ) AS (
SELECT 'INC', TO_DATE('7/2/2018','MM/DD/YYYY'), 'CHICAGO', 'US', 123456, 'AB', 3, 0.06 FROM DUAL UNION ALL
SELECT 'DEL', TO_DATE('7/3/2018','MM/DD/YYYY'), 'CHICAGO', 'US', 123456, 'AB', 3, 0.06 FROM DUAL UNION ALL
SELECT 'INC', TO_DATE('7/3/2018','MM/DD/YYYY'), 'CHICAGO', 'US', 67890, 'AB', 3, 0.06 FROM DUAL UNION ALL
SELECT 'INC', TO_DATE('7/6/2018','MM/DD/YYYY'), 'CHICAGO', 'US', 700000, 'CBO', 25, 0.5 FROM DUAL UNION ALL
SELECT 'INC', TO_DATE('7/11/2018','MM/DD/YYYY'), 'EUROPE', 'WG', 253235, 'EFDX', 1, 0.02 FROM DUAL UNION ALL
SELECT 'INC', TO_DATE('7/18/2018','MM/DD/YYYY'), 'NEWYORK', 'US', 700000, 'RTY', 2, 0.04 FROM DUAL UNION ALL
SELECT 'INC', TO_DATE('7/24/2018','MM/DD/YYYY'), 'CHICAGO', 'US', 700000, 'CBO', 2, 0.04 FROM DUAL UNION ALL
SELECT 'INC', TO_DATE('7/25/2018','MM/DD/YYYY'), 'EUROPE', 'WG', 253235, 'EFDX', 1, 0.02 FROM DUAL ),
tmp as (
SELECT id.*,
sum(decode(status,'INC',total,'DEL',-total))
over ( partition by market, country, acc, inct, vol, total) incdel
FROM input_data id )
SELECT status, trx_date, market, country, acc, inct, vol, total
FROM tmp
WHERE incdel != 0;结果：
+--------+-----------+---------+---------+--------+------+-----+-------+
| STATUS | TRX_DATE | MARKET | COUNTRY | ACC | INCT | VOL | TOTAL |
+--------+-----------+---------+---------+--------+------+-----+-------+
| INC | 03-JUL-18 | CHICAGO | US | 67890 | AB | 3 | 0.06 |
| INC | 24-JUL-18 | CHICAGO | US | 700000 | CBO | 2 | 0.04 |
| INC | 06-JUL-18 | CHICAGO | US | 700000 | CBO | 25 | 0.5 |
| INC | 25-JUL-18 | EUROPE | WG | 253235 | EFDX | 1 | 0.02 |
| INC | 11-JUL-18 | EUROPE | WG | 253235 | EFDX | 1 | 0.02 |
| INC | 18-JUL-18 | NEWYORK | US | 700000 | RTY | 2 | 0.04 |
+--------+-----------+---------+---------+--------+------+-----+-------+Stack Overflow用户
发布于 2018-08-16 20:54:03
要做到这一点，可能有几种方法。你可以做两个CTE。张贴您的查询将有帮助！这个查询还没有完成，您必须完成它。但主要的想法就在这里。
;with subQry1 (
select STATUS DATE MARKET COUNTRY ACC INCT VOL, sum(??) total,
from table
where ...
group by STATUS DATE MARKET COUNTRY ACC INCT VOL
), subQry2 (
select STATUS DATE MARKET COUNTRY ACC INCT VOL, total,
rownumber() over(partition by MARKET, COUNTRY,ACC, INCT, VOL, TOTAL order by MARKET, COUNTRY,ACC, INCT, VOL, TOTAL) rnk
from subQry1
) select * from subQry2 where rnk = 1;Stack Overflow用户
发布于 2018-08-16 21:07:12
听起来像'DEL'“删除”一个'INC'诱骗记录吗？这表明NOT EXISTS可能会有所帮助。
如果不知道实际的查询或模式，我就不能让您知道如何处理查询：
SELECT T1.STATUS,
T1.DATE,
T1.MARKET,
...
T1.TOTAL
FROM ELBAT T1
WHERE T1.STATUS = 'INC'
AND NOT EXISTS (SELECT *
FROM ELBAT T2
WHERE T2.STATUS = 'DEL'
AND T2.MARKET = T1.MARKET
...
AND T2.TOTAL = T1.TOTAL);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51885081
复制相关文章
相似问题
腾讯云开发者
