SQL语句

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
)T
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)
)        
AS
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)
AS
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)
GO

exec P_GZLDTimeAndCountByMonth '2013/1/1'

 

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

CREATE proc P_GZLDTimeAndCountByMonth
@sDate1  DATETIME,--当月的第一天
@sDate2 DATETIME--下月的第一天
AS

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
GO

 

 

CREATE Proc P_GetRepairLDCount
@beginDate varchar(8),
@endDate varchar(8),
@Area varchar(50)
As
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--该存储过程是动态的 可随这保养区中维修人员的编号而变化 （注意：站点字段可为空， 保养区的合计报修数量>= 各站点的报修数量总和）
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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 sex=''+@Area+'' and ((sDate=''+@beginDate+'' and sTime>'070000') or (sDate =''+@endDate+'' and sTime<'070000') or (sDate >= ''+@beginDate+'' and sDate< @endDate )) and ACC_PLAN='0'
  group by sex,job
) c
on r.station=c.job where r.Area=''+@Area+''
group by r.station,r.manager,c.c_Count
) t
----------计算出该保养区中的所有报修数，并插入到数据表中---------
DECLARE @Sum varchar(30)
select @Sum=count(*) from CRM_CASE where sex=''+@Area+'' and ((sDate=''+@beginDate+'' and sTime>'070000') or (sDate =''+@endDate+'' and sTime<'070000') or (sDate >= ''+@beginDate+'' and sDate< @endDate ))  and ACC_PLAN='0'
--exec('select '+@Sum+'=count(*) 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
GO

 动态行转列

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 area=@sex) 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，当结果集中任何行改变或者删除时，
--@@fetch_status会为-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
--------------------------------------------------------------------------------------------------

/*
----根据字段获取该字段所在的存储过程
----------------------------------------------------------------------------------------------------
SELECT obj.Name 存储过程名, sc.TEXT 存储过程内容
FROM syscomments sc
INNER JOIN sysobjects obj ON sc.Id = obj.ID
WHERE sc.TEXT LIKE '%' + 'newid' + '%'
AND TYPE = 'P'
GO
*/

--查看某个字段所在表
-------------------------------------------------------------------------------------------------
select 'select * from '+[name] from sysobjects where [id] in (select [id] from syscolumns where [name]='字段名')

Convert 和Cast 转换
convert(decimal(16,2) ,12.5)
或cast(12.5 as decimal(16,2))

 

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

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 表名

 

 

获取每个分组的第一条数据 

select a,b,c,createtime from
(select *,rn=ROW_NUMBER()over (PARTITION by a,b order by createtime desc) from tbName)k where k.rn=1