mysql - MySQL数据透视查询中的where子句

桌子

+-----------------+------------+--------------+
| submit_time     | field_name | field_value  |
+-----------------+------------+--------------+
| 1410098760.2225 |  cap       |  40128       |
| 1410098760.2225 |  provincia |  Bologna     |
| 1410098760.2225 |  localita  |  Bologna     |
| 1410098760.2225 |  indirizzo |  Via roma    |
| 1410098760.2225 |  zodiaco   |  Pesci       |
| 1410098760.2225 |  anno      |  1964        |
| 1410098760.2225 |  mese      |  03          |
| 1410098760.2225 |  giorno    |  10          |
| 1410098760.2225 |  card      |  12          |
+-----------------+------------+--------------+

这是我的问题，

SELECT
  submit_time,
  Max(if(field_name = 'nome', field_value, NULL)) AS nome,
  Max(if(field_name = 'giorno', field_value, NULL)) AS giorno,
  Max(if(field_name = 'mese', field_value, NULL)) AS mese,
  Max(if(field_name = 'anno', field_value, NULL)) AS anno,
  Max(if(field_name = 'card', field_value, NULL)) AS card,
  Max(if(field_name = 'localita', field_value, NULL)) AS localita,
  Max(if(field_name = 'indirizzo', field_value, NULL)) AS indirizzo,
  Max(if(field_name = 'email', field_value, NULL)) AS email,
  Max(if(field_name = 'tel', field_value, NULL)) AS tel,
  Max(if(field_name = 'cellulare', field_value, NULL)) AS cellulare
FROM zhter9mu3cf7dbplugin_submits
GROUP BY submit_time

这很好，但是如果我使用where子句

where field_name = 'card' and field_value = 12

所有字段均为空。有解决这个问题的建议吗？

最佳答案

问题是，当使用WHERE子句筛选field_name = 'card' and field_value = 12时，返回一行。
基本上是用其他field_names删除行。通过对示例数据执行以下操作可以看到这一点：

select *
from submits
where field_name = 'card'
  and field_value = 12

见SQL Fiddle with Demo。这只返回您请求的field_name和field_value行。其他列中没有其他内容可放入，因此返回null。
如果要返回与筛选的submit_time和field_name相同的field_value，则可以使用：

SELECT
  submit_time,
  Max(if(field_name = 'nome', field_value, NULL)) AS nome,
  Max(if(field_name = 'giorno', field_value, NULL)) AS giorno,
  Max(if(field_name = 'mese', field_value, NULL)) AS mese,
  Max(if(field_name = 'anno', field_value, NULL)) AS anno,
  Max(if(field_name = 'card', field_value, NULL)) AS card,
  Max(if(field_name = 'localita', field_value, NULL)) AS localita,
  Max(if(field_name = 'indirizzo', field_value, NULL)) AS indirizzo,
  Max(if(field_name = 'email', field_value, NULL)) AS email,
  Max(if(field_name = 'tel', field_value, NULL)) AS tel,
  Max(if(field_name = 'cellulare', field_value, NULL)) AS cellulare
FROM submits s
WHERE EXISTS (SELECT 1
              FROM submits f
              WHERE f.field_name = 'card'
                AND f.field_value = '12'
                AND f.submit_time = s.submit_time)
GROUP BY submit_time;

见SQL Fiddle with Demo

关于mysql - MySQL数据透视查询中的where子句，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/26423353/