php - MySQL:我需要根据相同的条件将多行中的相同列名拉到单个查询中

Question

我想将以下查询组合成一个查询:

SELECT val FROM resource WHERE facet="all" and urlId="1234"
SELECT val FROM resource WHERE facet="your" and urlId="1234"
SELECT val FROM resource WHERE facet="base" and urlId="1234"

哦——每个 urlId 都有多个“方面”。但每个“方面”只有一行。这个表是别人设置的。我们正在迁徙。显然，每个“方面”都有一列和一行会更好，但为时已晚。

我可以用多个查询来做到这一点，但我想要一个更快/更好的解决方案。

<pre>
mysql> describe resource;
+-------------+-------------------------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                                | Null | Key | Default           | Extra                       |
+-------------+-------------------------------------+------+-----+-------------------+-----------------------------+
| resId       | int(11) unsigned                    | NO   | PRI | NULL              | auto_increment              |
| urlId       | int(11)                             | NO   | MUL | NULL              |                             |
| sectionId   | int(3)                              | YES  |     | NULL              |                             |
| mode        | enum('archive','live','edit','dev') | NO   |     | edit              |                             |
| facet       | varchar(50)                         | NO   | MUL | NULL              |                             |
| typeId      | int(1)                              | NO   |     | 1                 |                             |
| val         | mediumtext                          | NO   |     | NULL              |                             |
| itemOrder   | tinyint(2)                          | NO   |     | 1                 |                             |
| editorId    | varchar(30)                         | NO   |     | NULL              |                             |
| created     | datetime                            | NO   |     | NULL              |                             |
| lastUpdated | timestamp                           | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+-------------------------------------+------+-----+-------------------+-----------------------------+
11 rows in set (0.05 sec)
</pre>

记住:在苏联，SQL 查询你。

Answer 1

这是我的首选方法，它将使用您的索引:

SELECT resource.val FROM
  ( (SELECT "all" AS name) UNION (SELECT "your") UNION (SELECT "base") ) AS facets
  LEFT JOIN resource ON( resource.facet  = facets.name AND resource.urlId = "1234" )