MySQL查询到MS访问查询

Hello I have been working with a project to MySQL database and I am converting it to MS ACCESS database.

你好，我一直在做一个MySQL数据库的项目，我正在把它转换成MS ACCESS数据库。

Can someone help me for the equivalent of this query from MySQL to MS ACCESs SQL? Becouse my ms access query does not work.

有人能帮我从MySQL到MS ACCESs SQL的等价查询吗?因为我的ms访问查询不起作用。

The MySQL Query :

MySQL查询:

Select count(u.phone) as ordernr, u.firstname , u.lastname , u.address from user u join orders o on u.phone = o.phone group by firstname ;

选择count(u - phone)作为ordernr, u。firstname,u。姓,u。从用户u加入订单o到u。电话= o。电话组以姓名命名;

MS ACCESS Query (is not working) which i tryed so far:

MS ACCESS Query(不工作)，我一直在查询:

Select count(u.phone) as ordernr, u.firstname as firstname, u.lastname as lastname , u.address as add from user u inner join orders o on u.phone = o.phone group by firstname ;

选择count(u - phone)作为ordernr, u。firstname firstname,u。lastname as lastname, u。地址从用户u内部连接订单o添加到u。电话= o。电话组以姓名命名;

is giving me this error message : your query does not include the specified expression 'lastname' as part of an aggregate function.

给我这个错误消息:您的查询不包含指定的表达式‘lastname’作为聚合函数的一部分。

Looking forward for your help and/or suggestions! Thank you

期待您的帮助和/或建议!谢谢你！

2 个解决方案

#1

The correct MS Access version is:

正确的MS Access版本是:

Select count(u.phone) as ordernr, u.firstname, u.lastname, u.address
from [user] as u inner join
     orders as o
     on u.phone = o.phone
group by u.firstname, u.lastname, u.address;

Note:

注意:

user is a reserved word in MS Access.
用户是MS Access中的一个保留字。
join operation needs to include the inner.
连接操作需要包含内部操作。
as is needed for table aliases.
表别名所需要的。
The group by needs to include all columns (that is a SQL-thing true of all databases except MySQL).
group by需要包含所有列(这是除MySQL外的所有数据库的sql)。

#2

You have to include in the GROUP BY clause every non-aggregated field present in the SELECT clause:

您必须在GROUP BY子句中包含SELECT子句中出现的每个非聚合字段:

Select count(u.phone) as ordernr, 
       u.firstname as firstname, 
       u.lastname as lastname , 
       u.address as addr 
from user u 
inner join orders o on u.phone = o.phone
group by firstname, lastname, addr ;

#1