使用四个表查询SQL(Querying SQL with four tables)

有人可以解释一下如何查询这些表格，以获得他们所在的每个国家的公司员工人数吗？结果列的数量应为3，公司，国家和员工。公司和分公司相连，但国家与城市相连，所以我不知道如何解决这个问题。谢谢你们，欣赏它！

CREATE TABLE country ( id integer NOT NULL PRIMARY KEY, name varchar NOT NULL, population integer check(population > 0) ); CREATE TABLE city ( id integer NOT NULL PRIMARY KEY, name varchar NOT NULL, population integer check(population > 0), country integer NOT NULL REFERENCES country(id) ); CREATE TABLE company ( id integer NOT NULL PRIMARY KEY, name varchar NOT NULL ); CREATE TABLE branch ( company integer NOT NULL REFERENCES company(id), city integer NOT NULL REFERENCES city(id), name varchar, employees integer NOT NULL, PRIMARY KEY (company, city) );

这就是我所做的，但我知道这是错误的，我尝试了我的组合，但到目前为止没有什么是正确的！

SELECT branch.employees, company.name AS company, country.name AS country FROM branch INNER JOIN company ON branch.company = company.id INNER JOIN country ON city.country = country.id ORDER BY country;

Can someone explain me how to query these tables to get number of employees in companies for each country they are present in? Number of resulting columns should be 3, Company, Country and Employees. Company and Branch are connected but Country is connected with City so I am not sure how to solve this. Thanks guys, appreciate it!!!

CREATE TABLE country ( id integer NOT NULL PRIMARY KEY, name varchar NOT NULL, population integer check(population > 0) ); CREATE TABLE city ( id integer NOT NULL PRIMARY KEY, name varchar NOT NULL, population integer check(population > 0), country integer NOT NULL REFERENCES country(id) ); CREATE TABLE company ( id integer NOT NULL PRIMARY KEY, name varchar NOT NULL ); CREATE TABLE branch ( company integer NOT NULL REFERENCES company(id), city integer NOT NULL REFERENCES city(id), name varchar, employees integer NOT NULL, PRIMARY KEY (company, city) );

This is what I did but I know it is wrong, I tried my combinations but so far nothing was correct!

SELECT branch.employees, company.name AS company, country.name AS country FROM branch INNER JOIN company ON branch.company = company.id INNER JOIN country ON city.country = country.id ORDER BY country;

最满意答案

SELECT company.name AS company, country.name AS country, sum(branch.employees) AS country_employees FROM branch JOIN company ON company.id = branch.company JOIN city ON city.id = branch.city JOIN country ON country.id = city.country GROUP BY company.name, country.name ORDER BY country.name, company.name;

您希望总结每个公司的员工数量，对于他们所在的每个国家/地区。这意味着您必须对这两个字段进行GROUP BY同时记录每个分支的员工数量。您也需要使用城市表，即使您没有显示任何数据。

阅读聚合函数以获取更多背景信息。

SELECT company.name AS company, country.name AS country, sum(branch.employees) AS country_employees FROM branch JOIN company ON company.id = branch.company JOIN city ON city.id = branch.city JOIN country ON country.id = city.country GROUP BY company.name, country.name ORDER BY country.name, company.name;

You want to sum over the number of employees per company, for each of the countries they work in. This means that you have to GROUP BY those two fields while you SUM the number employees per branch. You need to use the city table as well, even though you do not display any data from it.

Read up on aggregate functions for more background information.

更多推荐