SQL開源替代品，誕生了！

現(xiàn)在我們想知道出空調(diào)和電視銷售額都在前 10 名的銷售員名單。

1． 按空調(diào)銷售額排序，找出前 10 名；

2． 按電視銷售額排序，找出前 10 名；

select * from     ( select top 10 sales from sales_amount where product='AC' order by amount desc )intersect     ( select top 10 sales from sales_amount where product='TV' order by amount desc )

with A as       select top 10 sales from sales_amount where product='AC' order by amount desc     B as       select top 10 sales from sales_amount where product='TV' order by amount descselect * from A intersect B

句子沒有更短，但分步后思路確實變清晰了。

1.   列出所有產(chǎn)品；

2．將每種產(chǎn)品的前 10 名取出，分別保存；

1．將數(shù)據(jù)按產(chǎn)品分組，將每組排序，取出前 10 名；

select salesfrom ( select sales,     from ( select sales,                   rank() over (partition by product order by amount desc ) ranking            from sales_amount)     where ranking <=10 )group by saleshaving count(*)=(select count(distinct product) from sales_amount)

這是能寫出來，但這樣復(fù)雜的 SQL，有多少人會寫呢？

select salesfrom ( select A.sales sales, A.product product,             (select count(*)+1 from sales_amount              where A.product=product AND A.amount<=amount) ranking       from sales_amount A )where product='AC' AND ranking<=10

select salesfrom ( select A.sales sales, A.product product, count(*)+1 ranking       from sales_amount A, sales_amount B       where A.sales=B.sales and A.product=B.product AND A.amount<=B.amount       group by A.sales,A.product )where product='AC' AND ranking<=10

這樣的 SQL 語句，專業(yè)程序員寫出來也未必容易吧！而僅僅是計算了一個前 10 名。

我們已經(jīng)計算出“好”銷售員的名單，比較自然的想法，是用名單到花名冊時找出其性別，再計一下數(shù)。但在 SQL 中要跨表獲得信息需要用表間連接，這樣，接著最初的結(jié)果，SQL 就會寫成：

select employee.gender,count(*)from employee,    ( ( select top 10 sales from sales_amount where product='AC' order by amount desc )    intersect    ( select top 10 sales from sales_amount where product='TV' order by amount desc ) ) Awhere A.sales=employee.namegroup by employee.gender

select sales.gender,count(*)from (…) // …是前面計算“好”銷售員的SQLgroup by sales.gender

顯然，這個句子不僅更清晰，同時計算效率也會更高（沒有連接計算）。

mov ax,3mov bx,5mul bx,7add ax,bx

這樣的代碼無論書寫還是閱讀都遠不如 3+5*7 了（要是碰到小數(shù)就更要命了）。雖然對于熟練的程序員也算不了太大的麻煩，但對于大多數(shù)人而言，這種寫法還是過于晦澀難懂了，從這個意義上講，F(xiàn)ORTRAN 確實是個偉大的發(fā)明。

集合無序

   
   

    
   
   
   
   

    
    

     
     

     
     

     
     

     
     

    
    
    
    
select name, birthdayfrom (select name, birthday, row_number() over (order by birthday) ranking      from employee )where ranking=(select floor((count(*)+1)/2) from employee)
   
   

   
   

    
    

     
     

     
     

     
     

     
     

     
     

     
     

     
     

     
     

     
     

    
    
    
    
select max (consecutive_day)from (select count(*) (consecutive_day      from (select sum(rise_mark) over(order by trade_date) days_no_gain            from (select trade_date,                         case when                              closing_price>lag(closing_price) over(order by trade_date)                         then 0 else 1 END rise_mark                from stock_price) )     group by days_no_gain)
   
   

集合化不徹底

   
   

    
    

     
     

     
     

     
     

     
     

     
     

    
    
    
    
select * from employeewhere to_char (birthday, ‘MMDD’) in    ( select to_char(birthday, 'MMDD') from employee      group by to_char(birthday, 'MMDD')      having count(*)>1 )
   
   

   
   

    
    

     
     

     
     

     
     

     
     

     
     

     
     

     
     

     
     

    
    
    
    
select namefrom (select name      from (select name,                   rank() over(partition by subject order by score DESC) ranking            from score_table)      where ranking<=10)group by namehaving count(*)=(select count(distinct subject) from score_table)
   
   

缺乏對象引用

   
   

    
    

     
     

     
     

     
     

     
     

    
    
    
    
select A.*from employee A, department B, employee Cwhere A.department=B.department and B.manager=C.name and      A.gender='male' and C.gender='female'
   
   

   
   

    
    

     
     

     
     

     
     

     
     

     
     

    
    
    
    
select * from employeewhere gender='male' and department in    (select department from department     where manager in          (select name from employee where gender='female'))
   
   

   
   

    
    

     
     

    
    
    
    
where gender='male' and department.manager.gender='female'
   
   

   
   

    
    

     
     

     
     

     
     

     
     

     
     

     
     

    
    
    
    
select name, company, first_companyfrom (select employee.name name, resume.company company,             row_number() over(partition by resume. name                               order by resume.start_date) work_seq      from employee, resume where employee.name = resume.name)where work_seq=1
   
   

   
   

    
    

     
     

     
     

     
     

     
     

     
     

     
     

    
    
    
    
select name,    (select company from resume     where name=A.name and           start date=(select min(start_date) from resume                       where name=A.name)) first_companyfrom employee A
   
   

對于以有序集合為基礎(chǔ)的 SPL 來說，按位置取值是個很簡單的任務(wù)。

 任務(wù) 2   

SPL 按自然的思路過程編寫計算代碼即可。

 任務(wù) 3   

SPL 可以保存分組結(jié)果集，繼續(xù)處理就和常規(guī)集合一樣。

 任務(wù) 4   

使用 SPL 只要按思路過程寫出計算代碼即可。

 任務(wù) 5   

支持對象引用的 SPL 可以簡單地將外鍵指向記錄的字段當作自己的屬性訪問。

 任務(wù) 6   

SPL 支持將子表集合作為主表字段，就如同訪問其它字段一樣，子表無需重復(fù)計算。

…Class.forName("com.esproc.jdbc.InternalDriver");Connection conn =DriverManager.getConnection("jdbc:esproc:local://");Statement st = connection.();CallableStatement st = conn.prepareCall("{call xxxx(?,?)}");st.setObject(1, 3000);st.setObject(2, 5000);ResultSet result=st.execute();...

GitHub：https://github.com/SPLWare/esProc

重磅！開源SPL交流群成立了

簡單好用的SPL開源啦！

為了給感興趣的小伙伴們提供一個相互交流的平臺，

特地開通了交流群（群完全免費，不廣告不賣課）

需要進群的朋友，可長按掃描下方二維碼