MySQL查询到MS Access查询 [英] MySQL Query to MS Access Query

问题描述

您好，我一直在使用MySQL数据库项目，并将其转换为MS ACCESS数据库.

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

有人可以帮我进行从MySQL到MS ACCESs SQL的此查询吗? 因为我的ms访问查询不起作用.

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查询:

选择count(u.phone)作为ordernr，u.firstname，u.lastname， 用户的u地址u加入订单o u.phone = o.phone分组依据 名字;

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 ;

到目前为止，我尝试过的

MS访问查询(不起作用):

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

选择count(u.phone)作为ordernr，u.firstname作为firstname， u.lastname作为lastname，u.address作为用户u内部联接顺序的添加 o在u.phone上= o.phone按名字分组；

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 ;

给我这个错误消息:您的查询未将指定的表达式姓氏"作为聚合函数的一部分.

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

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

推荐答案

正确的MS Access版本是:

The correct MS Access version is:

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;

注意:

• user是MS Access中的保留字.
• join操作需要包含inner.
表别名需要
• as.
• group by需要包括所有列(这是除MySQL外所有数据库的SQL事实).

• user is a reserved word in 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).

这篇关于MySQL查询到MS Access查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！