使用 ColdFusion 查询，查找 varchar 'headline'的 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

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

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，您也可以实现这一点，正如一些人所建议的那样，因为它是同一个表，并且这种SELECT不需要将表本身加入。

如果您需要输出所有标题(包括<5(的标题记录计数，但只输出计数>5，只需一个SELECT，如下所示：

<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

但是，如果不需要所有标题的记录计数，则使用HAVING子句使用具有GROUP BY的聚合SQL函数count()来仅选择计数>5已在SQL中。这将减少与CF引擎的DB连接之间的有效负载(请参阅我的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>