Prepared Statement: How to join header

问题:

I've got 2 tables where i want to join the header and the first column within an prepared statement. I've got managed to join the column, but how to join the header?

Table1 :

  ID |Name 
----------
  1  |  A  
  2  |  B  
  3  |  C  
  4  |  D  
  5  |  E  

Table2 :

TeamA|TeamB|Won|Lost
--------------------
  1  |  2  | 5 | 3
  1  |  3  | 2 | 4
  1  |  4  | 9 | 1
  2  |  5  | 5 | 5
  3  |  1  | 2 | 4

Result-Matrix:

     |  A | B |  C | D | E
----------------------------
  A  |  0 | 2 | -2 | 8 | 0
  B  |  0 | 0 |  0 | 0 | 0
  C  | -2 | 0 |  0 | 0 | 0

SQL Fiddle

回答1:

In order to get the team names from the ids, you will have to join on the table twice.

If you know all the values, the static version of the code is:

select a.name teamA,
  max(case when b.name = 'A' then won - lost else 0 end) as A,
  max(case when b.name = 'B' then won - lost else 0 end) as B,
  max(case when b.name = 'C' then won - lost else 0 end) as C,
  max(case when b.name = 'D' then won - lost else 0 end) as D,
  max(case when b.name = 'E' then won - lost else 0 end) as E
from yourtable t
left join teams a
  on t.teama = a.id
left join teams b
  on t.teamb = b.id
group by a.name;

See SQL Fiddle with Demo.

Then if you are using a prepared statement to create this dynamically, the code will be:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN TeamB = ''',
      TeamB,
      ''' THEN won - lost else 0 END) AS `',
      TeamB, '`'
    )
  ) INTO @sql
from
(
  select a.name TeamA,
    b.name TeamB,
    t.won,
    t.lost
  from yourtable t
  left join teams a
    on t.teama = a.id
  left join teams b
    on t.teamb = b.id
  order by teamb
) x;

SET @sql 
  = CONCAT('SELECT TeamA, ', @sql, ' 
           from
           (
             select a.name TeamA,
              b.name TeamB,
              t.won,
              t.lost
            from yourtable t
            left join teams a
              on t.teama = a.id
            left join teams b
              on t.teamb = b.id
           ) s
           group by TeamA');

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

See SQL Fiddle with Demo

The result is:

| TEAMA |  A | B | C | D | E |
------------------------------
|     A |  0 | 2 | 0 | 8 | 0 |
|     B |  0 | 0 | 0 | 0 | 0 |
|     C | -2 | 0 | 0 | 0 | 0 |