MySQL单条语句合并两个表 [英] MySQL single statement to merge two tables

问题描述

我确定已经有人问过/回答过这个问题，但是我不知道这种动作的调用方式，而且我的SQL知识也很有限.

I'm sure this has been ask/answered already but I don't know how this kind of action is called and my SQL knowledge is limited.

我正在搜索单个SQL语句以合并两个表:

I'm searching for a single SQL statement to merge two tables:

表USER:

ID   hash
=========
1    abc
2    def
3    ghi

和USER_FIELD表:

and the USER_FIELD table:

ID  user_id key        value
=============================
1   1       firstname  John
2   1       lastname   Doe
3   2       firstname  Justin
4   2       lastname   Case

现在我想知道如何获得这种视图:

Now I would like to know how I get this view:

ID  hash  firstname   lastname
================================
1   abc   John        Doe

因此，如果我使用键"email"在USER_FIELD表中添加其他条目，则会在最后一个结果中得到一个新列

So if I add an addtional entry in the USER_FIELD table with a key "email" I get a new column in the last result

这是否仅在MySQL中还是可能的?我是否必须去修改PHP中的结果?

Is this even possible just in MySQL or do I have to go and alter the result in PHP?

这甚至是一个好的数据库设计，还是我应该放弃它(使用另一个?)

Is this even a good DB design or should I drop this an go with a different (which one?)

推荐答案

联接夫妇可以解决问题.

Couple of Joins would do the trick.

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'

这篇关于MySQL单条语句合并两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！