问题描述
晚上好，我是Web编程的新手，需要您的帮助来解决SQL查询所固有的问题.我使用的数据库引擎是MySQL，我通过PHP访问它，在这里我将解释数据库的简化版本，以解决问题.让我们假设使用包含三个表的数据库:teams，teams_information，attributes.更精确地:
Good evening guys,I'm a newbie to web programming and I need your help to solve a problem inherent to SQL query.The database engine I'm using is MySQL and I access it via PHP, here I'll explain a simplified version of my database, just to fix ideas. Let's suppose to work with a database containing three tables: teams, teams_information, attributes. More precisely:
1)teams是一个表格，其中包含有关意大利足球队(足球，而非美式足球:D)的一些基本信息，它由三个字段组成:"id"(int, primary key)，名称"(varchar，团队名称)，nickname(Varchar，团队昵称)；
1) teams is a table containing some basic information about italian football teams (soccer, not american football :D), it is formed by three fields: 'id' (int, primary key), 'name' (varchar, team name), nickname (Varchar, team nickname);
2)attributes是一个表格，其中包含有关足球队的可能信息的列表，例如city(球队进行主场比赛的城市)，captain(队长的全名)，f_number (风扇数)等.该表由三个字段组成:id (int, primary key)，attribute_name(varchar，属性的标识符)，attribute_desc(文本，属性含义的解释).该表的每个记录代表一个足球队的单个可能属性；
2) attributes is a table containing a list of possible information about a football team, such as city (the city where team plays its home match), captain (team captain's fullname), f_number (number of fans) and so on. This table is formed by three fields: id (int, primary key), attribute_name (varchar, an identifier for the attribute), attribute_desc (text, an explanation of the meaning of attribute). Each record of this table represents a single possible attribute of a football team;
3)teams_information是一个表，其中提供了一些有关team表中列出的球队的信息.该表包含三个字段:id(int，主键)，team_id(int，标识团队的外键)，attribute_id(int，标识属性之一的外键)在attributes表中列出)，attribute_value(varchar，属性的值).每条记录代表单个团队的单个属性.通常，不同的团队将拥有不同数量的信息，因此对于某些团队而言，大量属性将可用，而对于其他团队而言，仅少数属性将可用.
3) teams_information is a table where some information, about teams listed in team table, are available. This table contains three fields: id (int, primary key), team_id (int, a foreign key which identifies a team), attribute_id (int, a foreign key which identifies one of the attributes listed in attributes table), attribute_value (varchar, the value of the attribute). Each record represents a single attribute of a single team. In general, different teams will have a different number of information, so for some teams a large number of attributes will be available while for other teams only a small number of attributes will be available.
请注意，teams和teams_information之间的关系是一对多的，并且attributes和teams_information之间存在相同的关系
Note that relation between teams and teams_information is one to many and the same relation exists between attributes and teams_information
好吧，考虑到此模型，我的目的是实现一个网格(也许使用ExtJS 4.1)以向用户显示意大利足球队的列表，该网格的每个记录将代表一个足球队，并将包含所有可能的属性:一些字段可能为空(因为对于所考虑的团队而言，因为对于所考虑的团队而言，对应属性是未知的)，而其他字段将包含存储在teams_information表中的值(对于所考虑的团队而言).根据上面的网格，字段是:id，team_name和一些字段，这些字段代表属性"表中列出的所有不同属性.
Well, given this model my purpose is to realize a grid (maybe with ExtJS 4.1) to show user the list of italian football team, each record of this grid will represent a single football team and will contain all possible attributes: some fields may be empty (because, for considered team, the correspondent attribute is unknown), while the others will contain the values stored in teams_information table (for the considered team).According to the above grid's field are: id, team_name and a number of fields to represent all the different attributes listed in 'attributes' table.
我的问题是:我可以通过使用单个SQL查询(也许是一个正确的SELECT查询，以从数据库表中获取我需要的所有数据)来实现这样的网格吗?谁能建议我如何编写类似的查询(如果存在)?
My question is: can I realize such a grid by using a SINGLE SQL query (maybe a proper SELECT query, to fetch all data I need from database tables) ? Can anyone suggest me how to write a similar query (if it exists) ?
预先感谢您的帮助.
致谢.
恩里科.
推荐答案
您的问题的简短答案是否"，MySQL中没有简单的构造可实现所需的结果集.
The short answer to your question is no, there is no simple construct in MySQL to achieve the result set you are looking for.
但是可以仔细地(精心地)制作这样的查询.这是一个例子，我相信您将能够解密它.基本上，对于要返回的每个属性，我都会在选择列表中使用相关子查询.
But it is possible to carefully (painstakingly) craft such a query. Here is an example, I trust you will be able to decipher it. Basically, I'm using correlated subqueries in the select list, for each attribute I want returned.
SELECT t.id
, t.name
, t.nickname
, ( SELECT v1.attribute_value
FROM team_information v1
JOIN attributes a1
ON a1.id = v1.attribute_id AND a1.attribute_name = 'city'
WHERE v1.team_id = t.id ORDER BY 1 LIMIT 1
) AS city
, ( SELECT v2.attribute_value
FROM team_information v2 JOIN attributes a2
ON a2.id = v2.attribute_id AND a2.attribute_name = 'captain'
WHERE v2.team_id = t.id ORDER BY 1 LIMIT 1
) AS captain
, ( SELECT v3.attribute_value
FROM team_information v3 JOIN attributes a3
ON a3.id = v3.attribute_id AND a3.attribute_name = 'f_number'
WHERE v3.team_id = t.id ORDER BY 1 LIMIT 1
) AS f_number
FROM teams t
ORDER BY t.id
对于多值"属性，您必须分别拉出该属性的每个实例. (使用LIMIT指定是否要检索第一个，第二个等).
For 'multi-valued' attributes, you'd have to pull each instance of the attribute separately. (Use the LIMIT to specify whether you are retrieving the first one, the second one, etc.)
, ( SELECT v4.attribute_value
FROM team_information v4 JOIN attributes a4
ON a4.id = v4.attribute_id AND a4.attribute_name = 'nickname'
WHERE v4.team_id = t.id ORDER BY 1 LIMIT 0,1
) AS nickname_1st
, ( SELECT v5.attribute_value
FROM team_information v5 JOIN attributes a5
ON a5.id = v5.attribute_id AND a5.attribute_name = 'nickname'
WHERE v5.team_id = t.id ORDER BY 1 LIMIT 1,1
) AS nickname_2nd
, ( SELECT v6.attribute_value
FROM team_information v6 JOIN attributes a6
ON a6.id = v6.attribute_id AND a6.attribute_name = 'nickname'
WHERE v6.team_id = t.id ORDER BY 1 LIMIT 2,1
) AS nickname_3rd
我在这里以昵称为例，因为美国足球俱乐部经常有多个昵称，例如芝加哥消防足球俱乐部(Chicago Fire Soccer Club)的绰号是:"The Fire"，"LaMáquinaRoja"，"Men in Red"，"CF97"等).
I use nickname as an example here, because American soccer clubs frequently have more than one nickname, e.g. Chicago Fire Soccer Club has nicknames: 'The Fire', 'La Máquina Roja', 'Men in Red', 'CF97', et al.)
无需回答您的问题，但...
NOT AN ANSWER TO YOUR QUESTION, BUT ...
我之前已经提到过很多次，我有多少不喜欢使用EAV数据库实现? IMO应该是一个非常简单的查询，但会变成一个潜在的轻量级调光查询的过于复杂的动物.
Have I mentioned numerous times before, how much I dislike working with EAV database implementations? What should IMO be a very simple query turns into an overly complicated beast of a potentially light dimming query.
创建一个表格，其中每个属性"都是一个单独的列，这会容易得多吗?然后返回合理结果集的查询看起来会更合理...
Wouldn't it be much simpler to create a table where each "attribute" is a separate column? Then queries to return reasonable result sets would look more reasonable...
SELECT id, name, nickname, city, captain, f_number, ... FROM team
但是真正让我感到震惊的是，某些开发人员决定将LDQ作为视图隐藏"在数据库中以启用更简单"的查询的前景.
But what really makes me shudder is the prospect that some developer is going to decide that the LDQ should be "hidden" in the database as a view, to enable the "simpler" query.
如果您走这条路，请拒绝任何要求，您可能必须将此查询作为视图存储在数据库中.
If you go this route, PLEASE PLEASE PLEASE resist any urge you may have to store this query in the database as a view.
这篇关于SQL问题:一对多关系和EAV模型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！