我有一个名为 [client] 的表,其列和值如下所示:
+--------+-----------+--------------+----------------+
| uid | client_id | client_input | input_value |
+--------+-----------+--------------+----------------+
| 1 | 22 | City | Seattle |
+--------+-----------+--------------+----------------+
| 2 | 45 | City | Ojai |
+--------+-----------+--------------+----------------+
| 3 | 22 | State | OR |
+--------+-----------+--------------+----------------+
| 4 | 45 | State | CA |
+--------+-----------+--------------+----------------+
| 5 | 65 | City | Orlando |
+--------+-----------+--------------+----------------+
| 6 | 74 | State | AB |
+--------+-----------+--------------+----------------+
| 7 | 65 | State | FL |
+--------+-----------+--------------+----------------+
| 8 | 12 | City | Los Angeles |
+--------+-----------+--------------+----------------+
| 9 | 12 | State | CA |
我需要 PHP/MySql 查询,它将列出input_value列中位于"CA"状态的所有城市。显然,通用标识符是始终匹配的client_id,因为如果客户端位于"洛杉矶",则相同的client_id也在包含input_value列中"CA"值的行中。此表有超过 800 万行,任何快速执行的 equerry 都受到高度赞赏。
我的脑子炸了,截止日期快到了,无法改变桌子设计,需要帮助,拜托!
SELECT *
FROM table AS t1 INNER JOIN table AS t2 ON t1.client_id = t2.client_id
WHERE t1.client_input = 'City' AND t2.client_input = 'State' AND t2.input_value = 'CA'
注意:这可能需要一些调整,并且可能会进行优化。但它应该让你开始并冷却你的大脑。
我没有测试这个查询。但是这个或类似的查询应该有效。我首先使用了两个子查询来避免不必要的连接。第一个子查询筛选状态"CA",第二个子查询筛选城市。如果要按州列出所有城市的表,请从子查询 a 中删除"CA"子句。
SELECT b.input_value AS city, a.input_value AS state
FROM
(SELECT client_id, input_value
FROM tablename
WHERE input_value = 'CA'
AND client_input = 'State')
AS a,
(SELECT client_id, input_value
FROM tablename
WHERE client_input = 'City')
AS b
WHERE a.client_id = b.client_id
SELECT distinct
t2.Input_Value as City
from
YourTable t1
JOIN YourTable t2
on t1.client_ID = t2.Client_ID
AND t2.client_input = 'City'
where
t1.client_input = 'State'
AND t1.Input_Value = 'CA'
我会有两个索引...(Client_Input,Input_Value)和(Client_ID、Client_Input、Input_Value)