我有这个疑问。
SELECT notes.id,enter.name as 'enter_name',step.title as 'flow status',notes.user_name as user_created,notes.created,notes.rel_client_id,td_doc_nr.value_string as 'document number',enter.enter_code,
IF(!ISNULL(td_doc_nr.value_string),
(SELECT GROUP_CONCAT(product_name SEPARATOR ',') from notes d
join note_bundles b on b.note_id = d.id
join note_products p on p.doc_bundle_id = b.id
join note_product_get_fields f on f.doc_product_id = p.id
join note_product_get_field_data fd on fd.get_field_id = f.id
where d.doc_nr = td_doc_nr.value_string
and value_string ='auto')
,NULL) as test
FROM notes notes
JOIN notes_steps step ON step.id = notes.step_id
JOIN notes_enters enter ON enter.id = notes.enter_id
LEFT JOIN notes_custom_fields tf_doc_nr ON tf_doc_nr.name = 'note_number' AND tf_doc_nr.rel_entity_id = enter.id
LEFT JOIN notes_custom_field_data td_doc_nr ON td_doc_nr.rel_entity_id = notes.id AND
td_doc_nr.field_instance_id = tf_doc_nr.id
WHERE notes.enter_id in (777) AND notes.status = 1
我加入这个子查询到“if语句”
SELECT GROUP_CONCAT(product_name SEPARATOR ',') from nontes d
join note_bundles b on b.note_id = d.id
join note_products p on p.doc_bundle_id = b.id
join note_product_get_fields f on f.doc_product_id = p.id
join note_product_get_field_data fd on fd.get_field_id = f.id
where d.doc_nr = 'G7777777'
and value_string ='auto'
在此之后我添加了一个新列。
SELECT GROUP_CONCAT(product_name SEPARATOR ','),GROUP_CONCAT(DISTINCT b.msisdn SEPARATOR ',') from notes d
join note_bundles b on b.note_id = d.id
join note_products p on p.doc_bundle_id = b.id
join note_product_get_fields f on f.doc_product_id = p.id
join note_product_get_field_data fd on fd.get_field_id = f.id
where d.doc_nr = 'G7777777'
and value_string ='auto'
它返回两列。 我怎样才能返回两列？这可能吗？ ：） 谢谢
一个IF语句中的子查询不能返回多个列。 您将需要单独加入到子查询的结果，并拉出两个单独的列：
SELECT ...
IF(!ISNULL(td_doc_nr.value_string), sub.one, NULL) as one,
IF(!ISNULL(td_doc_nr.value_string), sub.two, NULL) as two
FROM ...
LEFT JOIN (
SELECT d.doc_nr, GROUP_CONCAT(product_name SEPARATOR ','),GROUP_CONCAT(DISTINCT b.msisdn SEPARATOR ',') from documents d
join document_bundles b on b.document_id = d.id
join document_products p on p.doc_bundle_id = b.id
join document_product_cstm_fields f on f.doc_product_id = p.id
join document_product_cstm_field_data fd on fd.cstm_field_id = f.id
where value_string ='auto'
group by d.doc_nr
) sub on sub.doc_nr = td_doc_nr.value_string
IF语句内相关子查询只能返回1列和1列，这就是为什么你所得到的错误。 不过，看在你的查询的唯一外部引用里面的子查询
d.doc_nr = td_doc_nr.value_string
所以你并不需要真的需要一个相关子查询，您可以通过子查询移动到join和子查询内doc_nr分组达到同样的效果，这将可能会更有效，它可以让你回到了2列你要：
SELECT tickets.id,
source.name as 'source_name',
flow_stage.title as 'flow status',
tickets.user_name as user_created,
tickets.created,
tickets.rel_client_id,
td_doc_nr.value_string as 'document number',
source.source_code,
IF(!ISNULL(td_doc_nr.value_string), ProductNames, NULL) as test,
d.MSISDNS
FROM tickets tickets
JOIN tickets_flow_stages flow_stage
ON flow_stage.id = tickets.flow_stage_id
JOIN tickets_sources source
ON source.id = tickets.source_id
LEFT JOIN tickets_custom_fields tf_doc_nr
ON tf_doc_nr.name = 'document_number'
AND tf_doc_nr.rel_entity_id = source.id
LEFT JOIN tickets_custom_field_data td_doc_nr
ON td_doc_nr.rel_entity_id = tickets.id
AND td_doc_nr.field_instance_id = tf_doc_nr.id
LEFT JOIN
( SELECT d.Doc_nr,
GROUP_CONCAT(product_name SEPARATOR ',') AS ProductNames,
GROUP_CONCAT(DISTINCT b.msisdn SEPARATOR ',') AS MSISDNS
from documents d
INNER JOIN document_bundles b
ON b.document_id = d.id
INNER JOIN document_products p
ON p.doc_bundle_id = b.id
INNER JOIN document_product_cstm_fields f
ON f.doc_product_id = p.id
INNER JOIN document_product_cstm_field_data fd
ON fd.cstm_field_id = f.id
WHERE value_string ='auto'
GROUP BY d.Doc_nr
) d
ON d.doc_nr = td_doc_nr.value_string
WHERE tickets.source_id IN (114,122,125,129,131)
AND tickets.status = 1
