一千萬個為什麽

搜索

Mysql選擇動態列(3個表)


我正在尋找SQL來做:

關於字段列表的事情必須是完全動態的。每次管理員在字段表中添加字段時,我都不想更改我的SQL。

Table content
    -------------
    CID
    text
    category

    CID | text | category
    ----------------------
    1   | ...  | apple
    2   | ...  | apple
    3   | ...  | apple

    Table fields
    -------------
    FID
    typename

    FID | typename
    ----------------
    1   | field1
    2   | field2
    3   | field3

    Table fields_value
    -------------
    CID
    FID
    value

    CID | FID | value
    -------------------
    1   | 1   | value1
    1   | 2   | value2
    1   | 3   | value3
    2   | 1   | value4
    2   | 2   | value5
    2   | 3   | value6
    3   | 1   | value7
    3   | 2   | value8
    3   | 3   | value9

    #####
    ---> ???? SELECT content.*,...dynamic field.* FROM content WHERE category = "apple" LIMIT 20

    RESULT I AM LOOKING FOR:
    -------------------
    CID | text | category | field1 | field2 | field3 
    -------------------------------------------------
    1   | ...  | apple    | value1 | value2 | value3
    2   | ...  | apple    | value4 | value5 | value6
    3   | ...  | apple    | value7 | value8 | value9

最佳答案

沒有辦法用mysql做你想要的(有動態命名的列)。你最接近的是

SELECT CID, text, category, typename, value
  FROM content JOIN fields_value JOIN fields
  WHERE content.CID = fields_value.CID AND fields_value.FID = fields.FID

哪個會給你

CID | text | category | typename | value
 1  | ...  | apple    | field1   | value1
 1  | ...  | apple    | field2   | value2
 1  | ...  | apple    | field3   | value3
 2  | ...  | apple    | field1   | value4
 2  | ...  | apple    | field2   | value5
 2  | ...  | apple    | field3   | value6
 3  | ...  | apple    | field1   | value7
 3  | ...  | apple    | field2   | value8
 3  | ...  | apple    | field3   | value9

當然,您可以通過加載 fields 表並使用您想要的字段構建 CREATE TABLE 命令來動態創建表,然後從 fields_value <�填充它/ code> table,但這可能不是你想要的。

轉載註明原文: Mysql選擇動態列(3個表)

猜你喜歡