帮忙编写 derby 的数据库查询吗？

发布于 2024-08-25 00:59:01 字数 422 浏览 5 评论 0原文

我有一个包含多个表（Person、Parents 等）的数据库，

Person 表具有某些属性，特别是 ssn、countryofbirth 和 currentcountry。

父母表有 ssn，而fathersbirthcountry

个人中的 ssn 与父母中的 ssn 相同 - 这就是它们的链接方式。

我正在尝试输出所有出生国家与父亲出生国家相同并且当前国家/地区与父亲出生国家相同的人的 SSN。

SELECT Person.ssn 
FROM Person, Parents 
WHERE fathersbirthcountry = countryofbirth 
AND currentcountry = fathersbirthcountry;

以上似乎不起作用，有人可以帮我吗？

原文

I have a database containing multiple tables (Person, Parents, etc)

Person table has certain attributes particularly ssn, countryofbirth and currentcountry.

Parents table has ssn, and fathersbirthcountry

The ssn in Person is the same ssn in Parents - that is how they're linked.

I'm trying to output the SSNs of all people who have the same countryofbirth as their fathersbirthcountry and also have same currentcountry as fathersbirthcountry.

SELECT Person.ssn 
FROM Person, Parents 
WHERE fathersbirthcountry = countryofbirth 
AND currentcountry = fathersbirthcountry;

the above doesn't seem to be working, could anyone please help me out?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

伴我心暖 2024-09-01 00:59:02

您从未提及 Person 是如何存储其对 Parent 的引用的。我假设 Person 表中有一个 MotherId 和 FatherId，这样您就会得到：

Select SSN
From Person
Where BirthCountry =    (
                        Select BirthCountry
                        From Parents
                        Where Person.FatherId = Parents.Id
                        )

现在，假设 Person 表中的 BirthCountry 与 Parent 表中的 BirthCountry 列表相同。

You never mention how Person's stores its reference to Parents. I'll assume there is a MotherId and FatherId in the Person table so you would get:

Select SSN
From Person
Where BirthCountry =    (
                        Select BirthCountry
                        From Parents
                        Where Person.FatherId = Parents.Id
                        )

Now, this assumes BirthCountry in the Person table is the same list as BirthCountry in the Parents table.

回复收藏 0 原文

空名 2024-09-01 00:59:01

您没有明确什么条件将人员记录与父记录链接起来。对于此示例，我将假设 Person 包含一个您未提及的附加字段，称为 FatherSSN。如果是这样：

   SELECT Person.SSN 
   FROM Person, Parents
   WHERE Person.FatherSSN = Parents.SSN
     AND Person.CountryOfBirth = Parents.FathersBirthCountry
     AND Person.CurrentCountry = Parents.FathersBirthCountry

或者，在 SQL-92 JOIN 语法中：

   SELECT Person.SSN 
   FROM Person INNER JOIN Parents
   ON Person.FatherSSN = Parents.SSN
     AND Person.CountryOfBirth = Parents.FathersBirthCountry
     AND Person.CurrentCountry = Parents.FathersBirthCountry

两个版本应该产生相同的结果（和执行计划）。

最后，如果这是您自己的数据库，则可以轻松且有利地重构为仅包含单个 Person 表，该表使用与您现在拥有的单个表完全相同的结构来保存所有代。如果您进行重组，您的 SQL 将如下所示：

   SELECT P1.SSN 
   FROM Person P1 INNER JOIN Parents P2
   ON P1.FatherSSN = P2.SSN
     AND P1.CountryOfBirth = P2.CountryOfBirth
     AND P1.CurrentCountry = P2.CountryOfBirth

You do not make clear what condition links a Person record with a Parent record. For this example, I am going to assume that Person contains an additional field, not mentioned by you, called FatherSSN. If so:

   SELECT Person.SSN 
   FROM Person, Parents
   WHERE Person.FatherSSN = Parents.SSN
     AND Person.CountryOfBirth = Parents.FathersBirthCountry
     AND Person.CurrentCountry = Parents.FathersBirthCountry

or, in SQL-92 JOIN syntax:

   SELECT Person.SSN 
   FROM Person INNER JOIN Parents
   ON Person.FatherSSN = Parents.SSN
     AND Person.CountryOfBirth = Parents.FathersBirthCountry
     AND Person.CurrentCountry = Parents.FathersBirthCountry

The two versions should yield the same result (and execution plan).

Finally, if this is your own database it could easily and profitably be refactored to feature only a single Person table holding all generations using exactly the same structure for that single table as you have now. If you make that restructuring, your SQL would look like this:

   SELECT P1.SSN 
   FROM Person P1 INNER JOIN Parents P2
   ON P1.FatherSSN = P2.SSN
     AND P1.CountryOfBirth = P2.CountryOfBirth
     AND P1.CurrentCountry = P2.CountryOfBirth

回复收藏 0 原文

~没有更多了~