SQL语句-白红宇

SQL语句

阅读量：5138 次

发布时间：2019-06-13

本文共 11725 字，大约阅读时间需要 39 分钟。

1、将查询结果导入另一个表中

（1）、查询A表部分字段导入B表

insert into B表(字段1，字段2，字段3)

select 字段1，字段2，字段3 from A表

where ......

B表中的每个字段要和从A表中读取的字段一致。

B表中除了上边显示的字段外，还可以有其它的字段，但是要保证其它字段的属性为null，否则会报错。

（2）、A表数据全部导入B表（A、B表的所有字段完全相同，字段名可省掉）

insert into B表

select * from A表

where......

（3）、字段名不同，但字段属性一致，可以使用别名。（注意：别名的三种不同写法）

insert into B表(字段1,字段2,字段3)

select 字段a as 字段1,字段2 = 字段b,字段c 字段3

from A表

where.....

（4）、以上的三种情况都是先创建好所要插入数据的B表，也可以在插入数据时自动的创建B表

select * into B表 from A表

2、分别查出每个小组中人数最多的三个地区：

方法一：

select *

from Persons t1

where

(

select count(*)

from Persons t2

where t1.小组=t2.小组

and t2.人数>=t1.人数

) <=3

方法二：（不理解）

set statistics profile on

select 小组,地区,人数

from

(select row_number()

over(partition by 小组

order by 人数 desc) as Number,* from Persons

where T.Number<=3

3、从A表中取出第31到第40条记录：

（1）、select top 40* from A表 except select top 30* from

（2）、select top 10 * from A表 where ID not in(select top 30 ID from A表)

4、

insert into AreaDev

select c.Code area ,(select isnull(a.num,0)) num from CXFLDM c

left join areadev a on c.code=a.area

where c.code>100000 and c.code<9000000

5、

//查询出所有数据库中的所有表的列表

select * from sysobjects

//查询出所有的列名

select * from syscolumns

//查询出数据库中“subject”列名的所有表

select a.name from sysobjects a,syscolumns b where a.id = b.id and b.name = 'subject'

6、根据某个值查询出在数据库中出现过该值的某个表名和所在的列表名存储过程如下：

CREATE PROCEDURE [dbo].[SP_FindValueInDB]

(

@value VARCHAR(1024)

)

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @sql VARCHAR(1024)

DECLARE @table VARCHAR(64)

DECLARE @column VARCHAR(64)

CREATE TABLE #t (

tablename VARCHAR(64),

columnname VARCHAR(64)

)

DECLARE TABLES CURSOR

FOR

SELECT o.name, c.name

FROM syscolumns c

INNER JOIN sysobjects o ON c.id = o.id

WHERE o.type = 'U' AND c.xtype IN (167, 175, 231, 239)

ORDER BY o.name, c.name

OPEN TABLES

FETCH NEXT FROM TABLES

INTO @table, @column

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sql = 'IF EXISTS(SELECT NULL FROM [' + @table + '] '

SET @sql = @sql + 'WHERE RTRIM(LTRIM([' + @column + '])) LIKE ''%' + @value + '%'') '

SET @sql = @sql + 'INSERT INTO #t VALUES (''' + @table + ''', '''

