[Shell] AWK實現SQL的功能(1)

原始資料:
[/tmp/test]# cat emp
7369    SMITH   CLERK           7902    1980-12-17      800     0       20
7499    ALLEN   SALESMAN        7698    1981-02-20      1600    300     30
7521    WARD    SALESMAN        7698    1981-02-22      1250    500     30
7566    JONES   MANAGER         7839    1981-04-02      2975    0       20
7654    MARTIN  SALESMAN        7698    1981-09-28      1250    1400    30
7698    BLAKE   MANAGER         7839    1981-05-01      2850    0       30
7782    CLARK   MANAGER         7839    1981-06-09      2450    0       10
7788    SCOTT   ANALYST         7566    1987-04-19      3000    0       20
7839    KING    PRESIDENT       7839    1981-11-17      5000    0       10
7844    TURNER  SALESMAN        7698    1981-09-08      1500    0       30
7876    ADAMS   CLERK           7788    1987-05-23      1100    0       20
7900    JAMES   CLERK           7698    1981-12-03      950     0       30
7902    FORD    ANALYST         7566    1981-12-03      3000    0       20
7934    MILLER  CLERK           7782    1982-01-23      1300    0       10
[/tmp/test]# cat dept
10      ACCOUNTING      NEW YORK
20      RESEARCH        DALLAS
30      SALES           CHICAGO
40      OPERATIONS      BOSTON


1.select * from emp where sal>2000
[/tmp/test]# cat emp | awk '$6>2000'

2.select distinct job from emp
[/tmp/test]# cat emp | awk '{print $3}' | sort -u  
[/tmp/test]# cat emp | awk '!a[$3]++{print $3}'
[/tmp/test]# cat emp | awk '{a[$3]=$3}END{for(i in a) print a[i]}'

3.select * from emp order by sal
[/tmp/test]# cat emp | sort -nk6s
[/tmp/test]# cat emp | awk '{a[$6]}END{asorti(a);for(i=1;i<=length(a);i++){print a[i]}}'    ##這個需要驗證.

4.select * from emp where rownum<3
[/tmp/test]# cat emp | awk 'NR<3';

5.group by,having,sum,count
select job,count(*),sum(sal) from emp group by job
[/tmp/test]# cat emp | awk '{a[$3]+=$6;b[$3]+=1}END{for(i in a) printf "%-10s%-4s%-10s\n", i,b[i],a[i]}'  

select job,count(1),sum(sal) from emp group by job having count(1)>2
[/tmp/test]# cat emp | awk '{a[$3]+=$6;b[$3]+=1}END{for(i in a) if(b[i]>2){printf "%-10s%-4s%-10s\n", i,b[i],a[i]}}'

6.like
select * from emp where name like 'W%'
[/tmp/test]# cat emp |  awk '$2~/^W/'

select * from emp where name like '%N'
[/tmp/test]# cat emp |  awk '$2~/N$/'

select * from emp where job like '%CLERK%'
[/tmp/test]# cat emp |  awk '$3~/CLERK/'

7.join
select * from emp,dept where emp.deptno=dept.no
[/tmp/test]# awk 'FNR==NR{a[$1]=$2"  "$3;next}{if(a[$8]) print $0,a[$8]}' dept emp

select * from emp,dept where emp.deptno=dept.no and dept.no=20
[/tmp/test]# awk 'FNR==NR{if($1=20){a[$1]=$2"        "$3};next}{if(a[$8]) print $0,a[$8]}' dept emp