使用并集合并两个查询 [英] Combine two queries using union

问题描述

您能帮我使用UNION组合以下两个查询吗?
查询1:

Can you help me to combine the following two queries using UNION
Query 1:

SELECT  
	sr.Reg_Id as [Reg Id],
	se.Enquiry_Id as [Enquiry Id],
	se.studentName as [StudentName],
	se.ResPhone as [ResPhone],
	se.MobileNo as [MobileNo],
	tblUserDetails.UserName as Counselor,
	[dbo].fnGetStringOfCourseFromRegId(sr.Reg_Id) AS Courses,
	sf.Installment1 as Installment1,
	sf.PaymentationDate1 as PaymentationDate1,
	sf.InstallmentAmt1 as InstallmentAmt1
 	FROM
         	tblStudentsRegister sr  INNER JOIN tblStudentsEnquiry se ON
         	sr.Enquiry_Id=se.Enquiry_Id inner join tblFeeDatailsCalc sf on sf.EnqueryId=sr.Enquiry_Id 
         	and sf.PaymentationDate1>=@fromdate  and sf.PaymentationDate1<= @todate
         	 inner join tblUserDetails on 
         	se.Councelor_Id = tblUserDetails.User_Id  AND se.studentName like @studentname
         	AND se.Company_Id=5 and se.feestatus=0 ORDER BY  sr.Reg_Id 
  Query2:
    SELECT  
	sr.Reg_Id as [Reg Id],
	se.Enquiry_Id as [Enquiry Id],
	se.studentName as [StudentName],
	se.ResPhone as [ResPhone],
	se.MobileNo as [MobileNo],
	tblUserDetails.UserName as Counselor,
	[dbo].fnGetStringOfCourseFromRegId(sr.Reg_Id) AS Courses,
	sf.Installment2 as Installment,
	sf.PaymentationDate2 as PaymentationDate,
	sf.InstallmentAmt2 as InstallmentAmt
 	FROM
         	tblStudentsRegister sr  INNER JOIN tblStudentsEnquiry se ON
         	sr.Enquiry_Id=se.Enquiry_Id inner join tblFeeDatailsCalc sf on sf.EnqueryId=sr.Enquiry_Id 
         	and sf.PaymentationDate2>=@fromdate  and sf.PaymentationDate2<= @todate
         	 inner join tblUserDetails on 
         	se.Councelor_Id = tblUserDetails.User_Id  AND se.studentName like @studentname
         	AND se.Company_Id=5 and se.feestatus=0 ORDER BY  sr.Reg_Id 

推荐答案

