主页 / dba / 问题 / 35784
Accepted
Mannoj
Asked: 2013-03-02 23:37:20 +0800 CST

需要逻辑来整理数据

  • 772

我在下面的结构中有一个表。

phone bigint
answer_time datetime

我应该将所有电话号码及其总尝试次数归为一组，并提及其未接听时间和未接听时间。为此，条件是 answer_time='0000-00-00 00:00:00' 表示未回答， answer_time > '0000-00-00 00:00:00' 表示已回答。可以多次拨打特定号码。任何建议或方法都会有所帮助。我非常坚持这一点。

结果应该是：

+----------------+---------------+--------------------+--
| phone         | count(*) | NOT_ANSWERED | ANSWERED   |
+---------------+----------+------------+------------+-----+
| 7260513       |      2   |       1      |  1       |
+------------+----------+------------+--------------------+

在这种情况下，在第一次尝试时，呼叫在第二次尝试时被应答，但它没有。尝试范围最多可达 16 次。

对于最大

mysql> select phone, answer_time from cdr_test;
+------------+---------------------+
| phone   | answer_time         |
+------------+---------------------+
| 31389966 | 0000-00-00 00:00:00 |
| 31389966 | 0000-00-00 00:00:00 |
| 31389966 | 0000-00-00 00:00:00 |
| 31389966 | 0000-00-00 00:00:00 |
| 31389966 | 0000-00-00 00:00:00 |
| 31389966 | 0000-00-00 00:00:00 |
| 31389966 | 0000-00-00 00:00:00 |
| 31389966 | 0000-00-00 00:00:00 |
| 31389966 | 0000-00-00 00:00:00 |
| 31389966 | 0000-00-00 00:00:00 |
| 31389966 | 0000-00-00 00:00:00 |
| 31389966 | 0000-00-00 00:00:00 |
| 31389966 | 0000-00-00 00:00:00 |
+------------+---------------------+
13 rows in set (0.00 sec)

mysql> select phone, Count(*), Count(CASE WHEN Time(answer_time) = 0 THEN 1 end ) AS  NOT_ANSWERED, Count(CASE WHEN Time(answer_time) > 0 THEN 1 end) AS ANSWERED  FROM   cdr_test  GROUP  BY phone;
+------------+----------+--------------+----------+
| phone      | Count(*) | NOT_ANSWERED | ANSWERED |
+------------+----------+--------------+----------+
| 31389966 |       13 |            0 |        0 |
+------------+----------+--------------+----------+

1 row in set (0.00 sec)
mysql

2 个回答

  1. Best Answer

    我不建议将 Datetime 类型用于您的 answer_time，将其更改为 TIME 类型，因为 000-00-00 将因所有日期时间功能而失败（请参阅 MySQL 文档：链接）。

    所以，当你的桌子看起来像：

    [09:47:55] [DEV\MFO] mysql> desc MFO.phone_call;
+-------------+------------+------+-----+---------+-------+
| Field       | Type       | Null | Key | Default | Extra |
+-------------+------------+------+-----+---------+-------+
| phone       | bigint(20) | YES  |     | NULL    |       |
| answer_time | datetime   | YES  |     | NULL    |       |
+-------------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

    你可以试试：

    SELECT phone, 
   Count(*), 
   Count(CASE 
           WHEN Time(answer_time) = 0 THEN 1 
         end) AS NOT_ANSWERED, 
   Count(CASE 
           WHEN Time(answer_time) > 0 THEN 1 
         end) AS ANSWERED 
FROM   MFO.phone_call 
GROUP  BY phone; 

+-------+----------+--------------+----------+
| phone | count(*) | NOT_ANSWERED | ANSWERED |
+-------+----------+--------------+----------+
| 11111 |       21 |           10 |       11 |
| 22222 |        2 |            0 |        2 |
+-------+----------+--------------+----------+
2 rows in set (0.00 sec)

    最大限度。

    • 2

  2. 这需要 Max 方法的附加组件。

    select phone, count(*),  count(CASE WHEN if(answer_time='0000-00-00 00:00:00',0,1) = 0  then 1 end ) AS NOT_ANSWERED , count(CASE WHEN if(answer_time!='0000-00-00 00:00:00',0,1) = 0  then 1 end ) AS ANSWERED FROM cdr_data group by phone;  

+------------+----------+-------------+----------+
| phone      | count(*) | NOT_ANSWERED| ANSWERED |
+------------+----------+-------------+----------+
| 31389966   |       13 |    13       |       0 |
+------------+----------+-------------+---------+
    • 0

相关问题