MySQL-从两个表中正确获取用户及其订单数据

Question

我有具有此结构的用户和订单表(针对问题进行了简化):

用户

用户ID

ownerid(用户所有者 ID，如主用户)

公司

订单

ID

日期(下订单日期)

用户 ID

总计

我需要获取与在选定时间段内下订单的一位所有者(ownerid，例如 52)相关的所有用户，作为包含某些字段的数组(请参见查询)。不幸的是，我的请求给出了一个(不是数组)而不是当前结果(有时甚至在所有字段中都为空)。这是我的要求:

SELECT
  ei_users.userid as id,
  ei_users.company as company,
  ei_users.city as city,
  ei_users.user_type as user_type,
  ei_users.registered as registered,
  count(ei_orders.id) AS orders,
  sum(ei_orders.total) AS revenue
FROM
 ei_orders,
 ei_users
WHERE
 ei_users.userid = ei_orders.user_id 
 && ei_users.ownerid = 52 
 && DATE_FORMAT(ei_orders.date, "%b") = "Apr" 
 && DATE_FORMAT(ei_orders.date, "%Y") = "2019"

请告诉我我的请求有什么问题以及为什么我没有得到带有结果的数组？

我发现我的错误，我忘记在末尾添加GROUP BY ei_users.userid:

SELECT
  ei_users.userid as id,
  ei_users.company as company,
  ei_users.city as city,
  ei_users.user_type as user_type,
  ei_users.registered as registered,
  count(ei_orders.id) AS orders,
  sum(ei_orders.total) AS revenue
FROM
 ei_orders,
 ei_users
WHERE
 ei_users.userid = ei_orders.user_id 
 && ei_users.ownerid = 52 
 && DATE_FORMAT(ei_orders.date, "%b") = "Apr" 
 && DATE_FORMAT(ei_orders.date, "%Y") = "2019"
GROUP BY ei_users.userid

Answer 1

看起来我忘记在最后添加:

GROUP BY ei_users.userid