准备好的语句:如何加入标题 [英] Prepared Statement: How to join header

问题描述

我有2个表，我想将它们连接到已准备好的语句中的标题和第一列.我已经设法加入专栏，但是如何加入标题呢?

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?

表1:

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

表2:

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

结果矩阵:

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

SQL提琴

推荐答案

为了从ID中获取团队名称，您将必须在表上加入两次.

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;

请参见带演示的SQL提琴.

然后，如果您使用准备好的语句来动态创建此语句，则代码将为:

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;

请参见带有演示的SQL提琴

结果是:

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

这篇关于准备好的语句:如何加入标题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！