只需将 UNION [ ^ ]两个选择 [

Just put the UNION[^] between the two SELECT[^] queries.

SELECT *
UNION
SELECT *

尝试一下

SELECT  
	sr.Reg_Id as [Reg Id],
	se.Enquiry_Id as [Enquiry Id],
	se.studentName as [StudentName],
	se.ResPhone as [ResPhone],
	se.MobileNo as [MobileNo],
	tblUserDetails.UserName as Counselor,
	[dbo].fnGetStringOfCourseFromRegId(sr.Reg_Id) AS Courses,
	sf.Installment1 as Installment1,
	sf.PaymentationDate1 as PaymentationDate1,
	sf.InstallmentAmt1 as InstallmentAmt1
 	FROM
         	tblStudentsRegister sr  INNER JOIN tblStudentsEnquiry se ON
         	sr.Enquiry_Id=se.Enquiry_Id inner join tblFeeDatailsCalc sf on sf.EnqueryId=sr.Enquiry_Id 
         	and sf.PaymentationDate1>=@fromdate  and sf.PaymentationDate1<= @todate
         	 inner join tblUserDetails on 
         	se.Councelor_Id = tblUserDetails.User_Id  AND se.studentName like @studentname
         	AND se.Company_Id=5 and se.feestatus=0 
  union
    SELECT  
	sr.Reg_Id as [Reg Id],
	se.Enquiry_Id as [Enquiry Id],
	se.studentName as [StudentName],
	se.ResPhone as [ResPhone],
	se.MobileNo as [MobileNo],
	tblUserDetails.UserName as Counselor,
	[dbo].fnGetStringOfCourseFromRegId(sr.Reg_Id) AS Courses,
	sf.Installment2 as Installment1,
	sf.PaymentationDate2 as PaymentationDate1,
	sf.InstallmentAmt2 as InstallmentAmt1
 	FROM
         	tblStudentsRegister sr  INNER JOIN tblStudentsEnquiry se ON
         	sr.Enquiry_Id=se.Enquiry_Id inner join tblFeeDatailsCalc sf on sf.EnqueryId=sr.Enquiry_Id 
         	and sf.PaymentationDate2>=@fromdate  and sf.PaymentationDate2<= @todate
         	 inner join tblUserDetails on 
         	se.Councelor_Id = tblUserDetails.User_Id  AND se.studentName like @studentname
         	AND se.Company_Id=5 and se.feestatus=0 ORDER BY  sr.Reg_Id 

在全部执行联合/联合之前，两个查询都具有相同的列数和列数据类型也相同.

-UNION将保留副本
-UNION ALL将允许所有条目(允许重复)
-order by子句应使用last


选择
sr.Reg_Id为[Reg Id]，
se.Enquiry_Id为[查询ID]，
se.studentName为[StudentName]，
se.ResPhone为[ResPhone]，
se.MobileNo为[MobileNo]，
tblUserDetails.UserName作为顾问，
[dbo] .fnGetStringOfCourseFromRegId(sr.Reg_Id)AS课程，
sf.Installation1作为Installment1，
sf.PaymentationDate1作为PaymentationDate1，
sf.InstallmentAmt1作为InstallmentAmt1
来自
tblStudentsRegister sr INNER JOIN tblStudents查询se
sr.Enquiry_Id = se.Enquiry_Id内部联接tblFeeDatailsCalc sf上的sf.EnqueryId = sr.Enquiry_Id
和sf.PaymentationDate1> = @ fromdate和sf.PaymentationDate1< = @todate
在
上的内部连接tblUserDetails se.Councelor_Id = tblUserDetails.User_Id和se.studentName，例如@studentname
AND se.Company_Id = 5和se.feestatus = 0

UNION

选择
sr.Reg_Id为[Reg Id]，
se.Enquiry_Id为[查询ID]，
se.studentName为[StudentName]，
se.ResPhone为[ResPhone]，
se.MobileNo为[MobileNo]，
tblUserDetails.UserName作为顾问，
[dbo] .fnGetStringOfCourseFromRegId(sr.Reg_Id)AS课程，
sf.Installation2作为分期付款，
sf.PaymentationDate2作为PaymentationDate，
sf.InstallmentAmt2作为InstallmentAmt
来自
tblStudentsRegister sr INNER JOIN tblStudents查询se
sr.Enquiry_Id = se.Enquiry_Id内部联接tblFeeDatailsCalc sf上的sf.EnqueryId = sr.Enquiry_Id
和sf.PaymentationDate2> = @ fromdate和sf.PaymentationDate2< = @todate
在
上的内部连接tblUserDetails se.Councelor_Id = tblUserDetails.User_Id和se.studentName，例如@studentname
AND se.Company_Id = 5和se.feestatus = 0
ORDER BY 1.

Before u do union/union all, both queries have same no of columns and also column datatypes also same.

-- UNION will retain the duplicates
-- UNION ALL will allow all the entries (allow duplicates)
-- Order by clause should use last


SELECT
sr.Reg_Id as [Reg Id],
se.Enquiry_Id as [Enquiry Id],
se.studentName as [StudentName],
se.ResPhone as [ResPhone],
se.MobileNo as [MobileNo],
tblUserDetails.UserName as Counselor,
[dbo].fnGetStringOfCourseFromRegId(sr.Reg_Id) AS Courses,
sf.Installment1 as Installment1,
sf.PaymentationDate1 as PaymentationDate1,
sf.InstallmentAmt1 as InstallmentAmt1
FROM
tblStudentsRegister sr INNER JOIN tblStudentsEnquiry se ON
sr.Enquiry_Id=se.Enquiry_Id inner join tblFeeDatailsCalc sf on sf.EnqueryId=sr.Enquiry_Id
and sf.PaymentationDate1>=@fromdate and sf.PaymentationDate1<= @todate
inner join tblUserDetails on
se.Councelor_Id = tblUserDetails.User_Id AND se.studentName like @studentname
AND se.Company_Id=5 and se.feestatus=0

UNION

SELECT
sr.Reg_Id as [Reg Id],
se.Enquiry_Id as [Enquiry Id],
se.studentName as [StudentName],
se.ResPhone as [ResPhone],
se.MobileNo as [MobileNo],
tblUserDetails.UserName as Counselor,
[dbo].fnGetStringOfCourseFromRegId(sr.Reg_Id) AS Courses,
sf.Installment2 as Installment,
sf.PaymentationDate2 as PaymentationDate,
sf.InstallmentAmt2 as InstallmentAmt
FROM
tblStudentsRegister sr INNER JOIN tblStudentsEnquiry se ON
sr.Enquiry_Id=se.Enquiry_Id inner join tblFeeDatailsCalc sf on sf.EnqueryId=sr.Enquiry_Id
and sf.PaymentationDate2>=@fromdate and sf.PaymentationDate2<= @todate
inner join tblUserDetails on
se.Councelor_Id = tblUserDetails.User_Id AND se.studentName like @studentname
AND se.Company_Id=5 and se.feestatus=0
ORDER BY 1 .

这篇关于使用并集合并两个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！