mysql 2个交叉表视图,不是1=1关系到一个视图中

我有一个有四列的mysql表 subfield_id, submission_id, field_id, value

我提出了两个观点:

1。

SELECT submission_id, 
       MAX( CASE field_id WHEN '1' THEN value ELSE '0' END ) Usernumber,
       MAX( CASE field_id WHEN '2' THEN value ELSE '0' END ) Company, 
       MAX( CASE field_id WHEN '3' THEN value ELSE '0' END ) Zipp, 
FROM table
GROUP BY submission_id

结果表列: Submission_id, Usernumber, Company, Zipp

2。

SELECT submission_id, 
       MAX( CASE field_id WHEN '4' THEN value ELSE '0' END ) Usernumber1, 
       MAX( CASE field_id WHEN '5' THEN value ELSE '0' END ) Partnernumber 
FROM table 
GROUP BY submission_id

结果表列: Submission_id, Usernumber1, Partnernumber

Usernumber 和 Usernumber1 是同一个号码吗

如何创建包含以下列的视图?

Usernumber, Company,  Zipp, Partnernumber