使用左连接时出现重复行 [英] Duplicate rows occuring when use left join
本文介绍了使用左连接时出现重复行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的存储过程就是这个
创建 程序 EnquiryAdmission as
create table #Calendar
(月份日期)
insert INTO #Calendar VALUES (' 1/1/2014'),(' 2/1/2014'),(' 3/1/2014'),(' 4/1/2014'),(' 5/1/2014'),(' 6/1/2014 '),(' 7/1/2014'),(' 8/1 / 2014'),(' 9/1/2014'),(' 10/1/2014'),(' 11/1/2014'),(' 12 / 1/2014')
BEGIN
SELECT cal.Months, DISTINCT COUNT(enqno) AS 查询, DISTINCT 计数(learnerid) AS 录取
FROM #calendar cal
LEFT JOIN 查询e
ON 月(cal.Months)=月([查询日期])
LEFT 加入入场
< span class =code-keyword> ON 月(cal.Months)=月(日期)
WHERE (年([查询日期])=年(GetDate()))或 [inquiry date ] IS NULL
AND (年(日期)=年(GetDate())) OR date IS NULL
GROUP BY cal.Months
END
但它会提供重复记录。它应该给出准确的计数。我该怎么做才能解决这个问题
解决方案
你可以在写入查询之前更好地使用INNER JOIN。
了解什么是LEFT,正确,内部联系
阅读此文章
http://www.dotnet-tricks.com/Tutorial/sqlserver/W1aI140312 -Different-Types-of-SQL-Joins.html [ ^ ]
创建 procedure EnquiryAdmission as
创建 table #Calendar
(月份日期)
插入INTO #Calendar VALUES (' 2014年1月1日'),(' 2014年2月1日'),(' 3/1/2014'),(' 4/1/2014'),(' < span class =code-string> 5/1/2014'),(' 6/1 / 2014'),(' 7/1/2014'),(' 8/1/2014'),(' 9/1/2014'),(' 10 / 1/2014'),(' 11/1/2014'),( ' 12/1/2014')
BEGIN
SELECT datename(mm,cal.months),COUNT( DISTINCT enqno) AS 查询,计数( DISTINCT learnerid) AS 录取
FROM #calendar cal
LEFT JOIN 查询e
ON 月(cal.Months)=月([查询日期]) AND ((年([查询日期])=年(GetDate())) OR [查询日期] IS NULL )
LEFT JOIN adm ission
ON 月(cal.Months)=月(日期) AND ((YEAR( date )= YEAR(GetDate())) OR date IS NULL )
GROUP BY cal.Months
END 跨度>
my stored procedure is this
create procedure EnquiryAdmission as
create table #Calendar
(Months date)
insert INTO #Calendar VALUES('1/1/2014'),('2/1/2014'),('3/1/2014'),('4/1/2014'),('5/1/2014'),('6/1/2014'),('7/1/2014'),('8/1/2014'),('9/1/2014'),('10/1/2014'),('11/1/2014'),('12/1/2014')
BEGIN
SELECT cal.Months,DISTINCT COUNT(enqno) AS Enquiries,DISTINCT count(learnerid)AS Admissions
FROM #calendar cal
LEFT JOIN enquiry e
ON Month(cal.Months) = Month([enquiry date])
LEFT JOIN admission
ON Month(cal.Months)=Month(Date)
WHERE (YEAR([enquiry date])=YEAR(GetDate())) OR [enquiry date] IS NULL
AND (YEAR(date)=YEAR(GetDate())) OR date IS NULL
GROUP BY cal.Months
END
but it gives duplicate records. It should give exact count. What can I do to solve this problem
解决方案
better you can use INNER JOIN.
before your Write query know about What is LEFT,RIGHT,INNER JOINS
Read this Article
http://www.dotnet-tricks.com/Tutorial/sqlserver/W1aI140312-Different-Types-of-SQL-Joins.html[^]
CREATE procedure EnquiryAdmission as create table #Calendar (Months date) insert INTO #Calendar VALUES('1/1/2014'),('2/1/2014'),('3/1/2014'),('4/1/2014'),('5/1/2014'),('6/1/2014'),('7/1/2014'),('8/1/2014'),('9/1/2014'),('10/1/2014'),('11/1/2014'),('12/1/2014') BEGIN SELECT datename(mm,cal.months), COUNT(DISTINCT enqno) AS Enquiries,count(DISTINCT learnerid)AS Admissions FROM #calendar cal LEFT JOIN enquiry e ON Month(cal.Months) = Month([enquiry date]) AND ((YEAR([enquiry date])=YEAR(GetDate())) OR [enquiry date] IS NULL) LEFT JOIN admission ON Month(cal.Months)=Month(Date) AND ((YEAR(date)=YEAR(GetDate())) OR date IS NULL) GROUP BY cal.Months END
这篇关于使用左连接时出现重复行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文