--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'))