MySQL 在同一行中选择不同的值

Question

您好，我遇到了一个问题，因为我想从同一个表中选择 3 个不同的值并将它们显示在同一行的不同列中。我认为这是可以实现的，但我无法做到这一点。这是我要从中提取数据的表:

select * from mantis_custom_field_string_table limit 20 \G;                                                                   *************************** 1. row ***************************
field_id: 4
  bug_id: 1957
   value: COCO-AB-00132
    text: NULL
*************************** 2. row ***************************
field_id: 3
  bug_id: 1957
   value: COCO-AB-00132-100.220.181.65
    text: NULL
*************************** 3. row ***************************
field_id: 7
  bug_id: 1957
   value: Lore Ipsum Lalala.
    text: NULL

这是我尝试的第一种方法:

select summary,value from mantis_bug_table inner join mantis_custom_field_string_table on mantis_custom_field_string_table.bug_id=mantis_bug_table.id where (field_id=4 or field_id=3 or field_id=7) and id=1957;
+------------------------------------------+---------------------------------------------------------------------------------------------------------+
| summary                                  | value                                                                                                   |
+------------------------------------------+---------------------------------------------------------------------------------------------------------+
| This is the summary | COCO-AB-00132-100.220.181.65                                                                            |
| This is the summary | COCO-AB-00132                                                                                           |
| This is the summary | Lore Ipsum Lalala.     |
+------------------------------------------+-----------------------------------------------------------------------------------------

这就是我想要的:

+------------------------------------------+---------------------------------------------------------------------------------------------------------+
| summary                                  | ID |      COCO |  Lore                                                                            |             
+------------------------------------------+---------------------------------------------------------------------------------------------------------+
| This is the summary | COCO-AB-00132-100.220.181.65     | COCO-AB-00132    | Lore Ipsum Lalala                                                                  |

这也是我尝试过的，但没有成功

mysql> select summary,value as ID, value as COCO, value as Lore  from mantis_bug_table inner join mantis_custom_field_string_table on mantis_custom_field_string_table.bug_id=mantis_bug_table.id where (field_id=4 or field_id=3 or field_id=7) and id=1957 \G;
*************************** 1. row ***************************
summary: This is the summary
     ID: COCO-RT-00132-100.220.181.65
   COCO: COCO-RT-00132-100.220.181.65
   Lore: COCO-RT-00132-100.220.181.65
*************************** 2. row ***************************
summary: This is the summary
     ID: COCO-RT-00132
   COCO: COCO-RT-00132
   Lore: COCO-RT-00132
*************************** 3. row ***************************
summary: This is the summary
     ID: This is the summary.
   COCO: This is the summary.
   Lore: This is the summary.

我希望有人能帮助我!

Answer 1

如果您知道要搜索什么，则可以使用内部选择查询例如

SELECT
(
  SELECT value
    FROM mantis_custom_field_string_table
   WHERE
         "put your clause here"
   LIMIT 1
) AS summary,
(
  SELECT value
    FROM mantis_custom_field_string_table
   WHERE
         "put your clause here"
   LIMIT 1
) AS ID,
(
  SELECT value
    FROM mantis_custom_field_string_table
   WHERE
         "put your clause here"
   LIMIT 1
) AS COCO,
(
  SELECT value
    FROM mantis_custom_field_string_table
   WHERE
         "put your clause here"
   LIMIT 1
) AS LOREM

要知道的一件重要的事情是，您的每个内部选择查询都必须返回一个SINGLE ROW