mysql 返回所有行,MySQL返回多行

I have this query.

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

I added this subquery to the 'if statement'

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'

After this I added a new column.

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'

It returns two columns.

How can I return two columns?Is it possible? :) Thanks

解决方案

A subquery inside an IF statement can't return multiple columns. You will need to join the subquery into the results, and pull out the two separate columns individually:

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