为一对多表创建查询 [英] Creating queries for one-to-many tables

问题描述

我有三张桌子，里面有三个人的信息和他们拥有的

汽车。

一张桌子列出了人，一张列出了汽车，一张使用了id来自

其他两个来编制每个人拥有的汽车型号列表。

鲍勃拥有一辆福特，保罗拥有一辆丰田，而戴夫拥有两辆一辆福特和一辆

丰田。


简单。


如何创建一个告诉我的查询''谁拥有福特和

不拥有丰田'' - 你怎么得到鲍勃的结果？


表：人

字段：person_id，person_name

数据：1 - Bob，2 - Paul，3 - Dave


table：car

字段：car_id，car_name

数据：1 - 福特，2 - 丰田


表：person_car

字段：person_id，car_id

数据：1 - 1,2 - 2,3 - 1,3 - 2

I have three tables, containing information about three people and the
cars that they own.
One table lists people, one lists cars, and one uses the id''s from the
other two to compile a list of which models of car each person owns.
Bob owns a Ford, Paul owns a Toyota, and Dave owns both a Ford and a
Toyota.

Simple.

How do I create a query that will tell me ''which people own a Ford and
do not own a Toyota'' - i.e. how do you get a result of just Bob?

table: person
fields: person_id, person_name
data: 1 - Bob, 2 - Paul, 3 - Dave

table: car
fields: car_id, car_name
data: 1 - Ford, 2 - Toyota

table: person_car
fields: person_id, car_id
data: 1 - 1, 2 - 2, 3 - 1, 3 - 2

推荐答案

列出了哪些人o只有福特，

SELECT P.person_name来自人P，车C，person_car PC

WHERE PC。 person_id = P.person_id和PC.car_id = C.car_id和

C.car_name =''福特'';

This lists which people own a only a Ford,
SELECT P.person_name FROM person P,car C, person_car PC
WHERE PC. person_id=P.person_id and PC.car_id=C.car_id and
C.car_name=''Ford'';

这列出了哪些人只拥有福特，


SELECT P.person_name，count（PC.car_id）MyNo FROM person P，car C，

person_car PC

WHERE PC。 person_id = P.person_id和PC.car_id = C.car_id和

C.car_name =''福特''和MyNo = 1

This lists which people own a only a Ford,

SELECT P.person_name,count(PC.car_id) MyNo FROM person P,car C,
person_car PC
WHERE PC. person_id=P.person_id and PC.car_id=C.car_id and
C.car_name=''Ford'' and MyNo=1

m.******@btinternet.com 写道：

我有三张桌子，里面有三个人和他们拥有的汽车的信息。
一张桌子列出了人，一张列出了汽车，一张使用了来自
其他的ID两个人可以列出每个人拥有的汽车型号列表。
鲍勃拥有一辆福特汽车，保罗拥有一辆丰田汽车，戴夫拥有一辆福特汽车和一辆丰田汽车。

很简单。

如何创建一个查询，告诉我'哪些人拥有福特并且
不拥有丰田' - 即如何获得Bob的结果？表格：人物
字段：person_id，person_name
数据：1 - Bob，2 - Paul，3 - Dave

表：car
字段：car_id，car_name
数据：1 - 福特，2 - 丰田

表：person_car
字段：person_id，car_id
数据：1 - 1,2 - 2,3 - 1,3 - 2

I have three tables, containing information about three people and the
cars that they own.
One table lists people, one lists cars, and one uses the id''s from the
other two to compile a list of which models of car each person owns.
Bob owns a Ford, Paul owns a Toyota, and Dave owns both a Ford and a
Toyota.

Simple.

How do I create a query that will tell me ''which people own a Ford and
do not own a Toyota'' - i.e. how do you get a result of just Bob?

table: person
fields: person_id, person_name
data: 1 - Bob, 2 - Paul, 3 - Dave

table: car
fields: car_id, car_name
data: 1 - Ford, 2 - Toyota

table: person_car
fields: person_id, car_id
data: 1 - 1, 2 - 2, 3 - 1, 3 - 2

从人员中选择person.person_name

person.person_id = person_car.person_id car.car_id =

person_car.car_id car.car_name =''ford''和car .car_name！='''toyota'';


-


访问： http://www.h4xx0r.ch

这篇关于为一对多表创建查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！