In postgres I am fairly sure you can do something like this
在postgres我很相信你可以做这样的事情
SELECT
authors.stage_name,
count(select id from books where books.author_id = authors.id)
FROM
authors,
books;
Essentially, in this example I would like to return a list of authors and how many books each has written.... in the same query.
从本质上讲,在这个例子中,我想在同一个查询中返回一个作者列表和每个书写的书数....
Is this possible? I suspect this approach is rather naive..
这可能吗?我怀疑这种方法很天真。
Thanks :)
2 个解决方案
#1
1
Well, for one thing, it returns a Cartesian product of all authors to all books, regardless of whether that author wrote that book.
嗯,首先,它将所有作者的笛卡尔积都归还给所有书籍,无论该作者是否写过该书。
Here's how I'd write a query to get the result you say you want:
以下是我如何编写查询以获得您想要的结果:
SELECT a.stage_name, COUNT(b.id)
FROM authors a
LEFT OUTER JOIN books b ON (a.id = b.author_id)
GROUP BY a.id;
You need to learn how to write join queries if you use SQL. The join is to SQL what the loop is to application programming languages. I.e. it's a fundamental programming construct that you need to know.
如果使用SQL,则需要学习如何编写连接查询。连接是SQL对应用程序编程语言的循环。即它是您需要了解的基本编程结构。
#2
0
How about using a join:
如何使用连接:
SELECT authors.stage_name, count(*)
FROM authors INNER JOIN books on books.author_id = authors.id
GROUP BY authors.stage_name
#1
1
Well, for one thing, it returns a Cartesian product of all authors to all books, regardless of whether that author wrote that book.
嗯,首先,它将所有作者的笛卡尔积都归还给所有书籍,无论该作者是否写过该书。
Here's how I'd write a query to get the result you say you want:
以下是我如何编写查询以获得您想要的结果:
SELECT a.stage_name, COUNT(b.id)
FROM authors a
LEFT OUTER JOIN books b ON (a.id = b.author_id)
GROUP BY a.id;
You need to learn how to write join queries if you use SQL. The join is to SQL what the loop is to application programming languages. I.e. it's a fundamental programming construct that you need to know.
如果使用SQL,则需要学习如何编写连接查询。连接是SQL对应用程序编程语言的循环。即它是您需要了解的基本编程结构。
#2
0
How about using a join:
如何使用连接:
SELECT authors.stage_name, count(*)
FROM authors INNER JOIN books on books.author_id = authors.id
GROUP BY authors.stage_name