sql - SQL - 比较多行值

Question

有3张桌子。1.对象 2.位置 3.属性。一个对象将包含多个位置，每个位置将具有多个属性。

+--------+------------+   +--------+---------+------------+  +---------+-----------+-----------+-------+
|OBJ_ID  | OBJ_NAME   |   |LOC_ID  | LOC_NAME| OBJ_ID     |  | PROP_ID | PROP_NAME | PROP_VALUE| LOC_ID|
+--------|------------+   +--------|---------+------------+  +---------+-----------+-----------+-------+
| 1      |  BF        |   | 1      |  IND    |   1        |  |   1     | Number    |   6789    |   1   |
| 2      |  GF        |   | 2      |  UAE    |   1        |  |   2     | Name      |   JOHN    |   1   |
+--------+------------+   | 3      |  SLK    |   1        |  |   3     | Date      |   23-09-20|   1   |
                          | 4      |  IND    |   2        |  |   4     | Number    |   6789    |   2   |
                          | 5      |  UAE    |   2        |  |   5     | Name      |   JOHN    |   2   |
                          +--------+---------+------------+  |   6     | Date      |   23-09-20|   2   |
                                                             |   7     | Number    |   1234    |   3   |
                                                             |   8     | Name      |   JOHN    |   3   |
                                                             |   9     | Date      |   23-09-20|   3   |
                                                             |   10    | Number    |   2345    |   4   |
                                                             |   11    | Name      |   JOHN    |   4   |
                                                             |   12    | Date      |   23-09-20|   4   |
                                                             |   12    | Number    |   5678    |   5   |
                                                             |   13    | Name      |   JOHN    |   5   |
                                                             |   14    | Date      |   24-09-20|   5   |
                                                             +---------+-----------+-----------+-------+

要求是，如果我们将特定的 PROP_NAME 传递给查询，那么如果与该对象关联的任何位置中的相应属性都不同，并且其他属性相同，那么它应该打印该对象信息。

示例：如果我将 PROP_NAME = "Number" 传递给查询，那么它应该返回 OBJ_ID=1 的行，因为 OBJ_ID = 1 有 3 个位置（LOC_ID = 1,2,3 属于 OBJ_ID = 1 ）并且每个 LOC_ID 有 3特性。在这些属性中，只有“数字”不同，其余所有属性都相同。所以它应该返回 OBJ_ID = 1 详细信息。

注意：如果您查看 OBJ_ID = 2 它有两个位置（ Loc_ID=4,5 ）用于这些位置属性以及 Number 属性 Date 属性值也不同，因此它不应该是输出的一部分。要求是当且仅当传递的 PROP_NAME 值不同并且其余所有其他属性值相同时才返回该对象。

输入 PROP_NAME = "Number" 的预期 O/P

+--------+------------+---------+-----------+------------+
|OBJ_ID  | OBJ_NAME   |LOC_NAME | PROP_NAME | PROP_VALUE |
+--------|------------|---------|-----------|------------+
| 1      |  BF        |  IND    |   Number  |  6789      |
| 1      |  BF        |  UAE    |   Number  |  6789      |
| 1      |  BF        |  SLK    |   Number  |  1234      |
+--------+------------+---------+-----------+------------+

我的查询尝试使用自联接，但这没有考虑剩余属性是否相同

SELECT
  objects.OBJ_ID,
  loc_tab1.LOC_NAME,
  prop_tab1.PROP_NAME,
  prop_tab1.PROP_VALUE
FROM
  locations loc_tab1,
  locations loc_tab2,
  properties prop_tab1,
  properties prop_tab2,
  objects
WHERE
  objects.OBJ_ID= loc_tab1.OBJ_ID
  AND loc_tab1.OBJ_ID= loc_tab2.OBJ_ID
  AND loc_tab1.LOC_ID = prop_tab1.LOC_ID
  AND loc_tab2.LOC_ID = prop_tab2.LOC_ID
  AND prop_tab1.LOC_ID <> prop_tab2.LOC_ID
  AND prop_tab1.PROP_NAME = 'Number' --Here we can give Name or Date
  AND prop_tab2.PROP_NAME = 'Number' 
  AND prop_tab1.PROP_VALUE<> prop_tab2.PROP_VALUE;

标签： sqloracle

Answer 1

任务描述看起来很奇怪，但是好的，检查一下：

with v_join as (
   select
      p.*
     ,l.loc_name
     ,l.obj_id
     ,o.obj_name
   from properties p
        join locations l
             on l.loc_id = p.loc_id
        join objects o
             on o.obj_id = l.obj_id
)
select *
from v_join j1
where 
prop_name = 'Number'
and not exists(
           select 1
           from v_join j2
           where j1.obj_id   = j2.obj_id
             and j1.prop_name != j2.prop_name
           group by prop_name
           having count(distinct prop_value)>1
        )
order by 1,2,3,4,5;

带有样本数据和结果的完整测试用例： https ://dbfiddle.uk/?rdbms=oracle_18&fiddle=ec9064fb67cdb08040836e0a65530cc8

with
 objects(OBJ_ID, OBJ_NAME) as (
   select 1, 'BF' from dual union all
   select 2, 'GF' from dual
 )
