mysql - 使用Where 子句限制左连接上的行显示

Question

我有一个查询，首先从表案例中选择一行，然后从连接表付款中选择 5 行。选择结果显示 5 个结果。我想限制它在第一次选择时只显示一个结果，但仍然从内存中的连接表中选择了所有 5 个结果。

到目前为止我的查询如下:

SELECT c.*,  
DATE_FORMAT( ADDDATE(c.proposal_date, INTERVAL 30 DAY),'%Y-%m-%d') AS expiration_date_30,  
(SELECT name FROM members rp WHERE rp.id = c.resonsible_partner) as responsible_partner,            
FROM cases c 
LEFT JOIN payments AS inv ON inv.caseid = c.id 
WHERE c.id = $id

欢迎任何帮助!

本例中的示例输出应显示:

表格案例中应显示 c.*屏幕上:id, casename, proposal_date这就是应该控制必须显示的行数。

id  | casename | proposal_date |
------------------------------
300 | One      | 2016-05-05    |

表格付款应显示:payment_percentage_01 = 25, payment_percentage_02 = 30, payment_number_01 = 1, payment_number_02 = 2

id | caseid | payment_percentage | payment number |
---------------------------------------------------
1  | 300    |        25          |        1       |
2  | 300    |        30          |        2       |

Answer 1

这似乎是一个表枢轴问题，不确定您是否真的想要这个，但尝试一下，也许有用:

SELECT
    c.*,
    DATE_FORMAT( ADDDATE(c.proposal_date, INTERVAL 30 DAY),'%Y-%m-%d') AS expiration_date_30,
    -- (SELECT name FROM members rp WHERE rp.id = c.resonsible_partner) as responsible_partner,
    MAX(CASE WHEN p.id = 1 THEN p.payment_percentage END) AS payment_percentage_01,
    MAX(CASE WHEN p.id = 2 THEN p.payment_percentage END) AS payment_percentage_02,
    MAX(CASE WHEN p.id = 1 THEN p.`payment number` END) AS payment_number_01,
    MAX(CASE WHEN p.id = 2 THEN p.`payment number` END) AS payment_number_02
FROM cases c
LEFT JOIN payments p
ON c.id = p.caseid
GROUP BY c.id

SQLFiddle Demo

已编辑:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN p.id = ''',
      id,
      ''' THEN p.payment_percentage END) AS payment_percentage',
      id
    ), ',',
     CONCAT(
      'MAX(CASE WHEN p.id = ''',
      id,
      ''' THEN p.`payment number` END) AS payment_number',
      id
    )
  ) INTO @sql
FROM payments;
SET @sql = CONCAT('
    SELECT c.*,
    DATE_FORMAT( ADDDATE(c.proposal_date, INTERVAL 30 DAY),''%Y-%m-%d'') AS expiration_date_30, '
   , @sql
   , ' FROM cases c
       LEFT JOIN payments p
       ON c.id = p.caseid
       GROUP BY c.id;');

-- SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQLFiddle Demo