如何从子查询SQLite接收两个值 [英] How to receive two values from a subquery SQLite
问题描述
我是SQLite的自学和新手.我有三个表(人,宠物,人_宠物),. schema是:
I'm a self-learning and newbie in SQLite. I have three tables (person, pet, person_pet) and the .schema is:
CREATE TABLE person (
id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT,
age INTEGER,
dead INTEGER,
phone_number INTEGER,
salary FLOAT,
dob DATETIME
);
CREATE TABLE person_pet (
person_id INTEGER,
pet_id INTEGER,
);
CREATE TABLE pet (
id INTEGER PRIMARY KEY,
name TEXT,
breed TEXT,
age INTEGER,
dob DATETIME,
purchased_on DATETIME,
parent INTEGER /*Contains the ID of the pet's parent*/
);
我的任务是编写一个查询,该查询可以找到2004年之后购买的宠物及其所有者的所有名称.关键是基于person_pet列将person_pet映射到宠物和父母.我尝试做一个返回两个值的子查询,但是它不起作用.(你们中的有些人会说:很明显".)我的问题是:如果我不能从子查询中返回两个值,该如何完成此任务?
My task is writing a query that can find all the names of pets and their owners bought after 2004. Key to this is to map the person_pet based on the purchased_on column to the pet and parent. I tried doing a subquery that returns two values but it doesn't work. (Some of you would say: "Obviously".) My question is: if I can't return two values from a subquery how can I achieve this task?
我尝试了此子查询:
SELECT first_name, name FROM person, pet WHERE person.id pet.id IN(
SELECT person_id FROM person_pet WHERE pet_id IN (
SELECT id FROM pet WHERE purchased_on IN (
SELECT pet.purchased_on
FROM pet, person_pet, person
WHERE person.id = person_pet.person_id AND
pet.id = person_pet.pet_id AND
pet.purchased_on > '2004/01/01 0:0:0 AM'
)
)
SELECT pet_id FROM person_pet WHERE id IN (
SELECT id FROM pet WHERE purchased_on IN (
SELECT pet.purchased_on
FROM pet, person_pet, person
WHERE person.id = person_pet.person_id AND
pet.id = person_pet.pet_id AND
pet.purchased_on > '2004/01/01 0:0:0 AM'
)
)
);
PS:对不起,如果我的问题有点长.
PS: Sorry if my question is a bit long.
推荐答案
您不需要子查询.您需要加入:
You don't need a sub-query. You need joins:
SELECT p.id, p.first_name, p.last_name, p2.id as pet_id, p2.name as pet_name
FROM person as p
INNER JOIN person_pet as pp on pp.person_id = p.id
INNER JOIN pet as p2 on p2.id = pp.pet_id
WHERE
p2.purchased_on > '2004/01/01 0:0:0 AM'
这篇关于如何从子查询SQLite接收两个值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!