仅以多对多关系检索第一行 [英] Retrieve first row only in many-to-many relationship
问题描述
我有一个带有三个表的数据库 - 书籍，部分和连接表。
获得多对多关系的正常方式（即一本书
可能属于很多部分，一个部分可能包含很多书籍。
我想为每本书提取一行数据，这样我就可以获得
只检索任何书籍的第一部分描述。 （例如标题，
作者，部分，描述）
结构如下：
tbl_book
book_id，title，author，description etc ...
tbl_section
section_id，section_desc
tbl_book_section
book_id，section_id
DBA离开了我根本无法解决这个问题...感谢任何帮助
收到
。
I have a db with three tables - books, sections, and a joining table.
The normal way of getting a many to many relationship (i.e. one book
may belong to many sections, and one section may contain many books)
I want to extract the data with a single row for each book so that I
only retrieve the first section description for any book. (e.g. title,
author, section, description)
Structure as follows:
tbl_book
book_id, title, author, description etc...
tbl_section
section_id, section_desc
tbl_book_section
book_id, section_id
DBA is away and I can''t figure this out at all...any help gratefully
received.
推荐答案
试试这个。我假设通过第一部分你的意思是最低编号
section_id。
SELECT B1.book_id，B1.title，B1.author，B1.description，
S2.section_id，S2.section_desc
FROM tbl_book AS B1
JOIN
（SELECT book_id，MIN（section_id）AS section_id
FROM tbl_book_section
GROUP BY book_id）AS S1
ON B1.book_id = S1.book_id
JOIN tbl_section AS S2
ON S1.section_id = S2.section_id
-
David Portas
- ----------
请只回复新闻组
-
Try this. I''m assuming that by "first section" you mean the lowest numbered
section_id.
SELECT B1.book_id, B1.title, B1.author, B1.description,
S2.section_id, S2.section_desc
FROM tbl_book AS B1
JOIN
(SELECT book_id, MIN(section_id) AS section_id
FROM tbl_book_section
GROUP BY book_id) AS S1
ON B1.book_id = S1.book_id
JOIN tbl_section AS S2
ON S1.section_id = S2.section_id
--
David Portas
------------
Please reply only to the newsgroup
--
请发布DDL，这样人们就不必猜测你的架构是什么键，
约束，声明性参照完整性，数据类型等等。样本数据也是一个好主意，同时还有明确的
规范。
由于SQL是一种面向集合的语言，因此不存在
表中的第一行。下一个基本原则是所有
关系都显示为列中的值。因此，你必须
在DDL中有一段你没有张贴的款号
这才有意义。
Book_id应该是一个ISBN，但是我们不知道哪个section_id是
喜欢和它是否有订单。
当DBA获得时回来，请他阅读并使用ISO-11179命名
标准。他给你的东西说你只有一本关于家具，特别是桌子的书。
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.
Since SQL is a set-oriented language, there is no such concept as a
first row in a table. The next basic principle is that all
relationships are shown as values in a column. Therefore, you must
have a section number of some kind in the DDL that you did not post
for this to make sense.
Book_id ought to be an ISBN, but we have no idea what section_id is
like and if it has an ordering.
When the DBA gets back, ask him to read and use ISO-11179 naming
standards. What he ias given you says that you only have one book
about furniture, specifically tables.
David
这就好了谢谢。
Gareth
" David Portas" < RE **************************** @ acm.org>在消息新闻中写道：< ce ******************** @ giganews.com> ...
David
That did the trick thanks.
Gareth
"David Portas" <RE****************************@acm.org> wrote in message news:<ce********************@giganews.com>...
试试这个。我假设通过第一部分你的意思是编号最小的
section_id。
SELECT B1.book_id，B1.title，B1.author，B1.description，
S2.section_id，S2.section_desc FROM tbl_book AS B1
JOIN
（SELECT book_id，MIN（section_id）AS section_id
FROM tbl_book_section
GROUP BY book_id）AS S1
ON B1.book_id = S1 .book_id
加入tbl_section AS S2
ON S1.section_id = S2.section_id
Try this. I''m assuming that by "first section" you mean the lowest numbered
section_id.
SELECT B1.book_id, B1.title, B1.author, B1.description,
S2.section_id, S2.section_desc
FROM tbl_book AS B1
JOIN
(SELECT book_id, MIN(section_id) AS section_id
FROM tbl_book_section
GROUP BY book_id) AS S1
ON B1.book_id = S1.book_id
JOIN tbl_section AS S2
ON S1.section_id = S2.section_id
这篇关于仅以多对多关系检索第一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
