解决方案 »
- 共享 的数据库设计问题 MYSQL
- 更新innodb 类型的表 :出现Lock wait timeout exceeded; try restarting transaction
- mysql数据导入出现unknown command '\',怎么解决?
- mysql lock table的一些疑问
- windows程序报错想跟踪mysql执行情况(相当于mssql事件探查器功能).请教哪位会?
- 想知道一个可用的集群安装表
- mysql 每个连接的 最长时间是多少?
- 请教:mysql数据库问题?
- 有没有对pgsql的分布式数据库系统有了解的?
- mySQL无法启动服务,新手跪求大神指点
- 关于所谓的中点时间的计算 求助
- 将列值相同的放在一起显示
from tb
group by callingnumberselect callednumber,count(*),sum(lastime)
from tb
group by callednumber
mysql> SELECT number
, SUM(IF(number = callingnumber , lastime , 0) ) AS '主叫'
, SUM(IF(number = callednumber , lastime , 0) ) AS '被叫'
, SUM(IF(number = callingnumber , 1 , 0) ) AS '主叫次数'
, SUM(IF(number = callednumber , 1 , 0) ) AS '被叫次数'
FROM (
SELECT number,callingnumber,callednumber,lastime,IF(number = callingnumber , '1' , '2' ) AS flag FROM `test2`
) tbl
GROUP BY number ;
+-------------+------+------+----------+----------+
| number | 主叫 | 被叫 | 主叫次数 | 被叫次数 |
+-------------+------+------+----------+----------+
| 15625613212 | 10 | 89 | 1 | 1 |
| 15635645354 | 0 | 56 | 0 | 1 |
| 18325452455 | 45 | 0 | 1 | 0 |
+-------------+------+------+----------+----------+
mysql> SELECT number
, SUM(IF(number = callingnumber , lastime , 0) ) AS '主叫'
, SUM(IF(number = callednumber , lastime , 0) ) AS '被叫'
, SUM(IF(number = callingnumber , 1 , 0) ) AS '主叫次数'
, SUM(IF(number = callednumber , 1 , 0) ) AS '被叫次数'
FROM `test2`
GROUP BY number ;
+-------------+------+------+----------+----------+
| number | 主叫 | 被叫 | 主叫次数 | 被叫次数 |
+-------------+------+------+----------+----------+
| 15625613212 | 10 | 89 | 1 | 1 |
| 15635645354 | 0 | 56 | 0 | 1 |
| 18325452455 | 45 | 0 | 1 | 0 |
+-------------+------+------+----------+----------+
mysql> SELECT number
, SUM(lastime) AS '通话时间'
, SUM(IF(number = callingnumber , 1 , 0) ) + SUM(IF(number = callednumber , 1 , 0) ) AS '通话次数'
FROM `test2`
GROUP BY number ;
+-------------+----------+----------+
| number | 通话时间 | 通话次数 |
+-------------+----------+----------+
| 15625613212 | 99 | 2 |
| 15635645354 | 56 | 1 |
| 18325452455 | 45 | 1 |
+-------------+----------+----------+
select number,relateNumber,sum(times),sum(minutes) from
(select number as number ,callingnumber as relateNumber' ,count(*) as times,sum(lastime) as minutes
from tb group by callingnumber,number
union
select number as number ,callednumber as relateNumber ,count(*) as times,sum(lastime) as minutes
from tb group by callingnumber,number) a
group by number,relateNumber没测试。。提供个思路,看看是不是你要的那种效果?
from 比如这么一个表
group by number,if(number=callingnumber,callednumber,callingnumber)
from calltab
group by number,(CASE number WHEN callingnumber THEN callednumber WHEN callednumber THEN callingnumber END)