SqlServer和Oracle中一些常用的sql语句10 特殊应用

--482, ORACLE / SQL SERVER
--订购数量超过平均值的书籍
WITH Orders_Book
AS
  (
  SELECT Book_Name, SUM(Qty) Book_Qty
  FROM Orders
  GROUP BY Book_Name
  )
SELECT *
FROM Orders_Book
WHERE Book_Qty >
      (
      SELECT AVG(Book_Qty)
      FROM Orders_Book
      )

--递归 产生连续数列1至10000  
WITH Tally(N)
AS
  (
  SELECT 1 N       
  --FROM DAUL       -- ORACLE
  UNION ALL        
  --2.递归区块
  SELECT N+1     
  FROM Tally        
  WHERE N<=10000  
  ) 
  SELECT N
FROM TALLY
OPTION (MAXRECURSION 10000)  --SQL SERVER设定深度    

--490, SQL SERVER
--随机抽出3笔员工数据
SELECT TOP 3 
    E.Emp_Id
    , E.Emp_Name
    , E.Dept_Id
FROM Employees E
ORDER BY NEWID()       


--491, SQL SERVER
--在I100和I200部门中随机抽出一名员工(子分组中各抽出N笔)
SELECT E.Emp_Id  
       , E.Emp_Name    
       , E.Dept_Id  
FROM 
  (
  SELECT Emp_Id, Emp_Name, Dept_Id
         , ROW_NUMBER() OVER (PARTITION BY Dept_Id 
                                      ORDER BY NEWID()) RowNo
  FROM Employees
  WHERE Dept_Id IN ('I100', 'I200')
  ) E
WHERE E.RowNo <=1 


--492, ORACLE
--随机抽出3笔员工数据
SELECT Emp_Id
    , Emp_Name
    , Dept_Id
FROM
  (
    SELECT *
    FROM Employees
    ORDER BY DBMS_RANDOM.VALUE()
  )
WHERE ROWNUM<=3 

--493, ORACLE
--在I100和I200部门中随机抽出一名员工(子分组中各抽出N笔)
SELECT E.Emp_Id 
       , E.Emp_Name 
       , E.Dept_Id 
FROM 
(
SELECT Emp_Id, Emp_Name, Dept_Id
   , ROW_NUMBER() 
         OVER (PARTITION BY Dept_Id 
               ORDER BY DBMS_RANDOM.VALUE()) RowNo
   FROM Employees
   WHERE Dept_Id IN ('I100', 'I200')
   ) E
WHERE E.RowNo <=1 


--495, SQL SERVER
--以符号分割的字符串 分拆成table返回,含一字段 Column_Value
create function [dbo].[m_split](@c varchar(2000),@split varchar(2))  
    returns @t table(col varchar(200))  
as  
begin  
      while(charindex(@split,@c)<>0)  
        begin  
          insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))  
          set @c = stuff(@c,1,charindex(@split,@c),'')  
        end  
      insert @t(col) values (@c)  
      return  
end

--测试
select * from [dbo].[m_split]('1,2,3', ',')



--496, ORACLE
--以符号分割的字符串 分拆成table返回,含一字段 Column_Value
CREATE OR REPLACE TYPE split_tbl AS TABLE OF VARCHAR2(32767);

--测试1
SELECT Column_Value
FROM TABLE(Split_Tbl(1,2,3))

--测试2
SELECT Column_Value
FROM TABLE(Split_Tbl('A','B','C'))