获取连接查询中列的和 - Get sum of column in a join query

Get sum of column in a join query

本文关键字：连接查询获取 | 更新日期: 2023-09-27

我有查询…

  $productInfo = "SELECT list.* , production.*, Sum(production.units) AS totalQty
      FROM  production
      LEFT JOIN list
      ON list.id = production.com_id
      WHERE id = '$productID'
      AND production.userid = $userID";

由于某种原因，这只返回1行和总和。这个表有2行，它们都应该被返回。

如果我删除Sum(production.units) AS totalQty，它返回2行，但我将无法检索Sum。

解决这个问题的最好方法是什么?由于

您缺少GROUP BY:

SELECT l.*, Sum(p.units) AS totalQty
FROM  production p LEFT JOIN
      list l
      ON l.id = p.com_id
WHERE l.id = '$productID' AND p.userid = $userID
GROUP BY l.id;

实际上，left join在你得到它的顺序上没有意义。你可能想:

SELECT l.*, Sum(p.units) AS totalQty
FROM  list l LEFT JOIN
      production p 
      ON l.id = p.com_id AND p.userid = $userID
WHERE l.id = '$productID' AND 
GROUP BY l.id;

这将为您提供每个列表，其中包含该用户的总产量。

存储函数是一种返回单个值的特殊类型的存储程序。您可以使用存储函数封装在SQL语句或存储程序之间可重用的公共公式或业务规则。

    DELIMITER $$
CREATE FUNCTION mysum(p_creditLimit double,product_id Integer,user_id Interger) RETURNS VARCHAR(10)
    DETERMINISTIC
BEGIN
    DECLARE lvl varchar(10);
  DECLARE select_var VARCHAR(500);
  SET select_var = (SELECT list.* , production.*, Sum(production.units) AS totalQty
      FROM  production
      LEFT JOIN list
      ON list.id = production.com_id
      WHERE id = product_id
      AND production.userid = user_id limit 1);
  RETURN select_var;
END

并简单地调用SQL查询

 $productInfo = "SELECT list.* , production.*, mysum(production.units,".$productID.",".$userID.") AS totalQty
      FROM  production
      LEFT JOIN list
      ON list.id = production.com_id
      WHERE id = '".$productID."'
      AND production.userid = $userID";

现在$productInfo返回所有行

源http://www.mysqltutorial.org/mysql-stored-function/