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