作者热门文章
- android - RelativeLayout 背景可绘制重叠内容
- android - 如何链接 cpufeatures lib 以获取 native android 库?
- java - OnItemClickListener 不起作用,但 OnLongItemClickListener 在自定义 ListView 中起作用
- java - Android 文件转字符串
如何从 MySQL中选择sum(max remain(x + y + z)),来捕获
sum(1200+1300+1400)
?
id | user | remain
-----------------
1 | x | 1000
----------------
2 | x | 1200
----------------
3 | y | 1100
----------------
4 | y | 1300
----------------
5 | z | 1200
----------------
5 | z | 1400
----------------
using (SqlConnection cn = new SqlConnection(Class1.x))
{
cn.Open();
string cm1 = "select sum(max remain(all users)) as 'total' from item_new_company";
using (SqlCommand cmd = new SqlCommand(cm1, cn))
{
using (SqlDataReader dr = cmd.ExecuteReader())
{ dr.Read(); tot5 = dr["total"].ToString(); }
}
}
最佳答案
像这样:
SELECT SUM(MaxRemain) TotalOfMaxRemains
FROM
(
SELECT MAX(remain) AS MaxRemain
FROM item_new_company
GROUP BY user
) AS t;
SQL Fiddle Demo
结果:
| TOTALOFMAXREMAINS |
---------------------
| 3900 |
子查询:
SELECT MAX(remain) AS MaxRemain
FROM item_new_company
GROUP BY user
使用 GROUP BY user
和 MAX(remain)
,将为每个 user
提供 remain
的最大值>,然后在外部查询中,SUM
将为您提供总数。
更新
对于 SQL Server,前面的查询应该可以正常工作,但还有另一种方法:
WITH CTE AS
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY [user]
ORDER BY id DESC) AS rownum
FROM item_new_company
)
SELECT SUM(remain) AS Total
FROM CTE
WHERE rownum = 1;
SQL Fiddle Demo
关于c# - 如何从 MySQL 中选择 sum(max remain(x+y+z)) 来捕获 sum(1200+1300+1400),我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/15319850/
我是一名优秀的程序员,十分优秀!