SET @sql = @sql + @column + ''')'

EXEC(@sql)

FETCH NEXT FROM TABLES

INTO @table, @column

END

CLOSE TABLES

DEALLOCATE TABLES

SELECT *

FROM #t

DROP TABLE #t

End

EXEC [SP_FindValueInDB] '要查询某个表中的值'

百分比的表示方法：

cast(cast(分子*1.0*100/分母 as decimal(10,2)) as varchar(50)) +'%'

--获取每个月的连续日期字符串

select timesection=convert(varchar(10),dateadd(day,number,'2013-9-1'),112)

from master.dbo.spt_values

where type='P' and dateadd(day,number,'2013-9-1') <= '2013-9-30'

根据日期统计该月份每天的数据记录日期格式“yyyy/(M)M/(d)d“

Create proc P_GZLDTimeAndCountByMonth

@sDate varchar(10)

DECLARE @sql NVARCHAR(3000)

SELECT @sql='

select s1.sDate,ISNULL(s2.Area_One,0) as Area_One,ISNULL(s2.Area_Two,0) as Area_Two,ISNULL(s2.Area_Three,0) as Area_Three

from

(

select convert(varchar(100),s.s_date,112) as sDate

from(

select DATEADD(dd,number,'''+@sDate+''') as s_date

from master..spt_values

where type = ''P'' and DATEADD(dd,number,'''+@sDate+''') < DATEADD(mm,1,'''+@sDate+''')

)s)as s1

left join(

select sdate, count(case when sex=''保养一区'' then 1 end)Area_One ,

count(case when sex=''保养二区'' then 1 end) Area_Two,

count(case when sex=''保养三区'' then 1 end) Area_Three

from CRM_CASE

group by sdate

)s2

on s1.sDate=s2.sDate

EXEC (@sql)

exec P_GZLDTimeAndCountByMonth '2013/1/1'

获取某月中所有天数的记录

CREATE proc P_GZLDTimeAndCountByMonth

@sDate1 DATETIME,--当月的第一天

@sDate2 DATETIME--下月的第一天

CREATE TABLE #LDTemp_Table(s_Date varchar(100),sTime varchar(8),ACC_PLAN varchar(20),AREA varchar(50))

--- 声明一个游标

DECLARE my_Cursor CURSOR scroll FOR

--获取指定日期第一天 7点后和第二个月第一天7点之前的记录

select Convert(datetime,sdate) as sdate,stime,Acc_plan,sex

from CRM_CASE

where (

(sDate=Convert(varchar(8), @sDate1,112) and sTime>='070000')

or (sDate=Convert(varchar(8),@sDate2,112) and sTime<'070000')

or(sDate> Convert(varchar(8),@sDate1,112) and sDate< Convert(varchar(8),@sDate2,112)

)) and Acc_plan='0'

group by sdate,stime,Acc_Plan,sex

--打开游标

OPEN my_Cursor

DECLARE @s_Date datetime

DECLARE @sTime varchar(6)

DECLARE @ACC_PLAN varchar(20)

DECLARE @AREA varchar(50)

FETCH next FROM my_Cursor into @s_Date,@sTime,@ACC_PLAN,@AREA

WHILE @@FETCH_STATUS=0

BEGIN

--如果在07:00:00以后的记录则插入日期为当天，07:00:00之前的记录则插入日期为前一天

IF(@sTime>='070000')

INSERT INTO #LDTemp_Table values(@s_Date,@sTime,@ACC_PLAN,@AREA)

ELSE

INSERT INTO #LDTemp_Table values(DATEADD(DD,-1,@s_Date),@sTime,@ACC_PLAN,@AREA)

FETCH NEXT FROM my_Cursor into @s_Date,@sTime,@ACC_PLAN,@AREA

END

--关闭游标释放游标

CLOSE my_Cursor

DEALLOCATE my_Cursor

--select * from #LDTemp_Table

---联合查询列出当月每天的记录数

select substring(Convert(varchar(20),s1.sDate,112),7,2)+'号' as sDate,ISNULL(s2.Area_One,0) as Area_One,ISNULL(s2.Area_Two,0) as Area_Two,ISNULL(s2.Area_Three,0) as Area_Three

from

(

select s.s_date as sDate

from(

select DATEADD(dd,number,@sDate1) as s_date

from master..spt_values

where type = 'P' and DATEADD(dd,number,@sDate1) < DATEADD(mm,1,@sDate1)

)s)as s1

left join(

select s_date,count(case when Area='保养一区' then 1 end)Area_One ,

count(case when area='保养二区' then 1 end) Area_Two,

count(case when area='保养三区' then 1 end) Area_Three

from #LDTemp_Table

group by s_date

)s2

on s1.sDate=s2.s_Date

--删除临时表

DROP TABLE #LDTemp_Table

CREATE Proc P_GetRepairLDCount

@beginDate varchar(8),

@endDate varchar(8),

@Area varchar(50)

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--该存储过程是动态的可随这保养区中维修人员的编号而变化（注意：站点字段可为空，保养区的合计报修数量>= 各站点的报修数量总和）

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

if exists (select * from dbo.sysobjects where id=object_id(N'[dbo].[Table_New]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)

--print 'The UserTable exists'

drop table Table_New

----获取需要的数据列表 (因为“-”在语法上讲不可以作为列名，此处替换为“_”)

select Replace(station,'-','_')as 站名,Manager as 站长,c_Count as 报修数 into Table_RepairLDCount from(

select distinct(station)as Station,manager as Manager,ISNULL(c.c_Count,0) as c_Count

from rl_Area r

left join

(

select sex,job,count(*) as c_Count from CRM_CASE

where and (( and sTime>'070000') or (sDate and sTime<'070000') or (sDate >= and sDate< @endDate )) and ACC_PLAN='0'

group by sex,job

) c

on r.station=c.job where

group by r.station,r.manager,c.c_Count

) t

----------计算出该保养区中的所有报修数，并插入到数据表中---------

DECLARE @Sum varchar(30)

select @Sum=count(*) from CRM_CASE where and (( and sTime>'070000') or (sDate and sTime<'070000') or (sDate >= and sDate< @endDate )) and ACC_PLAN='0'

--exec('select (*) from CRM_CASE')

--print @Sum

insert into Table_RepairLDCount values('杭州区','合计',@Sum)

----执行表旋转操作

--生成中间数据表

declare @s varchar(8000)

set @s = 'create table Table_New (站名 varchar(20)'

select @s = @s + ',' + 站名 + ' varchar(10)' from Table_RepairLDCount order by 站名 asc

set @s = @s + ')'

exec(@s)

--print @s

--借助中间表实现行列转换

declare @name varchar(20)

declare t_cursor cursor for

select name from syscolumns

where id=object_id('Table_RepairLDCount') and colid > 1 order by colid asc

--打开游标

open t_cursor

fetch next from t_cursor into @name

while @@fetch_status = 0

begin

exec('select ' + @name + ' as t into Tabel_RepairTemp from Table_RepairLDCount order by 站名 asc')

set @s='insert into Table_New select ''' + @name + ''''

select @s = @s + ',''' + rtrim(t) + '''' from Tabel_RepairTemp

exec(@s)

exec('drop table Tabel_RepairTemp')

fetch next from t_cursor into @name

end

---关闭游标

close t_cursor

deallocate t_cursor

--查看行列互换处理结果

--select * from Table_RepairLDCount

select * from Table_New

--删除临时表

drop table Table_RepairLDCount

drop table Table_New

动态行转列

declare @sql varchar(8000)

set @sql = 'select s_Date as ' + 's_Date'

select @sql = @sql + ' , sum(case Area when ''' + station + ''' then 1 else 0 end) [' + station + ']'

from (select distinct station from RL_Area where ) as a

set @sql = @sql + ' from #NewTable group by s_Date'

exec(@sql)

游标使用语法：

--声明游标：

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]

[ FORWARD_ONLY | SCROLL ]

[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]

[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]

[ TYPE_WARNING ]

FOR select_statement

[ FOR UPDATE [ OF column_name [ ,...n ] ] ]

[;]

--local 表示为局部游标

--global 表示为全局游标

--forward_only 和 scroll 两种

--forward_only 意味着游标只能从数据集开始向数据集结束的方向读取，fetch next是唯一的选项

--scroll支持游标在定义的数据集中任何方向，或任何位置移动

--声明游标并给游标赋值

declare test_Cursor1 cursor scroll for

select * from role

open test_Cursor1

--跳转到第一行

fetch first from test_Cursor1

--跳转到最后一行

fetch last from test_Cursor1

--跳转到下一行

fetch next from test_Cursor1

--跳转到上一行

fetch prior from test_Cursor1

--直接跳转到某行

fetch absolute 3 from test_Cursor1

--相对于当前跳几行负值表示：向上，正值表示：向下

fetch relative -1 from test_Cursor1

--声明并负值

declare test_Cursor2 cursor for

select * from role

open test_Cursor2

fetch next from test_Cursor2

--关闭游标

Close test_Cursor1

Close test_Cursor2

--声明游标

deallocate test_Cursor1

deallocate test_Cursor2

--static keyset dynamic 和 fast_forward

--static：当游标被建立时，将会创建for 后面的select语句所包含的的数据集的副本存入tempdb数据库中，任何对于底层表内数据的更改不会影响

--到游标的内容。

--dynamic：和static相反，当底层数据库更改时，游标的内容也随之得到反映，在下一次fetch中，数据内容会随之改变。

--keyset可以理解为介于static和dynamic的之中方案。将游标所在的结果集的唯一能确定每一行的主键存入tempdb，当结果集中任何行改变或者删除时，

会为-2，keyset无法探测新加入的数据。

)

--fast_forward可以理解成forward_only的优化版本，forward_only执行的是静态计划，而fast_forward是根据情况进行选择采用动态计划还是静态计划，大多数情况下

--fast_forward要比forward_only性能略好。

--游标尽可能不使用（使用游标会比使用面向集合的方法慢2-3倍。如果有可能，尽量使用while，子查询，临时表，函数，表变量等代替游标。）

--使用过游标后一定要关闭和释放。

--尽量不要在大数据上定义游标

--尽量不适用insensitive，static和keyset参数定义游标

--如果可以，尽量使用fast_forward关键字定义游标

--如果只对数据进行读取，当读取时只用到fetch next选项，则最好使用forward_only参数

select 'insert into depworker(areadepid,workerid,remark) values('+convert(varchar(20),areadepid)+','+convert(varchar(20),workerid)+','''+remark+''')' from depworker

//查询该数据库中的表的变更时间

---------------------------------------------------------------------------------------------------------

SELECT SO.Name--表名

,ISNULL(EP.VALUE,'-') Description --描述

,CONVERT(varchar, SO.refdate, 120) as ModifiedTime --最后修改时间

FROM SYSCOLUMNS SC INNER JOIN SYSOBJECTS SO ON SC.ID = SO.ID AND SO.XTYPE = 'U' AND SO.NAME <> 'SYSDIAGRAMS'

LEFT JOIN SYS.EXTENDED_PROPERTIES EP ON SC.ID = EP.MAJOR_ID AND EP.MINOR_ID = 0

WHERE (CASE WHEN SC.COLORDER = 1 THEN SO.NAME ELSE '' END) <> ''

ORDER BY ModifiedTime DESC

//获取表的信息

exec sp_MShelpcolumns tb_Oil_Push

--------------------------------------------------------------------------------------------------