MySQL选择列，这是另一列中的值 [英] MySQL select column which is a value in another column

问题描述

我的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选择列，这是另一列中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！