php - mySql - 从表中获取相关值

Question

好吧，我会尽量说清楚，但请耐心等待——这是漫长的一周:)

我们有一个结构如下的表:

+----+----------+---------+------------------+
| id | field_id | user_id |      value       |
+----+----------+---------+------------------+
|  1 | Country  |       2 | USA              |
|  2 | Country  |       3 | USA              |
|  3 | Country  |       4 | CA               |
|  4 | Country  |       5 | MX               |
|  5 | Province |       2 | FL               |
|  6 | Province |       3 | GA               |
|  7 | Province |       4 | British Columbia |
|  8 | Province |       5 | Sonara           |
|  9 | City     |       2 | Orlando          |
| 10 | City     |       3 | Brunswick        |
| 11 | City     |       4 | Vancouver        |
| 12 | City     |       5 | Nogalas          |
+----+----------+---------+------------------+`

我们需要(希望)一个查询来返回所有国家 - 州/省 - 城市组合，以便动态生成 JSON 文件。

效果是

"SELECT all Provinces and Cities WHERE Country = 'USA'"

(但当然，我们的数据库表的结构——不幸的是不能改变——它要复杂得多。

任何共享一个公共(public)“user_id”的值都可以安全地假设为“一起”(即 user_id 2 的“国家”为“USA”，“省”为“FL”，“省”为“Orlando” “城市”)。

我们试图创建一个类似于下面的 json 文件的最终结果。

{
"USA": {
    "Florida": [
        {"City": "Orlando"},
        {"City": "Palm Beach"}
    ],
    "Georgia": [
        {"City": "Atlanta"},
        {"City": "Brunswick"}
    ]
}, 
"Canada": {
    "Alberta": [
        {"City": "Calgary"}
    ],
    "Ontario": [
        {"City": "Atlanta"},
        {"City": "Brunswick"}
    ]
}
}

Answer 1

有了这个查询和一些循环的结果，你应该能够根据需要生成你的 Json

SQL Fiddle

MySQL 5.6 架构设置:

CREATE TABLE t
    (`id` int, `field_id` varchar(8), `user_id` int, `value` varchar(16))
;

INSERT INTO t
    (`id`, `field_id`, `user_id`, `value`)
VALUES
    (1, 'Country', 2, 'USA'),
    (2, 'Country', 3, 'USA'),
    (3, 'Country', 4, 'CA'),
    (4, 'Country', 5, 'MX'),
    (5, 'Province', 2, 'FL'),
    (6, 'Province', 3, 'GA'),
    (7, 'Province', 4, 'British Columbia'),
    (8, 'Province', 5, 'Sonara'),
    (9, 'City', 2, 'Orlando'),
    (10, 'City', 3, 'Brunswick'),
    (11, 'City', 4, 'Vancouver'),
    (12, 'City', 5, 'Nogalas')
;

查询 1:

SELECT 
    tc.`value` as Country,  
    tp.`value` as Province,  
    tcy.`value` as City
FROM (
        SELECT `value`, user_id FROM t WHERE field_id = "Country"
    ) as tc
    LEFT JOIN (
            SELECT `value`, user_id FROM t WHERE field_id = "Province"
        ) as tp
        ON tc.user_id = tp.user_id
    LEFT JOIN (
            SELECT `value`, user_id FROM t WHERE field_id = "City"
        ) as tcy
        ON tp.user_id = tcy.user_id

Results :

|Country|         Province |      City |
|-------|------------------|-----------|
|   USA |               FL |   Orlando |
|   USA |               GA | Brunswick |
|    CA | British Columbia | Vancouver |
|    MX |           Sonara |   Nogalas |