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

--482, ORACLE / SQL SERVER--订购数量超过平均值的书籍WITH Orders_BookAS  (  SELECT Book_Name, SUM(Qty) Book_Qty  FROM Orders  GROUP BY Book_Name  )SELECT *FROM Orders_BookWHERE 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 NFROM TALLYOPTION (MAXRECURSION 10000)  --SQL SERVER设定深度    --490, SQL SERVER--随机抽出3笔员工数据SELECT TOP 3     E.Emp_Id    , E.Emp_Name    , E.Dept_IdFROM Employees EORDER 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')  ) EWHERE E.RowNo <=1 --492, ORACLE--随机抽出3笔员工数据SELECT Emp_Id    , Emp_Name    , Dept_IdFROM  (    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')   ) EWHERE E.RowNo <=1 --495, SQL SERVER--以符号分割的字符串 分拆成table返回,含一字段 Column_Valuecreate 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_ValueCREATE OR REPLACE TYPE split_tbl AS TABLE OF VARCHAR2(32767);--测试1SELECT Column_ValueFROM TABLE(Split_Tbl(1,2,3))--测试2SELECT Column_ValueFROM TABLE(Split_Tbl('A','B','C'))