1 with temp1 as ( 2 select 1 as id ,111 as barCode,'A1' name union 3 select 2 as id ,112 as barCode,'A2' name union 4 select 3 as id ,200 as barCode,'B1' name union 5 select 4 as id ,230 as barCode,'B2' name union 6 select 5 as id ,140 as barCode,'B3' name 7 ), temp2 as ( 8 select 1 as id ,112 as barCode,10 as Amount,'2013-10-20' Dates union 9 select 2 as id ,112 as barCode,20 as Amount,'2013-12-20' Dates union 10 select 3 as id ,200 as barCode,25 as Amount,'2014-01-08' Dates union 11 select 4 as id ,230 as barCode,30 as Amount,'2014-03-15' Dates union 12 select 5 as id ,112 as barCode,40 as Amount,'2014-08-7' Dates union 13 select 5 as id ,230 as barCode,70 as Amount,'2014-09-01' Dates 14 15 ) 16 17 18 19 20 /* 21 select t2.barCode,t1.name,t2.Amount,t2.dates 22 from temp2 t2 inner join temp1 t1 on t2.barCode=t1.barCode 23 where t1.name like 'B%' and DATES >'2014-01-01' ; 24 */ 25 26 /* 27 select t2.barCode,t1.name,sum(t2.amount) cnt 28 from temp2 t2 left join temp1 t1 on t2.barCode=t1.barCode 29 group by t2.barcode 30 */ 31 32 /* 33 select t2.barCode,t1.name,count(1) cnt 34 from temp2 t2 left join temp1 t1 on t2.barCode=t1.barCode 35 group by t2.barcode 36 having count(t2.barCode)>1 37 */
