连接两个表，在SQL oracle中添加选择性数据

Question

这些是我的表格

C_ID  USAGE B_ID
4     100   2
2     400   1
3     200   2
1     300   1

和

B_ID  ZONE_NAME
2     central
1     west

这是我的查询，

select 
    sum(usage)  
from 
(
    select 
        usage_679.c_id,
        usage_679.b_id,usage_679.usage,
        zone_679.zone_name 
    from usage_679 inner join zone_679
        on zone_679.b_id = usage_679.b_id
)
where zone_name like 'w%';

问题是它只能打印与西区相关的数据。我想打印两个区域名称和各自区域的总使用量。

预期输出

zone_name  sum(usage)
1            700
2            300

Answer 1

下面是执行示例的SQL填充链接。您不需要WHERE条件。可以通过GROUP BY

实现

select 
    sum(usage),zone_name  
from 
(
    select 
        usage_679.c_id,
        usage_679.b_id,usage_679.usage,
        zone_679.zone_name 
    from usage_679 inner join zone_679
        on zone_679.b_id = usage_679.b_id
)
group by  zone_name

| SUM(USAGE) | ZONE_NAME |
--------------------------
|        700 |      west |
|        300 |   central |

即使这样也可以:(不需要2个select)

SELECT
    sum(USAGE),zone_name
    FROM usage_679 INNER JOIN zone_679
        ON zone_679.b_id = usage_679.b_id
GROUP BY  zone_name

输出:

| SUM(USAGE) | ZONE_NAME |
--------------------------
|        700 |      west |
|        300 |   central |

Answer 2

您可以使用GROUP BY子句来获得结果。请试一试。

select 
    zone_679.zone_name,
    zone_679.b_id,
    SUM(usage_679.usage)
from usage_679 inner join zone_679
    on zone_679.b_id = usage_679.b_id 
group by zone_679.zone_name, zone_679.b_id;

Answer 3

只是为了添加@priyanks的答案…如果您只想要西部的结果，则添加having子句，即

SELECT
sum(USAGE),zone_name
FROM usage_679 INNER JOIN zone_679
    ON zone_679.b_id = usage_679.b_id
GROUP BY  zone_name
HAVING zone_name like 'w%' --Filter added for result