sql语句的多种写法

最新推荐文章于 2021-10-27 21:29:32 发布

JCJC错别字检测-田春峰

最新推荐文章于 2021-10-27 21:29:32 发布

阅读量3k

点赞数

CC 4.0 BY-SA版权

文章标签： sql table oracle scroll function query

本文链接：https://blog.youkuaiyun.com/accesine960/article/details/35541

DB 专栏收录该内容

6 篇文章

订阅专栏

博客介绍了精妙SQL语句，包括Oracle Union All Query根据指定ID返回父级记录，以及将DETAIL表数据汇总插入TOTAL表的多种写法。还讲解了如何使用Hint提示，如在select/delete/update后写/*+ hint */，并说明了相关注意事项。

sql formatter 1 2

精妙SQL语句

Bill Gates 眼中的聪明人
不可不看：人生十二个经典的感悟

精妙SQL语句介绍

Oracle Union All Query

根据指定ID，返回包含该ID的所有父级记录

一个DETAIL 表，
item ,empid, money
1       001    100
2       001     150
...    ...     ...

一个 TOTAL 表，
empid , money
目的是想把DETAIL 表的每个empid 的数据汇总插入到TOTAL 表中，
写的语句是这样地，
写法1：
update total a set
money=(select sum(nvl(money,0))
from detail where a.empid= empid group by
empid )

写法2：（有些不通用）
drop table total;
create table total as select empid,sum(nvl(money,0)) money from detail group by empid;
写法3：
update total a set
money=(select sum(nvl(money,0))
from detail where a.empid= empid )
写法4：
update total a set
money=(select sum(nvl(b.money,0))
from detail b where a.empid=b.empid )
where exists (select 1 from detail b where a.empid=b.empid );

Q]如何使用Hint提示
[A] 在select/delete/update后写/*+ hint */
如 select /*+ index(TABLE_NAME INDEX_NAME) */ col1...
注意/*和+之间不能有空格
如用hint指定使用某个索引

select /*+ index(cbotab) */ col1 from cbotab;
select /*+ index(cbotab cbotab1) */ col1 from cbotab;
select /*+ index(a cbotab1) */ col1 from cbotab a;
其中
TABLE_NAME是必须要写的，且如果在查询中使用了表的别名，在hint也要用表的别名来代替表名;
INDEX_NAME可以不必写，Oracle会根据统计值选一个索引;
如果索引名或表名写错了，那这个hint就会被忽略;

CREATE PROCEDURE dbo.PostGetPostByPage

(
  @page int,
  @forumid int,
  @topornot int
)

AS
/* SET NOCOUNT ON */
declare @begin int,@end int,@f int,@l int,@count int,@top int

select @top=count(*) from Posts as p1 where p1.PostID=(select min(PostID) from Posts as p2 where p1.ThreadID=p2.ThreadID)
and p1.ForumID=@forumid and PostType=4

if @topornot=1

select p1.PostType,p1.Title,p1.UserName,p1.TotalViews,p1.PostID,p1.ThreadID,p1.ForumID,FileName,
Reply=(select Count(*) from Posts as p2 where p1.ThreadID=p2.ThreadID)-1,
LastDate=(select Max(PostDate)from Posts as p2 where p1.ThreadID=p2.ThreadID),
LastWriter=(select UserName from posts as p2 where p2.PostID=(select Max(PostID)from Posts as p2 where p1.ThreadID=p2.ThreadID))
from Posts as p1 where p1.PostID=(select min(PostID) from Posts as p2 where p1.ThreadID=p2.ThreadID)
and p1.ForumID=@forumid and PostType=4

else if @topornot=2
begin

select @count=count(*) from Posts as p1 where p1.PostID=(select min(PostID) from Posts as p2 where p1.ThreadID=p2.ThreadID)
and p1.ForumID=@forumid and PostType<>4

declare my_cursor SCROLL CURSOR for
select p1.PostID from Posts as p1 where p1.PostID=(select min(PostID) from Posts as p2 where p1.ThreadID=p2.ThreadID)
and p1.ForumID=@forumid and PostType<>4 order by (select max(PostID) from Posts as p2 where p1.ThreadID=p2.ThreadID) desc
open my_cursor

if @count+@top<25 and @page=1
begin
select @f=1
select @l=@count
end
if @count+@top>=25 and @page=1
begin
select @f=1
select @l=25-@top
end

if(@page*25-@top>@count) and @page>1
begin
select @f=(@page-1)*25+1-@top
select @l=@count
end
if(@page*25-@top<=@count) and @page>1
begin
select @f=(@page-1)*25+1-@top
select @l=@page*25-@top
end

fetch absolute @f from my_cursor into @begin
fetch absolute @l from my_cursor into @end
set nocount off

select p1.PostType,p1.Title,p1.UserName,p1.TotalViews,p1.PostID,p1.ThreadID,p1.ForumID,FileName,
Reply=(select Count(*) from Posts as p2 where p1.ThreadID=p2.ThreadID)-1,
LastDate=(select Max(PostDate)from Posts as p2 where p1.ThreadID=p2.ThreadID),
LastWriter=(select UserName from posts as p2 where p2.PostID=(select Max(PostID)from Posts as p2 where p1.ThreadID=p2.ThreadID))
from Posts as p1 where p1.PostID=(select min(PostID) from Posts as p2 where p1.ThreadID=p2.ThreadID)
and p1.ForumID=@forumid and PostID<=@begin and PostID>=@end and PostType<>4 order by (select max(PostID) from Posts as p2 where p1.ThreadID=p2.ThreadID) desc

close my_cursor
end
RETURN @@Rowcount

----------

以下为一数据内容:

字段: 员工卡号(nvarchar) 打卡日期(smalldatetime) 打卡时间(smalldatetime)
          687623            2004-5-26              2004-5-26 7:29:00
          687623            2004-5-26              2004-5-26 11:5:00
          687623            2004-5-26              2004-5-26 13:31:00
          687623            2004-5-26              2004-5-26 17:33:00
          687244            2004-5-26              2004-5-26 7:35:00
          687244            2004-5-26              2004-5-26 11:1:00
          687244            2004-5-26              2004-5-26 13:28:00
          687244            2004-5-26              2004-5-26 17:24:00
            :                   :                         :
            :                   :                         :
            :                   :                         :
需达到以下效果:

         员工卡号            打卡日期            打卡明细时间
          687623            2004-5-26     7:29 11:5 13:31 17:33
          687244            2004-5-26     7:35 11:1 13:28 17:24

其中, 打卡明细时间由上表的打卡时间而来

--创建一个合并处理函数(生成打卡明细时间)
create function f_time(
@员工卡号 nvarchar(6),
@打卡日期 smalldatetime
)returns varchar(8000)
as
begin
declare @re varchar(8000)
set @re=''
select @re=@re+' '+convert(char(5),打卡时间,108)
from 数据表
where 员工卡号=@员工卡号 and 打卡日期=@打卡日期
return(stuff(@re,1,1,''))
end
go

--调用函数实现查询
select 员工卡号,打卡日期,打卡明细时间=dbo.f_time(员工卡号,打卡日期)
from 数据表
group by 员工卡号,打卡日期