Sql group by依靠一对多的关系

本文介绍了Sql group by依靠一对多的关系的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个应该这样做的SQL语句：

显示一个房屋的数量，对于一个房产有2个匹配的人类型的国家，即 - 现在该房产可能有20个产品总共，但2将通过我的标准匹配（125和111） - 参考数字在我的数据库中有效并且一些记录得到记录 - Level的列表在我的SQL语句中返回但是它显示了一堆属性，数量为1.

我想要的是返回国家内的数量，该国家内的房屋数量（由县和市加入）那里有1名成年男性和1名成年女性居住在那里，但只计算一次房子。

国家 - 是一个国家

县是国内的一个县

城市是县内的一个城市，在一个国家内。

房子是房子

人们是一种类型的人，可能是成年男性，成年女性，儿童男性和儿童女性 e。

LivingThere有一张桌子可以将人们连接到房子里，住在房子里。它有3个字段，一个主要的自己，一个HouseRef和一个PeopleRef。

I have a SQL Statement that is supposed to do this:
Shows a count of Houses, against Country where a Property has 2 matching "People Type" i.e. - now the Property could have 20 products in total, but 2 will have been matched via my criteria below (125 and 111) - the reference numbers are valid in my database and a number of records get record- a list of Level's come back in my SQL Statement below but its showing a bunch of Properties with a count of 1.

What I want is to return the count within Country, the number of Houses within that Country (joined by County and City) that have say 1 Adult Male and 1 Adult Female living there but count the House just once.

Country - is a Country
County is a county within the Country
City is a City within the County, within a Country.
House is a house
People is a type of people, could be "Adult Male","Adult Female","Child Male" and "Child Female".
LivingThere is a table that bridges People to the house and who lives in the house. It has 3 fields, a primary for itself, a HouseRef and a PeopleRef.

SELECT Country.Country,
Country.CountryRef,
COUNT(DISTINCT House.HouseRef) AS [Facility Count]
     FROM Country
        INNER JOIN County ON Country.CountryRef = County.CountryRef
        INNER JOIN City ON County.CountyRef = City.CountyRef
        INNER JOIN House ON City.CityRef = House.CityRef
        INNER JOIN LivingThere ON House.HouseRef = LivingThere.HouseRef
        INNER JOIN People ON LivingThere.LivingThereRef = People.LivingThereRef
WHERE     (LivingThere.LivingThereRef IN (125, 111))
GROUP BY Country.Country, Country.CountryRef, House.HouseRef
HAVING      (COUNT(LivingThere.LivingThereRef) = 2)

我想我几乎就在那里 - 我得到一份与之匹配的房屋清单计数国家的人们标准只显示每条记录1个而不是1000个反对if。如果我算上这些房子（例如将列表粘贴到开放式办公室，例如它们看似正确但我正在寻找每个国家的总分。

我错过了什么？：/

我尝试了什么：

更改分组并选择不同的

I think Im almost there - Im getting a list of houses that match the people criteria for the countries with the count just showing 1 per record instead of say 1,000 against if. and if I count those houses (by pasting the list into open office for example they seem correct but Im looking for a total sub total, per country.
What have I missed? :/

What I have tried:

Changing the group by and select distinct

Female

Sql group by依靠一对多的关系

问题描述

推荐答案