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

  1. –482, ORACLE / SQL SERVER
  2. –订购数量超过平均值的书籍
  3. WITH Orders_Book
  4. AS
  5.   (
  6.   SELECT Book_Name, SUM(Qty) Book_Qty
  7.   FROM Orders
  8.   GROUP BY Book_Name
  9.   )
  10. SELECT *
  11. FROM Orders_Book
  12. WHERE Book_Qty >
  13.       (
  14.       SELECT AVG(Book_Qty)
  15.       FROM Orders_Book
  16.       )
  17. –递归 产生连续数列1至10000
  18. WITH Tally(N)
  19. AS
  20.   (
  21.   SELECT 1 N
  22.   –FROM DAUL       — ORACLE
  23.   UNION ALL
  24.   –2.递归区块
  25.   SELECT N+1
  26.   FROM Tally
  27.   WHERE N<=10000
  28.   )
  29.   SELECT N
  30. FROM TALLY
  31. OPTION (MAXRECURSION 10000)  –SQL SERVER设定深度
  32. –490, SQL SERVER
  33. –随机抽出3笔员工数据
  34. SELECT TOP 3
  35.     E.Emp_Id
  36.     , E.Emp_Name
  37.     , E.Dept_Id
  38. FROM Employees E
  39. ORDER BY NEWID()
  40. –491, SQL SERVER
  41. –在I100和I200部门中随机抽出一名员工(子分组中各抽出N笔)
  42. SELECT E.Emp_Id
  43.        , E.Emp_Name
  44.        , E.Dept_Id
  45. FROM
  46.   (
  47.   SELECT Emp_Id, Emp_Name, Dept_Id
  48.          , ROW_NUMBER() OVER (PARTITION BY Dept_Id
  49.                                       ORDER BY NEWID()) RowNo
  50.   FROM Employees
  51.   WHERE Dept_Id IN (‘I100’, ‘I200’)
  52.   ) E
  53. WHERE E.RowNo <=1
  54. –492, ORACLE
  55. –随机抽出3笔员工数据
  56. SELECT Emp_Id
  57.     , Emp_Name
  58.     , Dept_Id
  59. FROM
  60.   (
  61.     SELECT *
  62.     FROM Employees
  63.     ORDER BY DBMS_RANDOM.VALUE()
  64.   )
  65. WHERE ROWNUM<=3
  66. –493, ORACLE
  67. –在I100和I200部门中随机抽出一名员工(子分组中各抽出N笔)
  68. SELECT E.Emp_Id
  69.        , E.Emp_Name
  70.        , E.Dept_Id
  71. FROM
  72. (
  73. SELECT Emp_Id, Emp_Name, Dept_Id
  74.    , ROW_NUMBER()
  75.          OVER (PARTITION BY Dept_Id
  76.                ORDER BY DBMS_RANDOM.VALUE()) RowNo
  77.    FROM Employees
  78.    WHERE Dept_Id IN (‘I100’, ‘I200’)
  79.    ) E
  80. WHERE E.RowNo <=1
  81. –495, SQL SERVER
  82. –以符号分割的字符串 分拆成table返回,含一字段 Column_Value
  83. create function [dbo].[m_split](@c varchar(2000),@split varchar(2))
  84.     returns @t table(col varchar(200))
  85. as
  86. begin
  87.       while(charindex(@split,@c)<>0)
  88.         begin
  89.           insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))
  90.           set @c = stuff(@c,1,charindex(@split,@c),”)
  91.         end
  92.       insert @t(col) values (@c)
  93.       return
  94. end
  95. –测试
  96. select * from [dbo].[m_split](‘1,2,3’, ‘,’)
  97. –496, ORACLE
  98. –以符号分割的字符串 分拆成table返回,含一字段 Column_Value
  99. CREATE OR REPLACE TYPE split_tbl AS TABLE OF VARCHAR2(32767);
  100. –测试1
  101. SELECT Column_Value
  102. FROM TABLE(Split_Tbl(1,2,3))
  103. –测试2
  104. SELECT Column_Value
  105. FROM TABLE(Split_Tbl(‘A’,’B’,’C’))

标签