mysql - MySQL join查询时用对应的值填充 'temporary'列，也限制

Question

我正在执行多个 MySQL 连接来获取模板变量(即自定义字段)及其值(在 MODX Evo 中，但它不相关 - 这是一般的 MySQL 查询)。

我希望能够创建 2 个临时列，以便在查询中使用 SORT BY 或类似的效果。我想在这些新列中填充“event_date”和“event_featured”的相应 id 值 - 然后我可以按这些列对结果进行排序。

在一个非常相关的说明中，我想将每个唯一 id 的结果限制为 20 个，而不是像我添加 LIMIT 时那样将每行的结果限制为 20 个 - 它会将以下结果裁剪到 .这可以同时完成吗？

有人知道如何/如果这些是可能的吗？非常感谢。

结果的代码和图像如下:

SELECT DISTINCT
    content.id, content.pagetitle, content.template , content.published,
    templates.templatename,
    tv_props.name,
    tv_values.value

FROM `modx_site_content` AS `content`
LEFT JOIN `modx_site_templates` AS `templates` ON content.template=templates.id
LEFT JOIN `modx_site_tmplvar_templates` AS `template_tvs` ON templates.id=template_tvs.templateid
LEFT JOIN `modx_site_tmplvars` AS `tv_props` ON template_tvs.tmplvarid=tv_props.id
LEFT JOIN `modx_site_tmplvar_contentvalues` AS `tv_values` ON template_tvs.tmplvarid=tv_values.tmplvarid

WHERE templates.id=89
AND (
    tv_props.name='event_featured'
OR tv_props.name='event_link_through'
OR tv_props.name='event_title'
OR tv_props.name='event_date'
OR tv_props.name='event_date_text'
OR tv_props.name='event_short_description'
OR tv_props.name='event_list_image'
);

Link to full-size image

Answer 1

您将需要几个虚拟表(也称为子查询)来从名称/值表中检索事件的这两个属性。供您引用，此类查询的通用名称是“枢轴”。

诀窍是将子查询视为可以在周围查询中使用的虚拟表。我相信 event_date 的子查询看起来像这样。

      SELECT content.id        AS id,
     tv_values.value   AS event_date
        FROM modx_site_content               AS content
   LEFT JOIN modx_site_templates             AS templates 
      ON content.template=templates.id
   LEFT JOIN modx_site_tmplvar_templates     AS template_tvs 
      ON templates.id=template_tvs.templateid
   LEFT JOIN modx_site_tmplvars              AS tv_props 
      ON template_tvs.tmplvarid=tv_props.id
   LEFT JOIN modx_site_tmplvar_contentvalues AS tv_values 
      ON template_tvs.tmplvarid=tv_values.tmplvarid
       WHERE tv_props.name = 'event_date'

这个小查询会生成一个结果集，该结果集是将内容 ID 与事件日期相关联的表。老实说，我不太了解您的架构，无法知道每个内容 id 是否只有一个事件日期，因此您可能需要调整此查询以选择更多列。当您调试它时，您应该尝试子查询并确保它给出您希望的结果。

然后，当您确定子查询正确时，您可以将该子查询加入到整个查询中，通常如下所示。

  SELECT DISTINCT
          content.id, event_date.event_date, templates.column,
          table.column, table.colum, etc, etc
     FROM modx_site_content AS content
    LEFT JOIN table ON condition
LEFT JOIN (
      SELECT content.id        AS id,
     tv_values.value   AS event_date
        FROM modx_site_content               AS content
   LEFT JOIN modx_site_templates             AS templates 
      ON content.template=templates.id
   LEFT JOIN modx_site_tmplvar_templates     AS template_tvs 
      ON templates.id=template_tvs.templateid
   LEFT JOIN modx_site_tmplvars              AS tv_props 
      ON template_tvs.tmplvarid=tv_props.id
   LEFT JOIN modx_site_tmplvar_contentvalues AS tv_values 
      ON template_tvs.tmplvarid=tv_values.tmplvarid
       WHERE tv_props.name = 'event_date'
     ) AS event_date ON event_date.id = content.id
LEFT JOIN etc, etc, etc.
    WHERE etc etc etc

你知道事情进展如何了吗？您可以互换使用tablename AS table 或(某些查询)AS table。您还可以在架构中定义提供相同数据的 VIEW，并在查询中对其进行命名。这是一种让您的查询不再那么复杂的便捷方法。

顺便说一下，如果你做出改变，你的性能将会提高

AND (
    tv_props.name='event_featured'
 OR tv_props.name='event_link_through'
 OR tv_props.name='event_title' etc  )

到

 AND tv.props.name IN ('event_featured',
                       'event_link_through',
                       'event_title', etc)

您可能已经注意到我对 SQL 查询中的缩进有点坚持。我发现这很有帮助；我在修复缩进时经常发现错误。您的做法可能会有所不同。