,locations(LOC_ID, LOC_NAME, OBJ_ID) as (
   select 1, 'IND', 1 from dual union all
   select 2, 'UAE', 1 from dual union all
   select 3, 'SLK', 1 from dual union all
   select 4, 'IND', 2 from dual union all
   select 5, 'UAE', 2 from dual
)
,properties(PROP_ID , PROP_NAME , PROP_VALUE, LOC_ID) as(
   select 1 , 'Number', '6789    ', 1 from dual union all
   select 2 , 'Name  ', 'JOHN    ', 1 from dual union all
   select 3 , 'Date  ', '23-09-20', 1 from dual union all
   select 4 , 'Number', '6789    ', 2 from dual union all
   select 5 , 'Name  ', 'JOHN    ', 2 from dual union all
   select 6 , 'Date  ', '23-09-20', 2 from dual union all
   select 7 , 'Number', '1234    ', 3 from dual union all
   select 8 , 'Name  ', 'JOHN    ', 3 from dual union all
   select 9 , 'Date  ', '23-09-20', 3 from dual union all
   select 10, 'Number', '2345    ', 4 from dual union all
   select 11, 'Name  ', 'JOHN    ', 4 from dual union all
   select 12, 'Date  ', '23-09-20', 4 from dual union all
   select 12, 'Number', '5678    ', 5 from dual union all
   select 13, 'Name  ', 'JOHN    ', 5 from dual union all
   select 14, 'Date  ', '24-09-20', 5 from dual
)
,v_join as (
   select
      p.*
     ,l.loc_name
     ,l.obj_id
     ,o.obj_name
   from properties p
        join locations l
             on l.loc_id = p.loc_id
        join objects o
             on o.obj_id = l.obj_id
)
select *
from v_join j1
where 
prop_name = 'Number'
and not exists(
           select 1
           from v_join j2
           where j1.obj_id   = j2.obj_id
             and j1.prop_name != j2.prop_name
           group by prop_name
           having count(distinct prop_value)>1
        )
order by 1,2,3,4,5;

结果：

   PROP_ID PROP_NAME PROP_VALUE      LOC_ID LOC     OBJ_ID OBJ_NAME
---------- --------- ----------- ---------- --- ---------- ---------
         1 Number    6789                 1 IND          1 BF
         4 Number    6789                 2 UAE          1 BF
         7 Number    1234                 3 SLK          1 BF

3 rows selected.

更新： 大数据集的优化版本

with v_join as (
   select
      p.*
     ,l.loc_name
     ,l.obj_id
     ,o.obj_name
   from properties p
        join locations l
             on l.loc_id = p.loc_id
        join objects o
             on o.obj_id = l.obj_id
)
select *
from (
   select 
       j2.*
      ,max(decode(prop_name,'Number',0,cnt_prop_values))over(partition by obj_id) max_cnt_not_number
   from (
      select
         j1.*
        ,count(distinct loc_id) over(partition by obj_id,prop_name) cnt_locations
        ,count(distinct prop_value) over(partition by obj_id,prop_name) cnt_prop_values
      from v_join j1
   ) j2
) j3 
where prop_name='Number'
  and max_cnt_not_number=1
  and cnt_prop_values > 1

带有样本数据的全面优化测试用例：

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=b9535c6b56b3a80baed8c8b641ff3c91

with
 objects(OBJ_ID, OBJ_NAME) as (
   select 1, 'BF' from dual union all
   select 2, 'GF' from dual
 )
,locations(LOC_ID, LOC_NAME, OBJ_ID) as (
   select 1, 'IND', 1 from dual union all
   select 2, 'UAE', 1 from dual union all
   select 3, 'SLK', 1 from dual union all
   select 4, 'IND', 2 from dual union all
   select 5, 'UAE', 2 from dual
)
,properties(PROP_ID , PROP_NAME , PROP_VALUE, LOC_ID) as(
   select 1 , 'Number', '6789    ', 1 from dual union all
   select 2 , 'Name  ', 'JOHN    ', 1 from dual union all
   select 3 , 'Date  ', '23-09-20', 1 from dual union all
   select 4 , 'Number', '6789    ', 2 from dual union all
   select 5 , 'Name  ', 'JOHN    ', 2 from dual union all
   select 6 , 'Date  ', '23-09-20', 2 from dual union all
   select 7 , 'Number', '6789    ', 3 from dual union all
   select 8 , 'Name  ', 'JOHN    ', 3 from dual union all
   select 9 , 'Date  ', '23-09-20', 3 from dual union all
   select 10, 'Number', '1234    ', 4 from dual union all
   select 11, 'Name  ', 'JOHN    ', 4 from dual union all
   select 12, 'Date  ', '23-09-20', 4 from dual union all
   select 12, 'Number', '5678    ', 5 from dual union all
   select 13, 'Name  ', 'JOHN    ', 5 from dual union all
   select 14, 'Date  ', '23-09-20', 5 from dual
)
,v_join as (
   select
      p.*
     ,l.loc_name
     ,l.obj_id
     ,o.obj_name
   from properties p
        join locations l
             on l.loc_id = p.loc_id
        join objects o
             on o.obj_id = l.obj_id
)
select *
from (
   select 
       j2.*
      ,max(decode(prop_name,'Number',0,cnt_prop_values))over(partition by obj_id) max_cnt_not_number
   from (
      select
         j1.*
        ,count(distinct loc_id) over(partition by obj_id,prop_name) cnt_locations
        ,count(distinct prop_value) over(partition by obj_id,prop_name) cnt_prop_values
      from v_join j1
   ) j2
) j3 
where prop_name='Number'
  and max_cnt_not_number=1
  and cnt_prop_values > 1;