SELECT操作语句集

最新推荐文章于 2023-06-30 20:40:12 发布

aqbeyond

最新推荐文章于 2023-06-30 20:40:12 发布

阅读量413

点赞数

分类专栏： SQL语句专题文章标签： insert date table c sql null

SQL语句专题专栏收录该内容

6 篇文章 0 订阅

订阅专栏

1.指定字符串匹配检索(charindex).
2.相同ID的VALUE值合并的实现.
3.游标的使用(数据库字段的拆分)
4.游标的使用(实现数据简单逻辑计算显示)
5.用CASE+GROUP BY实现数据分列显示.
6.当月最大天数的取得方法(使用DATEADD方法).
7.取得行号的方法
8.对datetime类型进行GROUP　ＢＹ操作，实现两个表的特定项统计．

9.获取本周所属日期的开始日期和结束日期。

1.指定字符串匹配检索(charindex).
select * from t1 where charindex(','+'55'+',',value) > 0

2.相同ID的VALUE值合并的实现.
A.创建表:create table t(id varchar(10),name varchar(10))
                 insert t select '1000','李梅'
                 union all select '1000','王俊'
                 union all select '1000','張風'
                 union all select '1001','嘩啦啦'
                 union all select '1001','王俊'
B.创建一个合并的函数
create function f_hb(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + cast(name as varchar) from tb where id = @id
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
C.SQL实现:select id, dbo.aa(id) as name from t group by id

3.游标的使用(数据库字段的拆分)
方法一:
            create table tc(name varchar(10),sss varchar(30))
insert into tc select 'dd','sd,fdf,999'
insert into tc select 'tt','sdf,ew'
--先建立一个表变量T
select id=identity(int,1,1) into t from sysobjects a,sysobjects b
select name,sss=substring(sss+',',b.id,charindex(',',sss+',',b.id)-b.id) from tc a,t b
where substring(','+sss,b.id,1)=','
name       sss
---------- -------------------------------
dd         sd
dd         fdf
dd         999
tt         sdf
tt         ew
(5 行受影响)
方法二:用游标法：
declare @name varchar(20),@sql varchar(20)
declare @t table(name varchar(10),sss varchar(10))
declare roy cursor for select name,sss from tc

open roy

fetch next from roy into @name,@sql
while @@fetch_status=0
begin

while charindex(',',@sql)>0
begin
insert into @t select @name,substring(@sql,1,charindex(',',@sql)-1)
set @sql=stuff(@sql,1,charindex(',',@sql),'')
end
insert into @t select @name,@sql

fetch next from roy into @name,@sql

end

close roy
deallocate roy
select * from @t

4.游标的使用(实现数据简单逻辑计算显示)
将以下数据
C1    C2    C3     ShouDaoTotal
--------------------------------
50    20    60     75
60    50   10     110
变成
C1    C2    C3     ShouDaoTotal
--------------------------------
0     0    55     0
0     0   10      0
即ShouDaoTotal=C1+C2+C3-ShouDaoTotal
A.建表count1
CREATE TABLE count1(C1 int,C2 int,C3 int,ShouDaoTotal int)
INSERT INTO count1
SELECT 50 , 20 , 60   , 75 UNION ALL
SELECT 60 , 50 , 10 ,   110
B.建PROCEDURE
CREATE PROCEDURE GetData
as
declare @c1 int
declare @c2 int
declare @c3 int
declare @ShouDaoTotal int
declare @t table(c1 int,c2 int,c3 int,ShouDaoTotal int)
declare roy cursor for select c1,c2,c3,ShouDaoTotal from count1

open roy

fetch next from roy into @c1,@c2,@c3,@ShouDaoTotal
while @@fetch_status=0
begin

if @ShouDaoTotal > @c1
begin
  set @ShouDaoTotal=@ShouDaoTotal-@c1 set @c1=0
end
else
begin
   set @c1=@c1-@ShouDaoTotal set @ShouDaoTotal=0
end
if @ShouDaoTotal > @c2
begin
  set @ShouDaoTotal=@ShouDaoTotal-@c2 set @c2=0
end
else
begin
  set @c2=@c2-@ShouDaoTotal set @ShouDaoTotal=0
end
if @ShouDaoTotal > @c3
begin
   set @ShouDaoTotal=@ShouDaoTotal-@c3 set @c3=0
end
else
begin
   set @c3=@c3-@ShouDaoTotal set @ShouDaoTotal=0
end
insert into @t select @c1,@c2,@c3,@ShouDaoTotal
fetch next from roy into @c1,@c2,@c3,@ShouDaoTotal
end

close roy
deallocate roy
select * from @t
C.执行exec GetData
结果:
C1    C2    C3     ShouDaoTotal
--------------------------------
0     0    55     0
0     0   10      0

5.用CASE+GROUP BY实现数据分列显示.
A.初期数据:
Code Time Value
01 070713 100
02 070713 110
01 070714 120
01 070715 130
02 070715 140
处理后显示数据:
Time Code_01 Code_02
070713 100 110
070714 120 NULL
070715 130 140
B.建表:
CREATE TABLE TEST(Code varchar(2),Time varchar(6),Value varchar(10))
INSERT INTO TEST
SELECT '01','070713','100' UNION ALL
SELECT '02','070713','110' UNION ALL
SELECT '01','070714','120' UNION ALL
SELECT '01','070715','130' UNION ALL
SELECT '02','070715','140'
C.SQL文实现:
SELECT Time,
SUM(CASE Code WHEN '01' THEN CAST(value AS INT) ELSE NULL END) AS Code_01,
SUM(CASE Code WHEN '02' THEN CAST(value AS INT) ELSE NULL END) AS Code_02
FROM Test
GROUP BY Time

6.当月最大天数的取得方法(使用DATEADD方法).
SELECT DAY(DATEADD(dd, -1, DATEADD(mm, 1, DATEADD(dd, 1-DAY('2004-2-01'), '2004-2-01')))) AS 'Day Number'

7.取得行号的方法
create table #A(a varchar(3),B varchar(1),c int,d varchar(15))
INSERT INTO #A
SELECT '100','x',32,'A-20070820-0001' UNION ALL
SELECT '100','y',31,'A-20070820-0001' UNION ALL
SELECT '324','z',35,'A-20070820-0004'

SELECT IDENTITY(int, 1,1) as 行号,* INTO #B FROM #A
SELECT * FROM #B
DROP TABLE #A,#B

8.对datetime类型进行GROUP　ＢＹ操作，实现两个表的特定项统计．
create table #T1(ID int,Date smalldatetime,加班 decimal(18,2))
insert #T1(ID,Date,加班)
select '1','2007-07-01','5' union all
select '2','2007-07-15','5' union all
select '1','2007-07-15','6' union all
select '1','2007-07-20','4' union all
select '2','2007-07-20','8'

create table #T2(ID int,Date smalldatetime,請假 decimal(18,2))
insert #T2(ID,Date,請假)
select '1','2007-07-05','10' union all
select '2','2007-08-22','10' union all
select '1','2007-07-8','2' union all
select '2','2007-07-21','10' union all
select '2','2007-07-22','5'

select ID,date,sum(TIME) TIME from (
select ID,convert(varchar(7),date,120) Date,SUM(加班) as TIME from #T1 group by (convert(varchar(7),date,120)),ID
union all
select ID,convert(varchar(7),date,120) Date,SUM(請假)*-1 as TIME from #T2 group by (convert(varchar(7),date,120)),ID
) as ttt
group by (date),ID

DROP TABLE #T1,#T2

9.获取本周所属日期的开始日期和结束日期。
select GETDATE()-DATEPART(WEEKDAY,GETDATE()+@@datefirst-1)+1
, GETDATE()-DATEPART(WEEKDAY,GETDATE()+@@datefirst-1)+7