我对sql很陌生,这似乎暂时超出了我的技能范围。
我需要从 2 个连接的表创建以下列表:
城市 |总结果 |结果 = A 或 B |结果 = C 或 D 或 E |结果 = F城市1 | 10 | 3 | 5 | 2 城市2 | 3 | 2 | 0 | 1
表结构为:
Tbl1:id(主要)、城市、结果Tbl2:代码(主)、时刻、状态表的连接很容易:从 Tbl1 中选择 * t1 在 t1.id=t2.code 上加入 Tbl2 t2
我首先尝试嵌套选择,但我不能或不知道如何匹配 2 个条件,城市 + "结果= A 或 B",除非我指定特定城市,在这种情况下无法创建所有城市的列表。
你可以做一个有条件的总和。由于您没有提供任何示例数据,因此您应该能够在您的案例中获取和使用以下示例:
with sample_data as (select 1 id, 'a' val1, 10 val2 from dual union all
select 1 id, 'a' val1, 20 val2 from dual union all
select 1 id, 'b' val1, 30 val2 from dual union all
select 1 id, 'b' val1, 40 val2 from dual union all
select 1 id, 'c' val1, 50 val2 from dual union all
select 1 id, 'd' val1, 60 val2 from dual union all
select 1 id, 'e' val1, 70 val2 from dual union all
select 2 id, 'a' val1, 100 val2 from dual union all
select 2 id, 'b' val1, 200 val2 from dual union all
select 2 id, 'b' val1, 300 val2 from dual union all
select 2 id, 'c' val1, 400 val2 from dual union all
select 2 id, 'd' val1, 500 val2 from dual union all
select 2 id, 'd' val1, 600 val2 from dual union all
select 2 id, 'e' val1, 700 val2 from dual)
-- end of creating a subquery that mimics a table called sample_data with data in it.
select id,
count(*) total,
count(case when val1 in ('a', 'b') then 1 end) total_a_b,
count(case when val1 in ('c', 'd') then 1 end) total_c_d
from sample_data
group by id;
ID TOTAL TOTAL_A_B TOTAL_C_D
---------- ---------- ---------- ----------
1 7 4 2
2 7 3 3
您可能需要条件聚合:
SELECT CITY,
SUM(1) AS TotalResult,
SUM(
CASE WHEN RESULT IN ('A', 'B')
THEN 1
ELSE 0
END) AS "Result= A or B",
SUM(
CASE
WHEN RESULT IN ('C', 'D', 'E')
THEN 1
ELSE 0
END
)
AS "Result= C or D or E",
SUM(
CASE
WHEN RESULT = 'F'
THEN 1
ELSE 0
END
) AS "Result= F"
FROM Tbl1 t1 JOIN Tbl2 t2 ON t1.id = t2.code
GROUP BY CITY
这在GROUP BY
子句中使用CITY
,因此为CITY
的每个值提供一行;在同一行上,它使用CASE
只在符合某些条件时才计算该行。