MSSQL:选择另一个表中出现次数超过 2 次的行 [英] MSSQL: Select rows with more than 2 occurrences in another table

问题描述

基本上，我需要获取在 StaffOnGrade 中出现超过 2 次的 CampaignTitle 列表，并列出等级等级高于 2 的 CampaignTitle、StaffNo

Basically I need to get a list of CampaignTitles that have more than 2 occurrences in StaffOnGrade and list the CampaignTitle, StaffNo who have a Grade rating higher then 2

WorksOn 表:

CampaignTitle      | StaffNo
-------------------|--------
ADVENTURE WORLD    | 11
AIR CANADA         | 11
CARNIVAL CRUISES   | 3
CARNIVAL CRUISES   | 8
CARNIVAL CRUISES   | 9
FLIGHT CENTRE      | 7
FLIGHT CENTRE      | 10
HARVEYWORLD TRAVEL | 4
LAST MINUTE        | 4
PRINCESS CRUISES   | 3
PRINCESS CRUISES   | 5
PRINCESS CRUISES   | 6
PRINCESS CRUISES   | 7
PRINCESS CRUISES   | 11
TRAVELSCENE        | 10
VALUETOURS AUST    | 3
VIRGIN AUSTRALIA   | 10

StaffOnGrade 表:

StaffOnGrade table:

Grade | StaffNo
------|--------
1     | 2
2     | 11
3     | 3
3     | 6
3     | 7
4     | 4
4     | 8
4     | 10
5     | 5
5     | 9

以下两个查询实现了各个部分，但我需要将其作为一组查询结果返回.

The following two queries achieve the individual parts, but I need it returned as one query set of results.

SELECT campaigntitle, COUNT (CAMPAIGNTITLE) As [count]
FROM WORKSON
GROUP BY CAMPAIGNTITLE
HAVING COUNT(CAMPAIGNTITLE) >2

SELECT STAFFNO, GRADE
FROM STAFFONGRADE
WHERE GRADE > 2

希望这是有道理的！

推荐答案

SELECT campaigntitle, StaffNo, COUNT (CAMPAIGNTITLE) As [count]
  FROM WORKSON
 WHERE StaffNo IN
       (SELECT STAFFNO
          FROM STAFFONGRADE
         WHERE GRADE > 2)
 GROUP BY CAMPAIGNTITLE
HAVING COUNT(CAMPAIGNTITLE) >2

这篇关于MSSQL:选择另一个表中出现次数超过 2 次的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！