gpt4 book ai didi

sqlite - 如何正确处理 sqlite 表自引用?

转载 作者:行者123 更新时间:2023-12-03 19:37:43 24 4
gpt4 key购买 nike

我为一小群牛创建了一个 sqlite 数据库。

CREATE TABLE Animals
(
animal_id PRIMARY KEY,
animal_name CHAR(15) NULL,
date_born DATE NULL,
f_parent REFERENCES Animals (animal_id) NULL,
m_parent REFERENCES Animals (animal_id) NULL,
date_purchased DATE NULL,
registered BIT NOT NULL,
gender CHAR(1) NOT NULL CHECK(gender IN ("M","F")),
breed INTEGER NOT NULL REFERENCES breed (breed_id)
);


CREATE TABLE termination (term_key INTEGER PRIMARY KEY, animal_id INTEGER, term_date DATE, sold BIT, price SMALLMONEY, comp_market_price SMALLMONEY, comp_market_tier TEXT);

我有这样的声明:
SELECT a1.animal_id, a1.animal_name, a1.f_parent, t1.term_date,   t1.price, t1.comp_market_price, t1.comp_market_tier
FROM Animals AS a1, termination AS t1
WHERE a1.animal_id = t1.animal_id
AND a1.f_parent NOT NULL;

结果是:

id#|'动物名称'|' parent id#'|销售日期...

15|一些名字|4|2014-05-26 ...

...

这是正确的,也是我想要的,除了我想要 parent 的名字来代替“ parent id#”。父 id# 是与后代在同一个表中的键(正如您从上面的 create 语句中看到的那样),但我不知道如何处理这种自引用。我知道这个问题相当普遍,我尝试过查看表、多个连接等,但无济于事。请显示我如何打印相同结果的代码片段,显示 parent 姓名代替 parent id#/key no。

非常感谢!

最佳答案

可能是这样的?

select a.animal_id, a.animal_name,
(select animal_name
from animals
where a.f_parent = animal_id) as parent,
t.term_date, t.price, t.comp_market_price, t.comp_market_tier
from animals as a, termination as t using(animal_id)
where a.f_parent not null;

或这个? (更好的执行计划)
select a.animal_id as id, a.animal_name as name,f.animal_name as mother,
t.term_date, t.price, t.comp_market_price, t.comp_market_tier
from animals as a, termination as t using(animal_id),
animals f
where a.f_parent = f.animal_id
and a.f_parent is not null;

关于sqlite - 如何正确处理 sqlite 表自引用?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/33060971/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com