解决方案 »
- 关于partition的问题
- 菜鸟求救 字段名可用"time"么? 不会和mysql内置函数冲突之类的吧
- mysqlql 数据库导出,myslqdump 语句导出的数据库文件放那里去了??
- mysql删除重复记录问题
- MYSQL数据库乱码问题(在线等)
- 求一个SQL触发器的语句
- 请问我下载了个MySQL Connector/ODBC 3.51,要想使用它,是不是还要有个DLL驱动啊?
- MysqlCC不能显示中文的问题。
- mysql关于临时表的联查问题
- 为什么我的mysql msi安装到最后两步的时候就停止了 我之前安装了了一个解压版的mysql没有卸载但是停掉服务了
- 求关于运算符优先级的解释
- mysql树形结构的级联删除
from tb A
where exists (select 1 from tb B where A.address=B.address and A.value<B.value)
1 5
1 4
1 9
2 2
2 6
2 10
2 12
3 1
3 9select address,sum(value)
from tT A
where exists (select 1 from tT B where A.address=address and A.value>value)
GROUP BY address
from tb A
where exists(select 1 from tb B where A.address=B.address and A.value>B.VALUE)
group by address;
from table
group by address
先select max(value), count(*) from tableA group by address;
得到每个地址的最大值
然后遍历求和。能用一个语句实现么?
tT as A
或者
tT as B
为表名取别名,以示区分
列名也可以这样去别名
FROM (SELECT *
FROM t
WHERE (address,value_t) NOT IN (select address,max(value_t) AS value_t FROM t GROUP BY address)) AS t2
GROUP BY address############################################################
#最里面的select语句:select address,max(value_t) AS value_t FROM t GROUP BY address 是用来把分组以后每一组最大的值找出来
#然后用NOT IN 表示需要的数据不在这个里面
#再用SELECT * FROM t WHERE (address,value_t) NOT IN (select address,max(value_t) AS value_t FROM t GROUP BY address)) 语句把不包含最大值的分组全部列出来,生成一个临时表,把表的别名为t2
#最后就是求和了
###############################################################
SELECT address,SUM(value_t)
FROM (SELECT *
FROM t
WHERE (address,value_t) NOT IN (select address,max(value_t) AS value_t FROM t GROUP BY address)) AS t2