问题描述
sql查询用于从两个表中检索匹配和不匹配的记录。
table1
Id名称地址日期月份年份
1 sony Hyd 10 06 2013
2 jhon Blr 5 06 2013
3 Ravi Hyd 6 06 2013
table2
id sal
2 10,000
3 20,000
O / P：
id状态类别地址Sal
1 Un_matched 0-5 sony Hyd 0
2匹配5-10 jhon Blr 10,000
3匹配5-10 Ravi Hyd 20,000
Hi,
sql query for retrieving matched and unmatched records from two tables.
table1
Id Name Address Date Month Year
1 sony Hyd 10 06 2013
2 jhon Blr 5 06 2013
3 Ravi Hyd 6 06 2013
table2
id sal
2 10,000
3 20,000
O/P:
id Status Catagory Name Address Sal
1 Un_matched 0-5 sony Hyd 0
2 matched 5-10 jhon Blr 10,000
3 matched 5-10 Ravi Hyd 20,000
推荐答案
SELECT T1.ID,
CASE WHEN T2.ID IS NULL THEN 'Un_matched'
ELSE 'matched' END 'Status',
CASE WHEN DATEDIFF(Day,CAST(T1.Year as VARCHAR(4))+ '-' + CAST(T1.Month As VARCHAR(2))+ '-' + CAST(T1.Day As VARCHAR(2)),GETDATE())>=-5 THEN '0-5'
WHEN DATEDIFF(Day,CAST(T1.Year as VARCHAR(4))+ '-' + CAST(T1.Month As VARCHAR(2))+ '-' + CAST(T1.Day As VARCHAR(2)),GETDATE())<-5 THEN '5-10'
ELSE '-' END 'Category',
T1.Name,
T1.Address,
ISNULL(T2.Sal,0) 'Sal'
FROM tabl1 T1
LEFT OUTER JOIN table2 T2 On T2.id=T1.id
问候，
GVPrabu
Regards,
GVPrabu
INSERT INTO Table_1 VALUES
(1,'sony', 'Hyd','10','06','2013'),
(2,'jhon','Blr','05','06','2013'),
(3,'Ravi','Hyd','06','06','2013')
INSERT INTO Table_2 Values
(2,10000),
(3,20000)
这是我的查询获得所需的结果集。
Here is my query to get desired result set.
SELECT Table_1.Id,Table_1.Name, (CASE WHEN Table_2.Id IS NULL THEN 'Not Matched' ELSE 'Matched' END) As Status,
CAST(DATEDIFF(D,(Table_1.Year+Table_1.Month+Table_1.Date) ,CONVERT(VARCHAR,GETDATE(),112))
- (DATEDIFF(D,(Table_1.Year+Table_1.Month+Table_1.Date) ,CONVERT(VARCHAR,GETDATE(),112)) % 5) AS VARCHAR)+'-'+
CAST(DATEDIFF(D,(Table_1.Year+Table_1.Month+Table_1.Date) ,CONVERT(VARCHAR,GETDATE(),112))
- (DATEDIFF(D,(Table_1.Year+Table_1.Month+Table_1.Date) ,CONVERT(VARCHAR,GETDATE(),112)) % 5)+ 5 AS VARCHAR) As Category,
(CASE WHEN Table_2.Id IS NULL THEN 0 ELSE Table_2.Sal END) As Salary
FROM Table_1 LEFT JOIN Table_2
ON Table_1.Id = Table_2.Id
我希望这会对你有所帮助。
快乐编码。
I hope this helps you.
Happy coding.
这篇关于用于从两个表中检索匹配和不匹配记录的SQL查询。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！