几个Joins可以解决问题。
SELECT u.id, u.hash, uf_f.value AS firstname, uf_l.value AS lastname
FROM user AS u
LEFT JOIN user_field AS uf_f ON uf_f.user_id = u.id AND uf_f.key = 'firstname'
LEFT JOIN user_field AS uf_l ON uf_l.user_id = u.id AND uf_l.key = 'lastname'