在子查询中为数据透视表列的值实现WHERE子句? [英] Implementing a WHERE clause within a subquery for the value of a pivot column?

问题描述

我已经成功地执行了一些表联接并使用数据透视表(在Ollie Jones的帮助下)生成了结果. sql语句和结果如下.

I've successfully managed to perform a few table joins and generate a result using a pivot table (with the aid of Ollie Jones). The sql statement and result are below.

我希望返回的结果仅包含为今天或将来设置的枢轴列日期"(我认为这是术语！?)值的行.从我看到的内容来看，WHERE date >= CURDATE()应该可以完成这项工作，因为从技术上讲'date'不存在，当在语句末尾添加到WHERE子句时，执行时会收到错误消息.我不确定如何将其集成到我的第二个子查询中，任何帮助将不胜感激:)

I'd like the results returned to only have rows with values of pivot column 'date' (I think that's the terminology!?) set for today or in the future. From what I can see WHERE date >= CURDATE() should do the job however because 'date' doesn't technically exist I receive an error on execution when added to the WHERE clause at the end of the statement. I'm not sure how to integrate it into my second subquery, any help would be hugely appreciated :)

预先感谢

SELECT 
    content.id as id, content.alias as alias,

    ( 
        SELECT modx_site_tmplvar_contentvalues.value FROM modx_site_tmplvar_contentvalues 
        WHERE modx_site_tmplvar_contentvalues.tmplvarid = 324 
        AND modx_site_tmplvar_contentvalues.contentid = content.id 
     ) AS featured, 

    ( 
        SELECT modx_site_tmplvar_contentvalues.value FROM modx_site_tmplvar_contentvalues 
        WHERE modx_site_tmplvar_contentvalues.tmplvarid = 289 
        AND modx_site_tmplvar_contentvalues.contentid = content.id

     ) AS date

    FROM modx_site_content AS content 

    LEFT JOIN 
        modx_site_tmplvar_contentvalues AS tv_values
        ON tv_values.contentid = content.id 

    WHERE content.parent = 1842 
     AND content.published = 1 

    GROUP BY tv_values.contentid 
    ORDER BY featured DESC, date ASC

推荐答案

一个选项si可以更改日期"部分，并使用左连接"进行操作:

One option si to change section of "date" and do it with "left join":

SELECT 
    content.id as id, content.alias as alias,

    ( 
        SELECT modx_site_tmplvar_contentvalues.value FROM modx_site_tmplvar_contentvalues 
        WHERE modx_site_tmplvar_contentvalues.tmplvarid = 324 
        AND modx_site_tmplvar_contentvalues.contentid = content.id 
     ) AS featured, 

    date_values.value as date

    FROM modx_site_content AS content 

    LEFT JOIN 
        modx_site_tmplvar_contentvalues AS tv_values
        ON tv_values.contentid = content.id

    LEFT JOIN
        modx_site_tmplvar_contentvalues AS date_values
        ON date_values.contentid = content.id

WHERE content.parent = 1842 
    AND content.published = 1 
    AND date_values.tmplvarid = 289
    AND date_values.value >= CURDATE() 

GROUP BY tv_values.contentid 
ORDER BY featured DESC, date ASC

这篇关于在子查询中为数据透视表列的值实现WHERE子句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！