将两条SQL语句合并为一条

Question

试图解决以下问题。

我有两条SQL语句：

SELECT TagID, TagTitle, 
COUNT(*) AS TagCount 
FROM TagCategories 
JOIN SocialAndTags 
ON SocialAndTags.TagCategoriesID = TagID
JOIN SocialManagementTracker 
ON SocialAndTags.SMTrackerID = SocialID 
WHERE InitialComment > '2020-01-01 00:00:00.000' 
AND InitialComment < '2020-03-25 23:59:59.999' 
GROUP BY TagTitle, TagID

SELECT TagID, TagTitle, 
COUNT(*) AS TagCount 
FROM TagCategories 
JOIN SocialAndTags 
ON SocialAndTags.TagCategoriesID_OtherA = TagID
JOIN SocialManagementTracker 
ON SocialAndTags.SMTrackerID = SocialID 
WHERE InitialComment > '2020-01-01 00:00:00.000' 
AND InitialComment < '2020-03-25 23:59:59.999' 
GROUP BY TagTitle, TagID

第一个查询给出了以下正确的结果：

TagID | TagTitle | TagCount
1     | PASO     | 26
2     | TUIT     | 1
3     | AST      | 3
4     | IS       | 3
10    | NA       | 17
20    | PSE      | 3
21    | CO       | 40

第二个查询给出了以下结果，这些结果也是正确的：

TagID | TagTitle | TagCount
1     | PASO     | 14
2     | TUIT     | 3
4     | IS       | 3
20    | PSE      | 13
22    | NONE     | 55
23    | Digital  | 2
24    | PCC      | 3

我需要的是让两个结果都出现在一个查询中，但我似乎不知道如何做到这一点。

我的最后一个问题是：

SELECT TagTitle,
COUNT(*) AS TagCount 
FROM TagCategories
LEFT OUTER JOIN SocialAndTags AS OtherA ON OtherA.TagCategoriesID = TagID
LEFT OUTER JOIN SocialAndTags AS OtherB ON OtherB.TagCategoriesID_OtherA = TagID
GROUP BY TagTitle

其中一些结果是正确的，但另一些结果却大错特错。例如，PASO结果是364，应该是40，TUIT结果是3，应该是4

或者，Digital的结果是2，这是正确的，没有55是正确的，这也是正确的

很明显我做错了什么。如有任何帮助，我们将不胜感激。

Answer 1

你可以试试这个。

SELECT TagID, TagTitle, 
COUNT(*) AS TagCount 
FROM TagCategories 
    JOIN SocialAndTags ON (SocialAndTags.TagCategoriesID = TagID OR SocialAndTags.TagCategoriesID_OtherA = TagID)
    JOIN SocialManagementTracker ON SocialAndTags.SMTrackerID = SocialID 
WHERE InitialComment > '2020-01-01 00:00:00.000' AND InitialComment < '2020-03-25 23:59:59.999' 
GROUP BY TagTitle, TagID

Answer 2

您可以使用UNION在两个SELECT https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-union-transact-sql?view=sql-server-ver15之间执行此操作

Answer 3

您可以将查询合并在一起，并获得计数的总和。例如：

SELECT TagID, TagTitle, SUM(TagCount)
FROM
(
    SELECT TagID, TagTitle, COUNT(*) AS TagCount 
    FROM TagCategories 
    JOIN SocialAndTags ON SocialAndTags.TagCategoriesID = TagID
    JOIN SocialManagementTracker ON SocialAndTags.SMTrackerID = SocialID 
    WHERE InitialComment > '2020-01-01 00:00:00.000' AND InitialComment < '2020-03-25 23:59:59.999' 
    GROUP BY TagTitle, TagID
    UNION ALL
    SELECT TagID, TagTitle, COUNT(*) AS TagCount 
    FROM TagCategories 
    JOIN SocialAndTags ON SocialAndTags.TagCategoriesID_OtherA = TagID
    JOIN SocialManagementTracker ON SocialAndTags.SMTrackerID = SocialID 
    WHERE InitialComment > '2020-01-01 00:00:00.000' AND InitialComment < '2020-03-25 23:59:59.999' 
    GROUP BY TagTitle, TagID
) BothCounts
GROUP BY TagTitle, TagID