Sql疑难问题

select ROW_NUMBER() OVER(ORDER BY id DESC) AS 'Row Number',emp_no,emp_name from employees


DECLARE
v_Count INT;
BEGIN
SELECT COUNT(USER_ID) INTO v_Count FROM USER_INFORMATION WHERE USER_ID=:NEW.WORK_NO;
IF (v_Count=0) THEN
INSERT INTO USER_INFORMATION(USER_ID,USER_NAME,DEPT_CODE,DEPT_NAME)
VALUES(:NEW.WORK_NO,:NEW.WORK_NAME,:NEW.DEPT_CODE,:NEW.DEPT_NAME);
END IF;
END;
/
插入一个表的同时，向另一个表插入记录。
2010-5-10 春 晴
NAME TYPE WEIGHT
1 A 50
1 B 30
1 C 40
1 D 60
select name,WMSYS.WM_CONCAT(type) AS ENAMES from aa group by name
运行结果：
NAME TYPE
1 B,C,A,D
注意：WMSYS.WM_CONCAT函数为oracle数据库特有的。

Oracle特有的运算符,decode,Rollup、Cube 运运符是 GROUP BY 子句的扩充。Rollup、Cube 运算符可以在GROUP BY的基础上再产生,行/列统计信息与全表汇总信息。
//Rollup语法
SELECT [column,] group_function(column) FROM table
[WHERE condition] [GROUP BY [Rollup] group_by_expression]
[HAVING having_expression]; [ORDER BY column];
//cube语法
SELECT [column,] group_function(column) FROM table
[WHERE condition] [GROUP BY [CUBE] group_by_expression]
[HAVING having_expression]; [ORDER BY column];


//按部门,职务求部门,岗位平均工资并计算各部门平均工资与总平均工资
select dept,job,avg(salary) from salary group by rollup(dept,job)
//按部门,职务求部门,岗位平均工资并计算各部门平均工资、岗位平均工资以及总平均工资
select dept,job,avg(salary) from salary group by cube(dept,job)
//通过对Dept字段进行 “分区汇总”得到个人信息明细与每个部门salary的汇总信息
select name,dept,salary,sum(salary) over (partition by dept) total_salary from salary
//个人salary除以部门salary的汇总得到所占比例
select name,dept,salary,sum(salary) over (partition by dept) total_salary,salary*100/sum(salary) over (partition by dept) percent from salary
//双胞胎年龄的计算
数学家X: 你有几个孩子？
数学家Y: 我有三个女儿。
数学家X: 她们有多大？
数学家Y:如果你把她们的年龄相乘，那积是36。
数学家X:这些信息还不太够。
数学家Y:如果你把她们年龄相加，你会发现年龄之和与这个
房间的人数相等。
数学家X 环顾房子一周后又说:这些信息还不够。
数学家Y: 我最大的女儿有一个木腿宠物老鼠。
数学家X:你有一对2岁的双胞胎吗？

with age_list as (
select rownum age
from all_objects
where rownum <= 36),
product_check as (
select
age1.age as youngest,
age2.age as middle,
age3.age as oldest,
age1.age + age2.age + age3.age as summed,
age1.age * age2.age * age3.age as product
from
age_list age1,
age_list age2,
age_list age3
where
age2.age>=age1.age and
age3.age>=age2.age
and age1.age * age2.age * age3.age = 36),
summed_check as (select youngest,middle,oldest,summed,product from (select youngest,middle,oldest,summed,product,
count(*) over (partition by summed) ct from product_check) where ct>1)
select * from summed_check where oldest>middle;

//查询SQL语句执行速度，适用于SQL server数据库
在各个select语句前加：
declare @d datetime
set @d=getdate()
并在select语句后加：
select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())


比如要找某个时间为每周第几天就可以
SQL> select to_char(to_date('20070101','yyyymmdd'),'d') from dual;
Oracle的SQL采用了mi代替分钟。select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss') from dual;
2.另要以24小时的形式显示出来要用HH24
select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;//mi是分钟
select to_char(sysdate,'yyyy-MM-dd HH24:mm:ss') from dual;//mm会显示月份
注意：select chr(ascii(max(version))+1) from table 改变版本号，例如A变成B
//sqlserver触发器

ALTER TRIGGER [dbo].[TrigCategoryDelete]
ON [dbo].[category]
instead of delete
AS
BEGIN
declare @CaId int
select @CaId=id from deleted
delete comment where newsid in (select id from news where caid=@CaId)
delete from news where caid=@CaId
delete from category where id=@CaId
END