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 >

--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.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,Dept_IDFROM  (    SELECT *    FROM Employees    ORDER BY DBMS_RANDOM.VALUE()  )WHERE ROWNUM<=3 --493,ORACLE--在I100和I200部门中随机抽出一名员工(子分组中各抽出N笔)SELECT E.Emp_ID,E.Dept_ID FROM (SELECT Emp_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,@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,3))--测试2SELECT Column_ValueFROM table(Split_Tbl('A','B','C'))    

总结

以上是内存溢出为你收集整理的SqlServer和Oracle中一些常用的sql语句10 特殊应用全部内容，希望文章能够帮你解决SqlServer和Oracle中一些常用的sql语句10 特殊应用所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。