作者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