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

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]

我可以通过状态找到不同的计数。现在我需要通过城市智慧找到不同的计数。就像在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

我使用查找表进行了类似的查询，但问题是，我在各州都得到了不同的计数，并且每个州的城市都重复相同的no计数。

例句:对于ca : 10，那么城市的计数应该是La-5，sanjose - 4，sfo-1。

但是通过我的查询，我得到了sfo - 10，la-10，sanjose-10..我找不到较低级别的伯爵。任何帮助都将不胜感激。

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

Answer 1

我认为您需要以下内容，但不能确定进一步的信息：

;WITH DistinctState AS
(
    SELECT  DISTINCT
            id1
        ,   id2
        ,   [year]
        ,   [State]
        ,   Gender
    FROM    tab1
)
SELECT  s.state
,       c.city
,       gender 
,       [year]
,       count(*) 

FROM     DistinctState s
    INNER JOIN
        tab2 c
    ON  s.id1   = c.id1
 AND    s.id2   = c.id2

GROUP   BY 
        s.state
    ,   c.city
    ,   gender
    ,   [year]

Answer 2

您的隐含模式似乎有一个缺陷：

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

您能为两个表提供实际的DDL语句吗？也许您还有其他列(city_id?)这可能为您更正查询提供必要的数据。