用于从两个表中检索匹配和不匹配记录的SQL查询。 [英] sql query for retrieving matched and unmatched records from two tables.

问题描述

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

推荐答案

检查以下脚本...

Hi,

Check t he following script...

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

嗨约翰，



我已经按照你在问题中提到的创建了表格，并使用下面的代码块添加了数据。

Hi John,

I have created tables as you mentioned in your question and added the data using following code block.

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查询。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！