代码之家 › 专栏 › 技术社区 › Matteo NNZ

比较具有相同键的两行并确定值是否不同

oracle sql

Matteo NNZ · 技术社区 · 8 年前

我有一张这样的桌子:

+ -------------+------------+------+-----------+-----------+
| M_PACK_INTID | M_PACK_REF | M_NB | M_XPFWSPT | M_BPFOLIO |
+ -------------+------------+------+-----------+-----------+
| 1.781        | 100        | 101  | 12        | PFOLIO    |
| 1.781        | 100        | 102  | 12        | PFOLIO    |
| 1.781        | 100        | 103  | 12        | PFOLIO    |
| 1.781        | 100        | 104  | 12.3      | PFOLIO    |
| 1.781        | 200        | 201  | 15        | PFOLIO    |
| 1.781        | 200        | 202  | 15        | PFOLIO    |
| 1.781        | 200        | 203  | 15        | PFOLIO    |
| 1.781        | 200        | 204  | 15        | PFOLIO    |
+ -------------+------------+------+-----------+-----------+

M_PACK_REF ,4个不同 M_NB ,为其分配了一个可能不同的 M_XPFWSPT .

我想把所有的 M_PACK_REF公司 为此第一 M_NB公司 有不同的价值 M_XPFWSPT公司 在上面的例子中 M_PACK_REF = 100 (自 M_NB = 101 M_XPFWSPT = 12 而 M_NB = 104 M_XPFWSPT = 12.3 ).

这是一个 SQL fiddle

M_PACK_REF
100
400

数一数

select M_PACK_REF
 from 
 (
    select M_PACK_REF, M_NB, M_XPFWSPT
    from GATHER
    where M_NB = (select min(M_NB) from GATHER G where G.M_PACK_REF = GATHER.M_PACK_REF)
    or M_NB = (select max(M_NB) from GATHER G where G.M_PACK_REF = GATHER.M_PACK_REF)
 )
 group by M_PACK_REF, M_NB, M_XPFWSPT
 having count (distinct M_XPFWSPT) > 1;

... 但它给了我一张空桌子。有人能帮忙吗?

3 回复 | 直到 8 年前

Matteo NNZ 8 年前

首先给你的行编号,所以得到第一和第四个 M_PACK_REF . 然后比较。

with numbered as
(
  select
    m_pack_ref,
    m_nb,
    m_xpfwspt,
    row_number() over (partition by m_pack_ref order by m_nb) as rn
  from gather
)
select *
from (select * from numbered where rn = 1) first
join (select * from numbered where rn = 4) fourth
  on  fourth.m_pack_ref = first.m_pack_ref
  and fourth.m_xpfwspt <> first.m_xpfwspt;

D-Shih 8 年前

如果我明白你可以用 exists having 才能成功。

select M_PACK_REF, M_NB, M_XPFWSPT
from GATHER g1
WHERE exists (
  SELECT 1 
  FROM GATHER g2
  HAVING
      min(g2.M_NB) = g1.M_NB OR MAX(g2.M_NB) = g1.M_NB 
  and 
      COUNT(distinct M_XPFWSPT) > 1
)

Kiryl Khadanovich 8 年前

WITH filtered as 
(
SELECT 
    M_PACK_REF,
    M_XPFWSPT,
    COUNT(*) as Cnt
FROM GATHER
    WHERE M_NB like '%01' or M_NB like '%04'
GROUP BY M_PACK_REF, M_XPFWSPT
HAVING COUNT(*) = 1
)

SELECT DISTINCT M_PACK_REF FROM filtered

如果M_XPFWSPT的值不同,我们将为每对M_PACK_REF,M_XPFWSPT设置一行。如果M_XPFWSPT相同-count(*)将返回2