mysql - 子查询返回超过 1 行 - 多选 MySQL

Question

我的查询是

SELECT  (
    SELECT invoice_custom_fieldvalue FROM ip_invoice_custom WHERE invoice_id ='2474' AND invoice_custom_fieldid = '9'
) AS 'Claim Number',
(
    SELECT invoice_custom_fieldvalue FROM ip_invoice_custom WHERE invoice_id ='2474' AND invoice_custom_fieldid = '7'
) AS 'Client Name',
(
    SELECT invoice_custom_fieldvalue FROM ip_invoice_custom WHERE invoice_id ='2474' AND invoice_custom_fieldid = '8'
) AS 'Employer',
(
    SELECT invoice_item_subtotal FROM ip_invoice_amounts WHERE invoice_amount_id ='2474'
) AS 'Invoice Amount',
(
    SELECT invoice_item_tax_total FROM ip_invoice_amounts WHERE invoice_amount_id ='2474'
) AS 'Tax',
(
    SELECT invoice_item_subtotal FROM ip_invoice_amounts WHERE invoice_amount_id ='2474'
) AS 'Tax1',
(
    SELECT CONCAT('CAD') FROM ip_invoice_amounts WHERE invoice_amount_id ='2474'
) AS 'Currency',
(
    SELECT invoice_date_created FROM ip_invoices WHERE invoice_id ='2474'
) AS 'Date',
(
    SELECT client_name FROM ip_clients c INNER JOIN ip_invoices i ON c.client_id = i.client_id WHERE i.invoice_id ='2474'
) AS 'Requested by',
(
    SELECT item_description FROM ip_invoice_items p WHERE p.invoice_id
) AS 'Transaction Subtype';

这是没有最后一次选择的结果：

Claim Number    Client Name Employer    Invoice Amount  Tax  Tax1   Currency    Date    Requested by
12345           John Smith  NULL        3326.99         0.00 0.00   CAD 2019-12-04  ATTN: Joe Smith

ip_invoice_items 表如下所示：

item_id invoice_id item_description
1       2474       Some product
2       2474       Some other product
3       2474       Some other other product

期望的输出：

Claim Number    Client Name Employer    Invoice Amount  Tax  Tax1   Currency    Date    Requested by Item Description
12345           John Smith  NULL        3326.99         0.00 0.00   CAD 2019-12-04  ATTN: Joe Smith  Some product
12345           John Smith  NULL        3326.99         0.00 0.00   CAD 2019-12-04  ATTN: Joe Smith  Some other product
12345           John Smith  NULL        3326.99         0.00 0.00   CAD 2019-12-04  ATTN: Joe Smith  Some other other product

每个 invoice_id（最后一个选择语句）都有多个项目描述。我怎样才能重写它，以便我得到一个将为每个项目描述添加行的结果？

标签： mysqlselect

Answer 1

涉及 5 个表，您可以从其他 4 个开始ip_invoice_custom并LEFT加入：

SELECT ic.`Claim Number`, ic.`Client Name`, ic.`Employer`,
       ia.invoice_item_subtotal `Invoice Amount`,
       ia.invoice_item_tax_total `Tax`,
       ia.invoice_item_subtotal `Tax1`,
       'CAD' `Currency`,
       i.invoice_date_created `Date`,
       c.client_name `Requested by`,
       ii.item_description `Transaction Subtype`
FROM (
  SELECT invoice_id, 
         MAX(CASE WHEN invoice_custom_fieldid = '9' THEN invoice_custom_fieldvalue END) `Claim Number`,
         MAX(CASE WHEN invoice_custom_fieldid = '7' THEN invoice_custom_fieldvalue END) `Client Name`,
         MAX(CASE WHEN invoice_custom_fieldid = '8' THEN invoice_custom_fieldvalue END) `Employer`
  FROM ip_invoice_custom
  WHERE invoice_id ='2474' AND invoice_custom_fieldid IN ('7', '8', '9')
  GROUP BY invoice_id
) ic
LEFT JOIN ip_invoice_amounts ia ON ia.invoice_amount_id = ic.invoice_id 
LEFT JOIN ip_invoices i ON i.invoice_id = ic.invoice_id 
LEFT JOIN ip_clients c ON c.client_id = i.client_id
LEFT JOIN ip_invoice_items ii ON ii.invoice_id = ic.invoice_id