Question

mysql 的 union 与group by 问题

select count(*) as N,a,b from (select a,b from 表1 union select a,b from 表2) group by a order by N

为什么在用access数据库就好使 用mysql 就不行呢 在mysql下该怎么写 请教各位大师

Answer 1

会不会是 group by 的地方 少了一个 字段 b

mysql> select
-> count(*) as N,
-> a,
-> b
-> from
-> (select
-> 1 AS a,
-> 1 AS b
-> union
-> select
-> 2 AS a,
-> 2 AS b
-> ) as tmp
-> group by a, b
-> order by N
-> ;
+---+---+---+
| N | a | b |
+---+---+---+
| 1 | 1 | 1 |
| 1 | 2 | 2 |
+---+---+---+
2 rows in set (0.00 sec)

追问

谢谢您的答案 是参考你的答案 完成的 呵呵

sql_1="select a,b from 表1"
sql_2="select a,b from 表2"
sql_3=sql_1 &" union all "& sql_2
sql_4="select count(*) as N,a,b from ("& sql_3 &" as NewName) NewName group by author order by NewSum desc"

Answer 2

group by a 只能针对count(*) as N,a
只要多出其他字段就会出错，比如b，想其他的方法解决。