代码之家 › 专栏 › 技术社区 › Zak

需要有关编写SQL查询的帮助

oracle database sql-server mysql sql

Zak · 技术社区 · 14 年前

我有下表。

客户表
客户姓名市区国号

乡村餐桌
国家国家

区域表
地区地区国家ID

城桌
城市城市地区国家ID

我需要从城市的角度找出客户的数量,也就是说,对于所有国家,对于所有国家的地区,对于该地区的所有城市,我需要得到每个城市的客户数量。

例如,我编写了以下SQL来获取每个国家/地区的客户数量:

SELECT a.country_id , b.country,count(*) 
FROM Customer_Table a INNER JOIN Country_Table b
ON a.country_id = b.country_id
GROUP BY b.country , b.country_id ;

并获取特定国家/地区的客户数量:

SELECT a.region_id , b.region , count(*)
FROM Customers_table a INNER JOIN Region_Table b
ON a.region_id = b.region_id
WHERE a.country_id ='USA' AND b.country_id ='USA'
GROUP BY region_id , country ;

我需要找到的是城市范围内的客户数量,也就是说,对于所有国家,对于国家的所有地区,对于该地区的所有城市,我需要得到每个城市的客户数量,比如

Country | Region  |       City     | no.of customers
  USA   |   CA    |  San Francisco |    53
  USA   |   CA    |  Los Angeles   |    45
  USA   |   CA    |  Sacramento    |    16
  USA   |   WA    |  Seattle       |    46
  USA   |   WA    |  Olympia       |    9
  UK    | England |  London        |    89
  UK    | England |  Nottingham    |    71
  UK    | Wales   |  Cardiff       |    69
  UK    | Wales   |  Newport       |    23

  ..................and so on for other countries.

3 回复 | 直到 12 年前

Gabriele Petrioli 14 年前

您的表有冗余信息

因为它们是链接的,所以每个表只需要引用其直接父级,而不需要引用层次结构中的所有父级。

所以客户只需要参考城市、城市、地区、国家

SELECT
    Country_Table.Country,
    Region_Table.Region,
    City_Table.City,
    Count(Customer_Id) AS Customers
FROM
    Country_Table
    INNER JOIN Region_Table ON Region_Table.Country_Id = Country_Table.Country_Id
    INNER JOIN City_Table ON City_Table.Region_Id = Region_Table.Region_Id
    INNER JOIN Customer_Table ON Customer_Table.City_Id = City_Table.City_Id
GROUP BY
    Country_Table.Country,
    Region_Table.Region,
    City_Table.City

Pranay Rana 14 年前

试试这个:

select Country,Region,City,count(Customer_ID)
from Customer_table as cust
inner join  Country_table on Country_ID=cust.Country_ID
inner join Region_table on Region_ID=cust.Region_ID
inner join Region_table on City_ID=cust.City_ID
group by country,Region,City

LittleBobbyTables - Au Revoir 14 年前

对于SQLServer:

SELECT Country, Region, City, COUNT(Customer_ID) as [no.of customers]
FROM Country_Table
INNER JOIN Region_Table ON Country_Table.Country_ID = Region_Table.Country_ID
INNER JOIN City_Table ON City_Table.Region_ID = Region_Table.Region_ID
INNER JOIN Customer_Table ON Customer_Table.City_ID = City_Table.City_ID
GROUP BY Country, Region, City;