使用ColdFusion查询，查找一个varchar‘标题’的SQL出现次数，但只显示该标题的一个实例

Question

这个CF查询可以做我想做的事情，但是我只想显示每一个匹配的“标题”中的一次，它的计数大于5。我不需要显示实际的计数，只需要显示标题，它将作为一个链接使用该标题作为url变量。一定有更有效的方法让我的代码工作吗？

<cfquery name="HeadInfo" datasource="certify">
    select headline
    from post
    where deleted = 0
    and headline IS NOT NULL
    order by altid desc
</cfquery>

<cfoutput>#HeadInfo.RecordCount#</cfoutput>

<cfoutput query="HeadInfo">

    <cfquery name="CountInfo" datasource="certify">
        select *
        from post
        where deleted = 0
        and headline = '#HeadInfo.Headline#'
        order by headline desc
    </cfquery>

<cfif CountInfo.RecordCount GT 5>
  #HeadInfo.headline# - Count:#CountInfo.RecordCount#<br>
</cfif>
</cfoutput>

Answer 1

让我们假设您有一个名为MySQL或MariaDB的post表，其结构如下：

CREATE TABLE IF NOT EXISTS `post` (
  `altid` int(11) NOT NULL AUTO_INCREMENT,
  `headline` varchar(50) DEFAULT NULL,
  `post` varchar(255) DEFAULT NULL,
  `deleted` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`altid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4;

填充了一些测试值，如：

INSERT INTO `post` (`altid`, `headline`, `post`, `deleted`) VALUES
    (1, 'headline1', 'post headline1 is foo', 0),
    (2, 'headline1', 'post headline1 is bar', 0),
    (3, 'headline1', 'post headline1 is foobar', 0),
    (4, 'headline1', 'post headline1 is contoso', 0),
    (5, 'headline1', 'post headline1 is contoso foo', 0),
    (6, 'headline1', 'post headline1 is contoso bar', 0),
    (7, 'headline2', 'post headline2 is foo is deleted', 1),
    (8, 'headline2', 'post headline2 is bar', 0),
    (9, 'headline2', 'post headline2 is barFoo', 0),
    (10, 'headline2', 'post headline2 is barFoo contoso', 0),
    (11, 'headline2', 'post headline2 is foo contoso', 0),
    (12, 'headline2', 'post headline2 is contoso bar', 0),
    (13, NULL, 'post headline3 is bar with NULL headline', 0),
    (14, 'headline2', 'post headline2 of another post', 0),
    (15, 'headline2', 'post headline2 of another foobar post', 0),
    (16, 'headline3', 'post headline3 some post for count<5', 0);

即使没有一些人建议的JOIN，也可以实现这一点，因为它是同一个表，而且这种选择不需要将表连接到自己。

如果您需要输出所有标题的标题记录数(包括那些< 5)，但只输出计数> 5的点击量，只需选择如下所示：

<cfquery name="HeadInfo" datasource="certify">
    SELECT      headline, count( headline ) as headlineCount -- aggregate function count()
    FROM        post
    WHERE       deleted = 0
                and headline IS NOT NULL
    GROUP BY    headline    -- group by for aggregate count() function
    ORDER BY    altid desc
</cfquery>


<cfoutput>
  Total of headlines #HeadInfo.recordcount#<br>
  <cfloop query="HeadInfo">
    <cfif HeadInfo.headlineCount GT 5>
        #HeadInfo.headline# - Count:#HeadInfo.headlineCount#<br>
    </cfif>
  </cfloop>
</cfoutput>

产出如下：

Total of headlines 3
headline2 - Count:7
headline1 - Count:6

但是，如果不需要所有标题的记录计数，那么使用聚合SQL函数count()和GROUP BY，使用HAVING子句只选择计数>5的标题。这将减少DB与CF引擎之间的有效负载(请参见行之间的SQL-注释)：

<cfquery name="HeadInfo" datasource="certify">
    SELECT      headline, count( headline ) as headlineCount -- aggregate function count()
    FROM        post
    WHERE       deleted = 0
                and headline IS NOT NULL
    GROUP BY    headline    -- group by for aggregate count() function
    HAVING      count( headline ) > 5 -- get headlines with more than 5 posts only (aggregate function count)
    ORDER BY    altid desc
</cfquery>


<cfoutput>
  <cfloop query="HeadInfo">
    #HeadInfo.headline# - Count:#HeadInfo.headlineCount#<br>
  </cfloop>
</cfoutput>

产出如下：

headline2 - Count:7
headline1 - Count:6

Answer 2

您需要在一个查询中执行此操作。

<cfquery name="HeadInfo" datasource="headlines">
SELECT          P.headline
FROM            post as P
    INNER JOIN  (
                    SELECT      headline
                    FROM        post
                    WHERE       deleted = 0
                    GROUP BY    headline
                        HAVING  COUNT(headline) > 5
                ) AS PC
        ON      P.headline = PC.headline
WHERE           P.deleted = 0
    AND         P.headline IS NOT NULL
ORDER BY        altid DESC
</cfquery>

<ul>
    <cfoutput query="HeadInfo">
        <li>#HeadInfo.headline#</li>
    </cfoutput>
</ul>