請問 MySQL 和 PostgreSQL 中使用 Group By 問題?

edited 十月 2013 in 資料庫
我建了兩個表格叫 authors 和 books .

select * from authors; 的結果如下:

author_id | author_name | author_city
+
+
1 | Alex | New York
2 | Ben | New York
3 | Cook | Taipei
4 | Clus | Taipei
5 | Class | Taipei
6 | Delta | Tokyo


select * from books; 的結果如下:

book_id | book_title | book_version | a_id
+
+
+
1 | sql book | 2 | 1
2 | java book | 3 | 1
3 | c++ book | 4 | 3
4 | c# book | 4 | 3


select * from authors, books; 的結果如下:

author_id | author_name | author_city | book_id | book_title | book_version | a_id
+
+
+
+
+
+
1 | Alex | New York | 1 | sql book | 2 | 1
1 | Alex | New York | 2 | java book | 3 | 1
1 | Alex | New York | 3 | c++ book | 4 | 3
1 | Alex | New York | 4 | c# book | 4 | 3
2 | Ben | New York | 1 | sql book | 2 | 1
2 | Ben | New York | 2 | java book | 3 | 1
2 | Ben | New York | 3 | c++ book | 4 | 3
2 | Ben | New York | 4 | c# book | 4 | 3
3 | Cook | Taipei | 1 | sql book | 2 | 1
3 | Cook | Taipei | 2 | java book | 3 | 1
3 | Cook | Taipei | 3 | c++ book | 4 | 3
3 | Cook | Taipei | 4 | c# book | 4 | 3
4 | Clus | Taipei | 1 | sql book | 2 | 1
4 | Clus | Taipei | 2 | java book | 3 | 1
4 | Clus | Taipei | 3 | c++ book | 4 | 3
4 | Clus | Taipei | 4 | c# book | 4 | 3
5 | Class | Taipei | 1 | sql book | 2 | 1
5 | Class | Taipei | 2 | java book | 3 | 1
5 | Class | Taipei | 3 | c++ book | 4 | 3
5 | Class | Taipei | 4 | c# book | 4 | 3
6 | Delta | Tokyo | 1 | sql book | 2 | 1
6 | Delta | Tokyo | 2 | java book | 3 | 1
6 | Delta | Tokyo | 3 | c++ book | 4 | 3
6 | Delta | Tokyo | 4 | c# book | 4 | 3
(24 筆資料列)


在 PostgreSQL 中, 下面這行指令會出錯 :

select * from authors, books group by author_id;

錯誤訊息: column "authors.author_name" must appear in the GROUP BY clause or be used in an aggregate function


但是在 MySQL 中, 結果如下:

mysql> select * from authors, books group by author_id;
+
+
+
+
+
+
+
+
| author_id | author_name | author_city | book_id | book_title | book_version | a_id |
+
+
+
+
+
+
+
+
| 1 | Alex | New York | 1 | sql book | 2 | 1 |
| 2 | Ben | New York | 1 | sql book | 2 | 1 |
| 3 | Cook | Taipei | 1 | sql book | 2 | 1 |
| 4 | Clus | Taipei | 1 | sql book | 2 | 1 |
| 5 | Class | Taipei | 1 | sql book | 2 | 1 |
| 6 | Delta | Tokyo | 1 | sql book | 2 | 1 |
+
+
+
+
+
+
+
+
6 rows in set (0.00 sec)



接下來, 在 PostgreSQL 中, 下面這行指令會出錯:

select *,count(*) from authors, books group by author_id;

錯誤訊息: column "authors.author_name" must appear in the GROUP BY clause or be used in an aggregate function


但是在 MySQL 中, 結果如下:

mysql> select *,count(*) from authors, books group by author_id;
+
+
+
+
+
+
+
+
+
| author_id | author_name | author_city | book_id | book_title | book_version | a_id | count(*) |
+
+
+
+
+
+
+
+
+
| 1 | Alex | New York | 1 | sql book | 2 | 1 | 4 |
| 2 | Ben | New York | 1 | sql book | 2 | 1 | 4 |
| 3 | Cook | Taipei | 1 | sql book | 2 | 1 | 4 |
| 4 | Clus | Taipei | 1 | sql book | 2 | 1 | 4 |
| 5 | Class | Taipei | 1 | sql book | 2 | 1 | 4 |
| 6 | Delta | Tokyo | 1 | sql book | 2 | 1 | 4 |
+
+
+
+
+
+
+
+
+
6 rows in set (0.00 sec)



看了 MySQL 的結果知道他的結果大概怎麼出來的, 但是現在我想要讓那幾行指令也能在 PostgreSql 中運作, 請問大家有什麼建議嗎?

原始討論: http://twpug.net/x/modules/newbb/viewtopic.php?topic_id=3593

評論

Sign In or Register to comment.