sql - 连接两个表时计算不同的计数

Question

id1     id2    year State Gender    
====  ====== ====== ===== =======    
1       A      2008    ca      M    
1       B      2008    ca      M    
3       A      2009    ny      F   
3       A      2008    ny      F     
4       A      2009    tx      F

select
    state, gender, [year],
    count (distinct(cast(id1 as varchar(10)) + id2))
from
    tabl1
group by state, gender, [year]

我可以通过 statewise 找到非重复计数。现在我需要通过城市明智地找到不同的计数。就像在 CA - 3 个城市.. sfo、la、sanjose。我有一张查找表，可以找到州和城市。

    table2 -  city     
    ====           
   cityid  name
     ==== ====
     1   sfo                
     2   la                    
     3   sanjose 

    table 3 - state
     ====   
stateid   name
   ==== ====
    1    CA
    2    Az

table 4 lookup state city
 ====   
pk_cityId pk_state_id
1             1
2             1

select state,city,gender, [year],
        count (distinct(cast(id1 as varchar(10)) + id2))
    from
        tabl1 p
    group by state, gender, [year],city

此查询用于查找城市和州名称。

select c.city,s.state from city_state sc
inner join (select * from state)s on sc.state_id = s.state_id
inner join (select * from city)c on sc.city_id = c.city_id

我使用查找表做了类似于此查询，但问题是我得到了整个州的不同计数，并且该州的每个城市都重复相同的计数。

例如:对于 ca 的计数:10 那么城市的计数应该像 La - 5、sanjose - 4、sfo-1。

但是我的查询得到的是 sfo - 10,la-10, sanjose-10.. 我找不到较低级别的计数。任何帮助将不胜感激。

更新:我已经更新了查询和查找表。

Answer 1

您的隐含模式似乎有缺陷:

您正在尝试获取城市级别的聚合，但您正在根据州将数据表 (table1) 连接到城市表 (table2)。这将导致同一州的每个城市都具有相同的聚合值；在你的例子中:所有加利福尼亚州的计数都是 10。

你能为你的两个表提供实际的 DDL 语句吗？也许您还有其他列(city_id？)可以为您提供纠正查询所需的数据。