mySQL中的列总数！

i have this sql :

我有这个sql:

SELECT * , (
xnum * xprice
) AS amount, SUM( xnum * xprice ) AS total
FROM xxbasket
LEFT JOIN xxsubproduct
USING ( xsubproduct_id )
LEFT JOIN xxcolor
USING ( xcolor_id )
WHERE xuserid = '3'

when i use SUM( xnum * xprice ) AS total it's gives me only one row , but when i remove this SUM( xnum * xprice ) it's give me all rows

当我使用SUM(xnum * xprice)AS总计它只给我一行,但当我删除这个SUM(xnum * xprice)它给我所有的行

My question is how can i get sum ? while i need all rows from query ?!

我的问题是如何得到总和?虽然我需要查询所有行?!

2 个解决方案

#1

Sum is a function where the sum of your value (xnum * xprice) over all selected rows is calculated. The result is one row.

Sum是一个函数,其中计算所有选定行的值(xnum * xprice)之和。结果是一行。

If you want to calculate the sum of multiple groups of rows, you may use the GROUP BY clause, which groups columns to groups (e.g. GROUP BY userid instead of your where clause would calculate SUM(xnum * xprice) for all users)

如果要计算多组行的总和,可以使用GROUP BY子句,该子句将列分组到组(例如GROUP BY userid而不是where子句将为所有用户计算SUM(xnum * xprice))

#2

The SUM() function is an aggregate operator. The select it is associated with can no longer return individual rows, only information about groups of rows.

SUM()函数是一个聚合运算符。与之关联的选择不能再返回单个行,只能返回有关行组的信息。

But you could return the sum in a subquery. Here's an example, assuming the xprice column is in the xxsubproduct table:

但是你可以在子查询中返回总和。这是一个例子,假设xprice列在xxsubproduct表中:

SELECT *, 
    xnum * xprice AS amount, 
    (   select sum(xnum * xprice)
        FROM xxbasket
        LEFT JOIN xxsubproduct USING xsubproduct_id
        WHERE xuserid = '3'
    ) as total
FROM xxbasket
LEFT JOIN xxsubproduct USING xsubproduct_id
LEFT JOIN xxcolor USING xcolor_id
WHERE xuserid = '3'

#1