时间戳

首页 > 解决方案 > Oracle - 如何在垂直方向的表中连接多行

sql - Oracle - 如何在垂直方向的表中连接多行

问题描述

假设我有两张这样的表：

table1:
-----------------
| someId | value|
|--------|------|
| 1      | 2    |
| 2      | 3    |
| 3      | 4    |
-----------------

table2:
-----------------------------------
| someId | type | value1 | value2 |
|--------|------|--------|--------|
| 1      | 2    | hello  |        |
| 1      | 3    |        | 2      |
| 1      | 4    |        |        |
| 2      | 4    |        |        |
-----------------------------------

table1.someId = table2.someId

table2是垂直的，因此该表的多行（基于someId）参考table1.someId.

现在我需要获取AND ( and ) AND ( and ) 加入的行数。table1table1.value=?table2.type=2table2.value1=?table2.type=3table2.value2=?table1.someId = table2.someId

这是我现在的查询（它是参数化的和参数value，value1并且value2是从客户端传递的）：

select count(case when t1.value = ? then 1 end) from table1 t1
  inner join
  (select value1.someId from
    (select someId from table2 where type = 2 and value1 = ?) value1
    inner join
    (select someId from table2 where type = 3 and value2 = ?) value2
    on value1.someId = value2.someId
  ) t2
  on t1.someId = t2.someId;

示例查询：

select count(case when t1.value = 2 then 1 end) from table1 t1
  inner join
  (select value1.someId from
    (select someId from table2 where type = 2 and value1 ='hello') value1
    inner join
    (select someId from table2 where type = 3 and value2 = 2) value2
    on value1.someId = value2.someId
  ) t2
  on t1.someId = t2.someId;

有没有其他方法可以实现这一点，而不是多个selects 由inner joins 连接？（实际上，我必须从 中搜索三个types）table2。

运行结果正确的示例（来自 Michael Buen 的更新示例）： db-fiddle.com

谢谢你。

标签： sqloraclejoincount

解决方案

您需要的是在加入 table1 之前为 table2 编写一个自定义数据透视表，由 someid 摸索：

with s (someId, type, value1, value2) as (
select 1, 2, 'hello', to_number(null) from dual union all
select 1, 3, null   , 2 from dual union all
select 1, 4, null   , null from dual union all
select 2, 4, null   , null from dual)
select someid,
max(case when type = 2 then value1 end) type2_value1,
max(case when type = 3 then value2 end) type3_value2/*,
max(case when type = 4 then value1 end) type4_value1
max(case when type = 4 then value2 end) type4_value2*/
from s
group by someid;

    SOMEID TYPE2 TYPE3_VALUE2
---------- ----- ------------
         1 hello            2
         2

推荐阅读