php - 在列值上使用 Mysql JOIN 作为列名与另一个表列名

Question

我想将一个表的列值用作列名，并将其用作与另一个表列名的 JOIN ON 值。以下是我的查询。

SELECT nid, sid, 
       MAX(CASE WHEN cid = 2 THEN data END) first_name,
       MAX(CASE WHEN cid = 3 THEN data END) last_name,
       MAX(CASE WHEN cid = 4 THEN data END) email,
       MAX(CASE WHEN cid = 5 THEN data END) phone_number,
       MAX(CASE WHEN cid = 6 THEN data END) transitions,
       MAX(CASE WHEN cid = 7 THEN data END) **upload**,
       MAX(CASE WHEN cid = 9 THEN data END) coupon_code
  FROM wsd AS wsd JOIN files AS fm ON **upload** = fm.fid WHERE nid IN(66,67) GROUP BY sid

正如您看到上面的代码，我想使用上传列作为另一个表上的 JOIN。另外，是否可以在结果中嵌入 HTML 标签？

Answer 1

我尝试使用临时表获取结果。这是我的答案。

CREATE TEMPORARY TABLE IF NOT EXISTS my_temp_table SELECT sid, cid, data FROM wsd WHERE cid = 7 AND nid IN (66,67);
SELECT wsd.nid, wsd.sid, fm.filename, 
       MAX(CASE WHEN wsd.cid = 2 THEN wsd.data END) fn,
       MAX(CASE WHEN wsd.cid = 3 THEN wsd.data END) ln,
       MAX(CASE WHEN wsd.cid = 4 THEN wsd.data END) email,
       MAX(CASE WHEN wsd.cid = 5 THEN wsd.data END) ph,
       MAX(CASE WHEN wsd.cid = 6 THEN wsd.data END) tr,
       MAX(CASE WHEN wsd.cid = 7 THEN wsd.data END) upr,
       MAX(CASE WHEN wsd.cid = 9 THEN wsd.data END) coc
  FROM wsd wsd JOIN my_temp_table ON wsd.sid = my_temp_table.sid JOIN files fm ON my_temp_table.data = fm.fid WHERE nid IN(66,67) GROUP BY wsd.sid;

代替临时表，更好的查询如下:

SELECT wsd.nid, wsd.sid, fm.filename, 
           MAX(CASE WHEN wsd.cid = 2 THEN wsd.data END) fn,
           MAX(CASE WHEN wsd.cid = 3 THEN wsd.data END) ln,
           MAX(CASE WHEN wsd.cid = 4 THEN wsd.data END) email,
           MAX(CASE WHEN wsd.cid = 5 THEN wsd.data END) ph,
           MAX(CASE WHEN wsd.cid = 6 THEN wsd.data END) tr,
           MAX(CASE WHEN wsd.cid = 7 THEN wsd.data END) upr,
           MAX(CASE WHEN wsd.cid = 9 THEN wsd.data END) coc
      FROM wsd wsd LEFT JOIN files fm ON wsd.data = fm.fid WHERE nid IN(66,67) GROUP BY wsd.sid;