作者cpper (>:D>)
看板Database
標題Re: [SQL ] 請教SQL達人 (有關GROUP BY)
時間Thu Aug 28 11:26:56 2008
※ 引述《ruby0104 (:))》之銘言：
: ※ 引述《cpper (>:D>)》之銘言：
: : 借用上面的表格請教一下以下的 SQL :
: : SELECT *, COUNT(*) AS counts FROM table GROUP BY visit_date ;
: : 在 MySQL 中這可以動作 @_@ , 可是在 PostgreSQL 中就不行 , 會被要求要把 id,
: : name, visit_date 等欄位補充到 GROUP BY 後面。而且跑出來的結果就和 MySQL 不一
: : 樣了。
: : 請問若要使 PostgreSQL 跑出來結果和 MySQL 一樣的話 , 該怎麼做呢 ?
: 我比較好奇的是 為什麼在MySQL里可以跑出來?
: 在select子句裡面的 除了一些function 不是都應該出現在 Group by 里嗎?
對不起, 我昨天打錯了 m(_._)m
我重新打一次請大家幫忙看看, 我建了兩個表格叫 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 中運作, 請問大家有什麼建議嗎?
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 140.96.89.57
1F：推 Antzzz:我的建議是把不在group by裡的欄位從select中去掉… 08/28 12:11
2F：推 Antzzz:全部有四筆它卻只挑一筆出來，那一筆不就規則不明嗎？ 08/28 12:17
3F：→ cpper:每個欄位都要select出來說,因為要完全轉換MySQL到PostgreSQL 08/28 13:18
4F：推 alpe:看你是要用 DISTINCT on 還是一個一個加到 group by 上 08/28 15:40