我有几个表,我只想检查是否在这些表中找到了某些条件的重复项。
这是我的 SQL 脚本:
--SELECT SUM(intSubCount) //this is first line that gets total no of summary
--Check Duplicate Subscribers Found :
(SELECT
s.Id, s.EmailAddress,
(SELECT Name FROM tbl_User_master
WHERE Id = s.user_id) AS CreatedBy,
s.SubscriptionDateTime, d.intSubCount
FROM
(SELECT EmailAddress, COUNT(*) as intSubCount
FROM tbl_Subscribers
WHERE user_id = '1' AND category_id = '17'
GROUP BY EmailAddress
HAVING COUNT(*) > 1) AS d
INNER JOIN
tbl_Subscribers s ON s.EmailAddress = d.EmailAddress)
--Check Duplicate From Users Found :
(SELECT
f.Id, f.Name, f.EmailAddress,
(SELECT Name FROM tbl_User_master WHERE Id = f.user_id) AS CreatedBy,
f.CreatedDate, d.intFromCount
FROM
(SELECT EmailAddress, COUNT(*) AS intFromCount
FROM tbl_From_master
WHERE user_id = '1'
GROUP BY EmailAddress
HAVING COUNT(*) > 1) AS d
INNER JOIN
tbl_From_master f ON f.EmailAddress = d.EmailAddress)
--Check Duplicate Categories Found
(SELECT
c.Id,c.CategoryName,(SELECT Name FROM tbl_User_master WHERE Id = c.user_id) As CreatedBy, d.intCatCount
FROM (
SELECT CategoryName, COUNT(*) as intCatCount
FROM tbl_Categories WHERE user_id='1'
GROUP BY CategoryName
HAVING COUNT(*) > 1
) AS d
INNER JOIN tbl_Categories c ON c.CategoryName = d.CategoryName)
--Check Duplicate Templates Categories Found
(SELECT
t.Id,t.CategoryName,(SELECT Name FROM tbl_User_master WHERE Id = t.user_id) As CreatedBy, d.intTCatCount
FROM (
SELECT CategoryName, COUNT(*) as intTcatCount
FROM tbl_Template_Categories WHERE user_id='1'
GROUP BY CategoryName
HAVING COUNT(*) > 1
) AS d
INNER JOIN tbl_Template_Categories t ON t.CategoryName = d.CategoryName)
--Check Duplicate Templates Found
(SELECT
t.Id,t.TemplateName,(SELECT Name FROM tbl_User_master WHERE Id = t.user_id) As CreatedBy,t.Created_date, d.intTCount
FROM (
SELECT TemplateName, COUNT(*) as intTCount
FROM tbl_Template_master WHERE user_id='1'
GROUP BY TemplateName
HAVING COUNT(*) > 1
) AS d
INNER JOIN tbl_Template_master t ON t.TemplateName = d.TemplateName)
现在我刚刚得到了每个查询的 int 变量计数,现在我只想求和 (intSubCount,intFromCount,intCatCount,intTCatCount,intTCount) 看到我这个脚本的第一行
为了运行这个脚本,我得到了这个结果:
(4 row(s) affected)
(2 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(2 row(s) affected)
我怎样才能做到这一点?请大家帮帮我..
------------------------------编辑------------------- -------
我尝试了这个溶胶,但给出了错误的行数
SELECT SUM(intSubCount) as GrandTotal
FROM(
--Check Duplicate Subscribers Found :
SELECT COUNT(*) as intSubCount
FROM tbl_Subscribers WHERE user_id='1' AND category_id='17'
GROUP BY EmailAddress
HAVING COUNT(*) > 1
UNION ALL
--Check Duplicate From Users Found :
SELECT COUNT(*) as intFromCount
FROM tbl_From_master WHERE user_id='1'
GROUP BY EmailAddress
HAVING COUNT(*) > 1
UNION ALL
--Check Duplicate Categories Found
SELECT COUNT(*) as intCatCount
FROM tbl_Categories WHERE user_id='1'
GROUP BY CategoryName
HAVING COUNT(*) > 1
UNION ALL
--Check Duplicate Templates Categories Found
SELECT COUNT(*) as intTCatCount
FROM tbl_Template_Categories WHERE user_id='1'
GROUP BY CategoryName
HAVING COUNT(*) > 1
UNION ALL
--Check Templates Categories Found
SELECT COUNT(*) as intTCount
FROM tbl_Template_master WHERE user_id='1'
GROUP BY TemplateName
HAVING COUNT(*) > 1
--UNION ALL
) AS Totals
结果是:
Grand Total :
7
如果您只关心sum
每个单独查询的 ,并且每个单独查询只会返回一个值,您可以简单地将它们添加为子选择:
select (select Query 1 total) + (select Query 2 total) + (select Query 3 total) as GrandTotal
另一种选择是union
结果和sum
结果数据集:
select sum(total) as GrandTotal
from(select total
from Query 1
union all -- the 'all' ensures no duplicate numbers are removed for an accurate total
select total
from Query 2
union all
select total
from Query 3
) as totals
任何一个:
SELECT
--Check Duplicate Subscribers Found :
(SELECT COUNT(*) as intSubCount
FROM tbl_Subscribers WHERE user_id='1' AND category_id='17'
GROUP BY EmailAddress
HAVING COUNT(*) > 1
)
+
--Check Duplicate From Users Found :
(SELECT intFromCount
FROM tbl_From_master WHERE user_id='1'
GROUP BY EmailAddress
HAVING COUNT(*) > 1
)
+
--Check Duplicate Categories Found
(SELECT intCatCount
FROM tbl_Categories WHERE user_id='1'
GROUP BY CategoryName
HAVING COUNT(*) > 1
)
as GrandTotal
或者
SELECT SUM(intSubCount) as GrandTotal
FROM(
--Check Duplicate Subscribers Found :
SELECT COUNT(*) as intSubCount
FROM tbl_Subscribers WHERE user_id='1' AND category_id='17'
GROUP BY EmailAddress
HAVING COUNT(*) > 1
UNION ALL
--Check Duplicate From Users Found :
SELECT intFromCount
FROM tbl_From_master WHERE user_id='1'
GROUP BY EmailAddress
HAVING COUNT(*) > 1
UNION ALL
--Check Duplicate Categories Found
SELECT intCatCount
FROM tbl_Categories WHERE user_id='1'
GROUP BY CategoryName
HAVING COUNT(*) > 1
) AS Totals
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句