这是另一列中的值

这是另一列中的值

本文介绍了MySQL选择列,这是另一列中的值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我的sql表是这样的:

my sql table is something like this:

+----+------------+--------+---------------+---------------+-------------+----------+-------------+---------------------+
| id | msisdn     | status | reason        | CallRejection | Unavailable | NoAnswer | NetworkBusy | autotimestamp       |
+----+------------+--------+---------------+---------------+-------------+----------+-------------+---------------------+
|  1 | 9999999901 | Failed | NoAnswer      |             1 |           2 |        2 |           4 | 2013-12-19 15:46:50 |
|  2 | 9999999902 | Failed | NetworkBusy   |             4 |           2 |        3 |           2 | 2013-12-19 13:07:42 |
|  3 | 9999999903 | Failed | CallRejection |             6 |           6 |        6 |           5 | 2013-12-19 13:07:53 |
|  4 | 9999999904 | Failed | Unavailable   |             2 |           4 |        2 |           0 | 2013-12-19 13:08:03 |
|  5 | 9999999905 | Failed | Misc          |             6 |           2 |        6 |           2 | 2013-12-19 13:07:01 |
|  6 | 9999999906 | Failed | NoAnswer      |             1 |           3 |        1 |           6 | 2013-12-19 13:07:24 |
|  7 | 9999999907 | Failed | NetworkBusy   |             6 |           6 |        2 |           3 | 2013-12-19 13:07:42 |
|  8 | 9999999908 | Failed | CallRejection |             8 |           3 |        3 |           0 | 2013-12-19 13:07:53 |
|  9 | 9999999909 | Failed | Unavailable   |             3 |           8 |        5 |           6 | 2013-12-19 13:08:03 |
| 10 | 9999999910 | Failed | Misc          |             8 |           4 |        0 |           4 | 2013-12-19 13:07:01 |
+----+------------+--------+---------------+---------------+-------------+----------+-------------+---------------------+


$ b b

我想根据每行的 reason 列的值提取每个原因的值。例如:

I want to extract the value of each reason based on the value of reason column for each row. eg:

第1行应将 2 结果,因为 reason 列具有 noanswer ,所以该特定列的值 / code>应该被返回。

row 1 should give 2 as a result because the reason column has noanswer, so the value of that particular column i.e. noanswer should be returned.

实际上,在比较返回的值和常量之后,我必须选择多行。我可以通过以下方法执行此操作:

Actually, I have to select multiple rows after comparing the returned valued with a constant. I'm able to do this by the following method :

select @col := reason from msisdn_table where id=1;
SET @a = concat('select ', @col, ' from msisdn_table where id=1');
PREPARE STMT FROM @a;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;

但这不是很有前途的解决方案,因为它只有一行,一个for循环,以获得组块的1000行,这将不是很有效率。更有效的方法是什么?

But this is not very promising solution because it is for only one row and i'll have to use a for loop in order to get chunk's of 1000 rows, and that would not be very efficient. What is the more efficient way?

推荐答案

尝试:

SELECT id,
       reason
       CASE
         WHEN reason = 'NoAnswer' THEN NoAnswer
         WHEN reason = 'NetworkBusy' THEN NetworkBusy
       END AS value
FROM table1

它应该给出这样的输出:

It should give you an output like this:

+----+------------+----------+
| id | reason     | value    |
+----+------------+----------+
|  1 | NoAnswer   |        2 |
+----+------------+----------+

这篇关于MySQL选择列,这是另一列中的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-18 18:49