金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > SQL操作收集,MSSql行列转换的Sql语法

SQL操作收集,MSSql行列转换的Sql语法

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-11-05 18:51

 

图片 1declare @name varchar(250)
图片 2declare #aa cursor for
图片 3select name from sysobjects where xtype not in('S','PK','D','X','L')
图片 4open #aa
图片 5fetch next from #aa into @name
图片 6while @@fetch_status=0
图片 7begin
图片 8 print dbo.fgetscript('onlytiancai','sa','sa','database',@name)
图片 9 fetch next from #aa into @name
图片 10end
图片 11close #aa
图片 12deallocate #aa
图片 13

二、这里的列是汉语的,你能够凑合成意国语的,就看您想要什么名字了。

  1. 怎么着得到叁个数据表的具有列名
    方法如下:先从sysobjects系统表中拿走数据表的systemid,然后再syscolumns表中获得该数据表的有所列名。
    SQL语句如下:
    Declare @objid int,@objname char(40)
    set @objname = 'ColumnToRow'
    --第1种方法
    select @objid = id from sysobjects where id = object_id(@objname)
    select 'Column_name' = name from syscolumns where id = @objid order by colid
    --或也得以写成
    select name as 'Column_name' from syscolumns where id = @objid order by colid
    --第2种方法:
    Select name as 'Column_Name' from SysColumns where id = object_id(@objname) Order by colid

 

三、这里是用的是一时表查询数据,那么数据库查询出的数码确定要加到一时表中,方法是用游标。

 

    (3)把二个光阴项目字段转变到"一九七零-07-06"  

  在做报表的时候,经常会超出要把询问出的多寡做为列头突显,但是查询出来的数据会依据法规的两样查询出的数量也就分裂(动态列卡塔尔,那么列头也分裂。

Declare @sql Varchar(8000)
Set @sql = ''
Select @sql = @sql + rtrim(name) + ' from ColumnToRow union all Select ' from SysColumns Where id = object_id('ColumnToRow')
Set @sql = SubString(@sql,1,len(@sql)-70)
--70的尺寸就是其一字符串'from ColumnToRow union all Select ID from ColumnToRow union all Select ',因为它会把ID这一列的值也算进去,所以要把它截掉
Exec ('Select ' + @sql + ' from ColumnToRow')

图片 14select identity(int,1,1) as autoID, * into #Tmp from tableName
图片 15select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID
图片 16select * from #Tmp where autoID in(select autoID from #tmp2) 

  declare @Str varchar(500);
  Create Table #t_Data(ID int identity(1,1),DeptID int,DeptName varchar(50),TotalAmountYS money,TotalAmountZY             money,TotalAmountZX money);      
      Set @str=(Select Stuff((SELECT ','+ltrim(a.YWTypeName)+' money'
      From
      (select YWTypeName,GKYWTypeID from tbgYearSourceGKSplitList where SplitYears=2016 group by YWTypeName,GKYWTypeID)
      a
  For XML  Path('')),1,1,''));
  Set @str='Alter Table #t_Data Add '+ @str; 
  Exec(@Str);
 select * from #t_Data

CREATE PROCEDURE [dbo].[pro_Sum_1]
AS
BEGIN
DECLARE @sql varchar(8000)
SET              @sql = 'select d_Name as 单位,s_Name as 职业职员'
                          SELECT     @sql = @sql + ', (case q_No when ''' + q_No + ''' then a_Answer else ''' + '' + ''' end) [' + q_No + ']'
                           FROM         (SELECT DISTINCT q_No
                                                  FROM          V_AnswerList) AS V_AnswerList
                                                      SELECT     @sql = @sql + 'from V_AnswerList'  EXEC (@sql)
END

**    7 分区视图

 图片 17Q78ZU%7B_DKEQ%60CHLH1ND.png)

--调用自定义函数获得结果
Select Distinct Class,dbo.KFReturn(Class) From ClassNo

    (6) 第六种

  生龙活虎、将查询出来的数码调换来列头

除此以外贰个带参数的存款和储蓄进程:

 

 图片 18

原表(这里实在能够是个视图V_AnswerList卡塔尔的数据结构,如下图所示:

    (2) 第二种

 

 

 数据Curry有1,2,3,4,5 共5条记下,要用一条sql语句让其排序,使它排列成4,5,1,2,3,怎么写?

以当时候就须求把询问出多少的行转产生列。

 

    还也许有个更加强的说话,是邹建写的,也写出来啊

那边因为a_Answer为字符串类型,小编不笑得使用非常聚合函数将其接二连三;假使a_Answer为int 型,在蕴藏进度中你能够这么Sum(case q_No when ....)。

    6  时间格式调换难题

  1. SQL Server日期计算
    a. 一个月的率后天
    SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
    b. 本周的星期三
    SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
    c. 一年的第一天
    SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
    d. 季度的率后天
    SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
    e. 前一个月的结尾一天
    SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
    f. 二〇一八年的最后一天
    SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
    g. 上个月的末段一天
    SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
    h. 前段日子的率先个周一
    select DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0)
    i. 下半年的尾声一天
    SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))

    12 让0变成1,1变成0 

图片 19

图片 20SELECT [dbo].[getstrarrlength]('1,2,3,4,a,b,c,d')
图片 21SELECT [dbo].[getstrofindex]('1,2,3,4,a,b,c,d',5)

  1. 经过SQL语句来改造顾客的密码
    修改外人的,需求sysadmin role
    Exec Sp_password '原始密码','修改后密码','账号'
    Exec sp_password null,ok,sa

图片 22 select * from #temp where id0 >=n and id0 <= m

set
@s_No_1 = @s_No;
SET
@sql = 'select d_Name as 单位,s_Name as 专门的职业人士'
                          SELECT     @sql = @sql + ', (case q_No when ''' + q_No + ''' then a_Answer else ''' + '' + ''' end) [' + q_No + ']'
                           FROM         (SELECT DISTINCT q_No
                                                  FROM          V_AnswerList) AS V_AnswerList
                                                      SELECT     @sql = @sql + 'from V_AnswerList where s_No=' + @s_No_1
EXEC (@sql)

**    [2]

END

图片 23
图片 24--参考
图片 25
图片 26--树形数据查询示例
图片 27--作者: 邹建
图片 28
图片 29--示例数据
图片 30create table [tb]([id] int identity(1,1),[pid] int,name varchar(20))
图片 31insert [tb] select 0,'中国'
图片 32union  all  select 0,'美国'
图片 33union  all  select 0,'加拿大'
图片 34union  all  select 1,'北京'
图片 35union  all  select 1,'上海'
图片 36union  all  select 1,'江苏'
图片 37union  all  select 6,'苏州'
图片 38union  all  select 7,'常熟'
图片 39union  all  select 6,'南京'
图片 40union  all  select 6,'无锡'
图片 41union  all  select 2,'纽约'
图片 42union  all  select 2,'旧金山'
图片 43go
图片 44
图片 45--查询内定id的全部子
图片 46create function f_cid(
图片 47@id int
图片 48)returns @re table([id] int,[level] int)
图片 49as
图片 50begin
图片 51 declare @l int
图片 52 set @l=0
图片 53 insert @re select @id,@l
图片 54 while @@rowcount>0
图片 55 begin
图片 56  set @l=@l+1
图片 57  insert @re select a.[id],@l
图片 58  from [tb] a,@re b
图片 59  where a.[pid]=b.[id] and b.[level]=@l-1
图片 60 end
图片 61图片 62/**//**//**//**//**//**//**//*--借使只显示最缜密的子(上边未有子),则拉长这些删除
图片 63 delete a from @re a
图片 64 where exists(
图片 65  select 1 from [tb] where [pid]=a.[id])
图片 66--*/
图片 67 return
图片 68end
图片 69go
图片 70
图片 71--调用(查询全体的子)
图片 72select a.*,层次=b.[level] from [tb] a,f_cid(2)b where a.[id]=b.[id]
图片 73go
图片 74
图片 75--删除测量试验
图片 76drop table [tb]
图片 77drop function f_cid
图片 78go
图片 79
图片 80

1.队列转换

图片 81 ** 16 SQL Server中除去重复数据的多少个方法
图片 82
 
** 图片 83  数据库的应用进度中由于程序方面包车型地铁标题不常会遇上重复数据,重复数据形成了数据库部分装置不可能科学安装。

CREATE PROCEDURE [dbo].[pro_Sum_2]
(
@s_No varchar(20)
)
AS
BEGIN

 

  1. 队列转变--合併
    原表:   班级    学号    
                1          1  
                1          2
                1          3
                2          1
                2          2
                3          1
    退换后的表:  班级  学号           
                           1   1,2,3
                           2   1,2
                           3   1  

    还应该有生机勃勃种就是

编排存款和储蓄进程:

 

实例:
Create table ClassNo  --创建表ClassNo
(
    ID Int IDENTITY(1,1)  not null,  --成立列ID,何况每回新扩展一条记下就能够加1
    Class  Varchar(50),    --班级列
    Number Varchar(50),    --学号列
    Primary Key(ID)        --定义ID为表ClassNo的主键
);
--Truncate Table ClassNo
--Select * from ClassNo
Insert Into ClassNo
Select 1,1 Union all
Select 1,2 Union all
Select 1,3 Union all
Select 2,1 Union all
Select 2,2 Union all
Select 3,1

    (5) 第五种

实例:
create table cj  --创建表cj
(
    ID       Int IDENTITY (1,1)     not null, --创设列ID,而且每便新扩展一条记下就能够加1
    Name     Varchar(50),  
    Subject  Varchar(50),
    Result   Int, 
    primary key (ID)      --定义ID为表cj的主键     
);
--Truncate table cj
--Select * from cj
Insert into cj
Select '张三','语文',80 union all
Select '张三','数学',90 union all
Select '张三','物理',85 union all
Select '李四','语文',85 union all
Select '李四','物理',82 union all
Select '李四','英语',90 union all
Select '李四','政治',70 union all
Select '王五','英语',90
--行列调换
Declare @sql varchar(8000)
Set @sql = 'Select Name as 姓名'
Select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result else 0 end) ['+Subject+']'
from (select distinct Subject from cj) as cj  --把具备唯大器晚成的科目标名目都列举出来
Select @sql = @sql+' from cj group by name'
Exec (@sql)

    (4) 第四种

  1. 怎么判别出贰个表的什么样字段不一样意为空?
    Declare @objname Varchar(50)
    set @objname = 'ColumnToRow'
    Select Column_Name from information_schema.Columns where is_nullable = 'No' and Table_Name = @objname

图片 84UPDATE lvshi
图片 85SET shenling = CONVERT(varchar(4), YEAR(shenling)) 
图片 86      + '-' + CASE WHEN LEN(MONTH(shenling)) = 1 THEN '0' + CONVERT(varchar(2), 
图片 87      month(shenling)) ELSE CONVERT(varchar(2), month(shenling)) 
图片 88      END + '-' + CASE WHEN LEN(day(shenling)) = 1 THEN '0' + CONVERT(char(2), 
图片 89      day(shenling)) ELSE CONVERT(varchar(2), day(shenling)) END
图片 90WHERE (zhiyezheng = '139770070153')

1.--行列调换 另例
原表:   姓名     科目   成绩
           张三     语文    80
           张三     数学    90
           张三     物理    85
           李四     语文    85
           李四     物理    82
           李四     英语    90
           李四     政治    70
           王五     英语    90

 

 

图片 91SELECT SUBSTRING(shengri, 3, 2) AS year, SUBSTRING(shengri, 6, 2) AS month, 
图片 92      SUBSTRING(shengri, 9, 2) AS day
图片 93FROM lvshi
图片 94WHERE (zhiyezheng = '139770070153')

3:列转行
--Drop Table ColumnToRow
Create table ColumnToRow
(
   ID Int IDENTITY(1,1)  not null,  --创设列ID,并且每一回新扩展一条记下就能够加1
   a  int,
   b  int,
   c  int,
   d  int,
   e  int,
   f  int,
   g  int,
   h  int,
   Primary Key(ID)        --定义ID为表ColumnToRow的主键     
);
--Truncate Table ColumnToRow
--Select * from ColumnToRow
Insert Into ColumnToRow
Select 15,9,1,0,1,2,4,2 Union all
Select 22,34,44,5,6,7,8,7 Union all
Select 33,44,55,66,77,88,99,12

图片 95select * from t
图片 96WHERE id between 0 and 5
图片 97order by charindex(cast(id as varchar),'45123')

供参考:

图片 98CREATE   FUNCTION fn_Get05LvshiNameBySuo  (@p_suo Nvarchar(50))
图片 99RETURNS Nvarchar(2000)
图片 100AS
图片 101BEGIN  
图片 102 DECLARE @LvshiNames varchar(2000), @name varchar(50)
图片 103 select @LvshiNames=''
图片 104 DECLARE lvshi_cursor CURSOR FOR

7. 怎么在数据Curry找到含有相通字段的表?
a. 查已知列名的气象
Select a.name as Columnname,b.name as tablename from SysColumns a inner join sysobjects b on a.id = b.id
and b.type = 'U' and a.name = '您要物色的字段名'
b. 未知列名查全部在不一致表现身过的列名
Select s.name as tablename,s1.name as columnname from SysColumns s1,Sysobjects s
Where s1.id = s.id and s.Type = 'U' and Exists (Select 1 from syscolumns s2 where s1.name = s2.name and s1.id <> s2.id)

**

 

图片 105 ** **就足以获取无重复记录的结果集。

退换后的表:  姓名       数学    物理     土耳其共和国语    语文    政治 
                       李四         0         82        90      85       70
                       王五         0          0         90       0         0
                       张三        90        85         0       80        0

    (1) 先用上边包车型地铁剧本制造一个函数

图片 106

图片 107select * from tablename where identitycol between n and m 

 

图片 108EXEC sp_addlinkedserver '别名','','MSDASQL',NULL,NULL,'DRIVER={SQL Server};SERVER=远程名;UID=用户;PWD=密码;'
图片 109exec sp_addlinkedsrvlogin  @rmtsrvname='别名',@useself='false',@locallogin='sa',@rmtuser='sa',@rmtpassword='密码'
图片 110GO
图片 111

落到实处后的功力:

    11 获取子表内的一列数据的咬合字符串

8.查询第N行数据
假设id是主键:
select *
from (select top N * from 表) aa
where not exists(select 1 from (select top N-1 * from 表) bb where aa.id=bb.id)

    (1) 获取成分个数的函数

开创叁个统后生可畏的函数
--Drop Function KFReturn
Create Function KFReturn(@Class Varchar(50))
Returns Varchar(8000)
as
Begin
Declare @str Varchar(8000)
Set @str = ''
Select @str = @str + cast(Number as Varchar(50))  + ',' from ClassNo Where Class = @Class
Set @str = SubString(@str,1,len(@str)-1)
Return(@str)
End

图片 112declare @max integer,@id integer
图片 113declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) > 1
图片 114open cur_rows
图片 115fetch cur_rows into @id,@max
图片 116while @@fetch_status=0
图片 117begin
图片 118select @max = @max -1
图片 119set rowcount @max
图片 120delete from 表名 where 主字段 = @id
图片 121fetch cur_rows into @id,@max
图片 122end
图片 123close cur_rows
图片 124set rowcount 0 

 

图片 125 ** **15 提取数据库内全体表的字段详细表达的SQL语句

DECLARE @s_No_1 varchar(20);
DECLARE @sql varchar(8000);

图片 126create function getstrofindex (@str varchar(8000),@index int =0)
图片 127returns varchar(8000)
图片 128as
图片 129begin
图片 130  declare @str_return varchar(8000)
图片 131  declare @start int
图片 132  declare @next int
图片 133  declare @location int
图片 134  select @start =1
图片 135  select @next =1 --借使习贯从0最先则select @next =0
图片 136  select @location = charindex(',',@str,@start)
图片 137  while (@location <>0 and @index > @next )
图片 138  begin
图片 139    select @start = @location +1
图片 140    select @location = charindex(',',@str,@start)
图片 141    select @next =@next +1
图片 142  end
图片 143  if @location =0 select @location =len(@str)+1 --假设是因为从没逗号退出,则认为逗号在字符串后
图片 144  select @str_return = substring(@str,@start,@location -@start) --@start分明是逗号之后之处依然正是开首值1
图片 145  if (@index <> @next ) select @str_return = '' --如若两岸不等于,则是因为逗号太少,可能@index小于@next的初步值1。
图片 146  return @str_return
图片 147end
图片 148

图片 149 〈2〉 那类重复难点日常必要保存重复记录中的第一条记下,操作方法如下

 

    上面那些函数获取05年已经登记了的某部所的辨方,唯后生可畏一个参数正是事务厅的名称,然后重返zhuce字段里包涵05字样的保有律师。 

 **   3 分隔字符串

图片 150Select * from fielddesc where table_name = '你的表名' 

图片 151 ** (2) 方法二**

    (3) 第三种

图片 152select * from t order by case when id >3 then id-5 else id end

图片 153print dbo.fgetscript('服务器名','顾客名','密码','数据库名','表名或此外对象名')

图片 154 ** (1) 方法一**

图片 155 假定tablename里未有其它identity列,那么:

    (2) 获取钦赐索引的值的函数

图片 156select * from OPENDATASOURCE('SQLOLEDB','Data Source=远程ip;User ID=sa;Password=密码').库名.dbo.表名

   本文的源地址:  
    [1]

 

    先成立二个视图

    5 获取三个表中全体的字段音讯

图片 157 ** **〈1〉 对于第生龙活虎种重复,比较轻便解决,使用

图片 158select * from t order by (id+2)%6

 

图片 159delete from [fujian] where charindex(','+cast([id] as varchar)+',',','+'5,6,8,9,10,11,'+',')>0

图片 160UPDATE lvshi
图片 161SET shengri = '19' + REPLACE(shengri, '.', '-')
图片 162WHERE (zhiyezheng = '139770070153')

    select top 0 * into [t1] from [t2]

    4  一条语句施行逾越若干个数据库
    自个儿要在一条语句里操作不相同的服务器上的不等的数据Curry的不等的表,如何做吧?
    方法一:

图片 163 ** **17 查询剖析器不能够单步调节和测量检验的的缘故

图片 164select * from t

    (2)用法如下:

    [3

    查询时:

 

图片 165select distinct * from tableName 

**    8 树型的兑现

图片 166 ** **发生这种重新的由来是表设计不周产生的,增添独一索引列就可以缓慢解决。

**图片 167  **(4) 第四种
图片 168
 若是表里有identity属性,那么简单:图片 169 **

    9 排序难题

图片 170 ** 具体步骤如下:
图片 171
 ** 1、将服务器【身份验证】属性设置成【混合形式】(window与sql身份验证卡塔 尔(英语:State of Qatar)
图片 172 ** 2、在【调节面板】中张开【服务】将【MSSQLSE奥德赛VE讴歌RDX】服务展开【属性】,选用【登陆】页面,将登入身份设置成服务器本地帐号和该帐号密码,如administrator,密码123;
图片 173
 ** 3、重新开动sqlserver服务,那个时候的劳动指的是【SQL服务微处理器】中的SQL SEPRADOVE凯雷德服务;倘若【帐号】设置为administrator
图片 174 ** 那会儿高达的功效是:服务器本地帐号administrator与顾客端上的administrator(并且该帐号的密码要与服务器密码相仿卡塔尔国能够透过【查询剖析器】实行调护医疗;
图片 175
 ** 假若想让【其余帐号】也能够调整,那么还索要如下设置:
图片 176 ** 1、在【服务器】上运行dcomcnfg.exe;
图片 177
 ** 2、在【默许安全机制】中【私下认可访谈权限】右侧点击【编辑默许值】采取允许调节和测试的帐号类型,如users客户类型,sample帐号有隐含users组;
图片 178 ** 3、重新起动sqlserver服务;
图片 179
 ** 3、在顾客端上创办与劳务帐号密码一样的顾客,如sample;
图片 180 ** 完结那步就能够通过查询分析器的调理功效拓宽单步调节和测验了。
图片 181
 ** 注:第二步更正“运营服务帐户”,在率先次登陆从前,必需改动客商密码。
图片 182 ** 不然,event log:
图片 183
 ** 以如今密码登入的品尝因下列错误而公布破产:
图片 184 ** **在首先次登陆以前,必需改动客户密码。

图片 185select distinct * into #Tmp from tableName
图片 186drop table tableName
图片 187select * into tableName from #Tmp
图片 188drop table #Tmp 

   (3) 第三种

    先是id列是int标记类类型,然后删除ID值为5,6,8,9,10,11的列,这里的cast函数不可能用convert函数代替,何况更动的类型必得是varchar,而不能够是char,不然就能够实践出您不愿意的结果,这里的"5,6,8,9,10,11"能够是你在页面上获得的多个chkboxlist营造设成的值,然后用下边的一句就整个刨除了,比循环用多条语句高效吧应该。

图片 189 取n到m条的话语为:

    因为新开采的软件必要用有个别旧软件生成的有个别数目,在时光格式上不联合,只可以手工业转变,斟酌了一早晨写了三条语句,以前没怎么用过convert函数和case语句,还也有"+"操作符在不一样上下文情状也会起到区别的作用,把本身搞晕了要,不过将来看来是大致弄好了。

图片 190 末尾四个select即获得了Name,Address不另行的结果集(但多了三个autoID字段,实际写时方可写在select子句中节省此列卡塔 尔(英语:State of Qatar)

图片 191delete from table1 where id in(1,2,3,4 )

图片 192select * from 别名.库名.dbo.表名
图片 193insert 库名.dbo.表名 select * from 别名.库名.dbo.表名
图片 194select * into 库名.dbo.新表名 from 别名.库名.dbo.表名
图片 195go

    方法二:
    先利用联结服务器:

 

    (1) 第一种

图片 196SELECT 
图片 197 (case when a.colorder=1 then d.name else '' end) N'表名',
图片 198 a.colorder N'字段序号',
图片 199 a.name N'字段名',
图片 200 (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'标识',
图片 201 (case when (SELECT count(*)
图片 202 FROM sysobjects
图片 203 WHERE (name in
图片 204           (SELECT name
图片 205          FROM sysindexes
图片 206          WHERE (id = a.id) AND (indid in
图片 207                    (SELECT indid
图片 208                   FROM sysindexkeys
图片 209                   WHERE (id = a.id) AND (colid in
图片 210                             (SELECT colid
图片 211                            FROM syscolumns
图片 212                            WHERE (id = a.id) AND (name = a.name))))))) AND
图片 213        (xtype = 'PK'))>0 then '√' else '' end) N'主键',
图片 214 b.name N'类型',
图片 215 a.length N'占用字节数',
图片 216 COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',
图片 217 isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',
图片 218 (case when a.isnullable=1 then '√'else '' end) N'允许空',
图片 219 isnull(e.text,'') N'默认值',
图片 220 isnull(g.[value],'') AS N'字段表达'
图片 221--into ##tx
图片 222
图片 223FROM  syscolumns  a left join systypes b 
图片 224on  a.xtype=b.xusertype
图片 225inner join sysobjects d 
图片 226on a.id=d.id  and  d.xtype='U' and  d.name<>'dtproperties'
图片 227left join syscomments e
图片 228on a.cdefault=e.id
图片 229left join sysproperties g
图片 230on a.id=g.id AND a.colid = g.smallid  
图片 231order by object_name(a.id),a.colorder
图片 232
图片 233

** 

   (2) 第二种 

图片 234select * from t order by id / 4 desc,id asc

图片 235SELECT 
图片 236(case when a.colorder=1 then d.name else '' end) N'表名', 
图片 237a.colorder N'字段序号', 
图片 238a.name N'字段名', 
图片 239(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' 
图片 240end) N'标识', 
图片 241(case when (SELECT count(*) 
图片 242FROM sysobjects 
图片 243WHERE (name in 
图片 244(SELECT name 
图片 245FROM sysindexes 
图片 246WHERE (id = a.id) AND (indid in 
图片 247(SELECT indid 
图片 248FROM sysindexkeys 
图片 249WHERE (id = a.id) AND (colid in 
图片 250(SELECT colid 
图片 251FROM syscolumns 
图片 252WHERE (id = a.id) AND (name = a.name))))))) AND 
图片 253(xtype = 'PK'))>0 then '√' else '' end) N'主键', 
图片 254b.name N'类型', 
图片 255a.length N'占用字节数', 
图片 256COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度', 
图片 257isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数', 
图片 258(case when a.isnullable=1 then '√'else '' end) N'允许空', 
图片 259isnull(e.text,'') N'默认值', 
图片 260isnull(g.[value],'') AS N'字段表达' 
图片 261FROM syscolumns a 
图片 262left join systypes b 
图片 263on a.xtype=b.xusertype 
图片 264inner join sysobjects d 
图片 265on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' 
图片 266left join syscomments e 
图片 267on a.cdefault=e.id 
图片 268left join sysproperties g 
图片 269on a.id=g.id AND a.colid = g.smallid 
图片 270order by object_name(a.id),a.colorder
图片 271拿到表结构[把 'sysobjects' 替换 成 'tablename' 即可] 
图片 272
图片 273SELECT CASE IsNull(I.name, '') 
图片 274When '' Then '' 
图片 275Else '*' 
图片 276End as IsPK, 
图片 277Object_Name(A.id) as t_name, 
图片 278A.name as c_name, 
图片 279IsNull(SubString(M.text, 1, 254), '') as pbc_init, 
图片 280T.name as F_DataType, 
图片 281CASE IsNull(TYPEPROPERTY(T.name, 'Scale'), '') 
图片 282WHEN '' Then Cast(A.prec as varchar) 
图片 283ELSE Cast(A.prec as varchar) + ',' + Cast(A.scale as varchar) 
图片 284END as F_Scale, 
图片 285A.isnullable as F_isNullAble 
图片 286FROM Syscolumns as A 
图片 287JOIN Systypes as T 
图片 288ON (A.xType = T.xUserType AND A.Id = Object_id('sysobjects') ) 
图片 289LEFT JOIN ( SysIndexes as I 
图片 290JOIN Syscolumns as A1 
图片 291ON ( I.id = A1.id and A1.id = object_id('sysobjects') and (I.status & 0x800) = 0x800 AND A1.colid <= I.keycnt) ) 
图片 292ON ( A.id = I.id AND A.name = index_col('sysobjects', I.indid, A1.colid) ) 
图片 293LEFT JOIN SysComments as M 
图片 294ON ( M.id = A.cdefault and ObjectProperty(A.cdefault, 'IsConstraint') = 1 ) 
图片 295ORDER BY A.Colid ASC
图片 296

图片 297 ** **有五个意思上的重复记录,一是完全重复的笔录,也即怀有字段均再度的记录,二是有的重大字段重复的笔录,比方Name字段重复,而别的字段不自然再一次或都重复能够忽视。

    (3)借使要获取Curry全部指标的本子,如如下格局

图片 298Create view fielddesc    
图片 299as
图片 300select o.name as table_name,c.name as field_name,t.name as type,c.length as 
图片 301
图片 302length,c.isnullable as isnullable,convert(varchar(30),p.value) as desp 
图片 303from syscolumns c  
图片 304join systypes t on c.xtype = t.xusertype
图片 305join sysobjects o on o.id=c.id 
图片 306left join    sysproperties p on p.smallid=c.colid and p.id=o.id    
图片 307where o.xtype='U'

**    分区视图是加强查询质量的几个很好的艺术

   ** 要是有一个用逗号分割开的字符串,比如说"a,b,c,d,1,2,3,4",如何用t-sql获取这么些字符串有几个因素,获取第多少个因素的值是有个别啊?因为t-sql里不曾split函数,也从没数组的定义,所以不能不自身写多少个函数了。

图片 308 如若您在实施 select identity(int) id0,* into #temp from tablename那条语句的时候报错,那是因为您的DB中间的select into/bulkcopy属性未有张开要先实行:
图片 309 exec sp_dboption 你的DB名字,'select into/bulkcopy',true

图片 310insert t values (newid())

图片 311CREATE TABLE [t] (
图片 312 [id] [int] IDENTITY (1, 1) NOT NULL ,
图片 313 [GUID] [uniqueidentifier] NULL 
图片 314) ON [PRIMARY]
图片 315GO

图片 316 如若有重复的字段为Name, Address,须求获得那七个字段唯大器晚成的结果集

    (1) 把具有"70.07.06"那样的值变成"一九六八-07-06"

    (2)在"1970-07-06"里提取"70","07","06"

    10 一条语句删除一堆记录

图片 317select rows from sysindexes where id = object_id('test') and indid in (0,1)

    翻看实践结果

图片 318select * from t order by charindex(cast(id as varchar),'45123')

    (3) 用法如下:

图片 319select top n * from (select top m * from tablename order by columnname) a order by columnname desc

图片 320 select identity(int) id0,* into #temp from tablename

     13  种种方法取表里n到m条纪录
    (1) 第一种** **

    然后:

图片 321create function getstrarrlength (@str varchar(8000))
图片 322returns int
图片 323as
图片 324begin
图片 325  declare @int_return int
图片 326  declare @start int
图片 327  declare @next int
图片 328  declare @location int
图片 329  select @str =','+ @str +','
图片 330  select @str=replace(@str,',,',',')
图片 331  select @start =1
图片 332  select @next =1 
图片 333  select @location = charindex(',',@str,@start)
图片 334  while (@location <>0)
图片 335  begin
图片 336    select @start = @location +1
图片 337    select @location = charindex(',',@str,@start)
图片 338    select @next =@next +1
图片 339  end
图片 340 select @int_return = @next-2
图片 341 return @int_return
图片 342end
图片 343

 

 

    1 只复制三个表结构,不复制数据

图片 344  14 快捷拿到表test的记录总量

图片 345 ** **固然该表供给删除重复的笔录(重复记录保留1条卡塔尔国,能够按以下措施删除

    2 获得数据库中有个别对象的始建脚本

    上边那句推行5次

图片 346declare @a int
图片 347set @a =0 --初始为0
图片 348select @a
图片 349set @a = @a^1 --把0变成1
图片 350select @a
图片 351set @a = @a^1 --把1变成0
图片 352select @a

图片 353select * from t
图片 354 order by case id when 4 then 1
图片 355                  when 5 then 2
图片 356                  when 1 then 3
图片 357                  when 2 then 4
图片 358                  when 3 then 5 end

图片 359if exists(select 1 from sysobjects where id=object_id('fgetscript') and objectproperty(id,'IsInlineFunction')=0)
图片 360  drop function fgetscript
图片 361go
图片 362
图片 363create function fgetscript(
图片 364@servername varchar(50)     --服务器名
图片 365 ,@userid varchar(50)='sa'    --客户名,假设为nt验证办法,则为空
图片 366 ,@password varchar(50)=''    --密码
图片 367 ,@databasename varchar(50)    --数据库名称
图片 368 ,@objectname varchar(250)    --对象名
图片 369) returns varchar(8000)
图片 370as
图片 371begin
图片 372 declare @re varchar(8000)        --重回脚本
图片 373 declare @srvid int,@dbsid int       --定义服务器、数据库集id
图片 374 declare @dbid int,@tbid int        --数据库、表id
图片 375 declare @err int,@src varchar(255), @desc varchar(255) --错误管理变量
图片 376
图片 377--创建sqldmo对象
图片 378 exec @err=sp_oacreate 'sqldmo.sqlserver',@srvid output
图片 379 if @err<>0 goto lberr
图片 380
图片 381--连接服务器
图片 382 if isnull(@userid,'')='' --倘使是 Nt验证方式
图片 383 begin
图片 384  exec @err=sp_oasetproperty @srvid,'loginsecure',1
图片 385  if @err<>0 goto lberr
图片 386
图片 387  exec @err=sp_oamethod @srvid,'connect',null,@servername
图片 388 end
图片 389 else
图片 390  exec @err=sp_oamethod @srvid,'connect',null,@servername,@userid,@password
图片 391
图片 392 if @err<>0 goto lberr
图片 393
图片 394--拿到数据库集
图片 395 exec @err=sp_oagetproperty @srvid,'databases',@dbsid output
图片 396 if @err<>0 goto lberr
图片 397
图片 398--拿到要拿走脚本的数据库id
图片 399 exec @err=sp_oamethod @dbsid,'item',@dbid output,@databasename
图片 400 if @err<>0 goto lberr
图片 401
图片 402--取得要拿到脚本的靶子id
图片 403 exec @err=sp_oamethod @dbid,'getobjectbyname',@tbid output,@objectname
图片 404 if @err<>0 goto lberr
图片 405
图片 406--获得脚本
图片 407 exec @err=sp_oamethod @tbid,'script',@re output
图片 408 if @err<>0 goto lberr
图片 409
图片 410 --print @re
图片 411 return(@re)
图片 412
图片 413lberr:
图片 414 exec sp_oageterrorinfo NULL, @src out, @desc out 
图片 415 declare @errb varbinary(4)
图片 416 set @errb=cast(@err as varbinary(4))
图片 417 exec master..xp_varbintohexstr @errb,@re out
图片 418 set @re='错误号: '+@re
图片 419   +char(13)+'错误源: '+@src
图片 420   +char(13)+'错误描述: '+@desc
图片 421 return(@re)
图片 422end
图片 423go
图片 424

图片 425--看上边包车型大巴演示
图片 426
图片 427--示例表
图片 428create table tempdb.dbo.t_10(
图片 429id int primary key check(id between 1 and 10),name varchar(10))
图片 430
图片 431create table pubs.dbo.t_20(
图片 432id int primary key check(id between 11 and 20),name varchar(10))
图片 433
图片 434create table northwind.dbo.t_30(
图片 435id int primary key check(id between 21 and 30),name varchar(10))
图片 436go
图片 437
图片 438--分区视图
图片 439create view v_t
图片 440as
图片 441select * from tempdb.dbo.t_10
图片 442union all
图片 443select * from pubs.dbo.t_20
图片 444union all
图片 445select * from northwind.dbo.t_30
图片 446go
图片 447
图片 448--插入数据
图片 449insert v_t select 1 ,'aa'
图片 450union  all select 2 ,'bb'
图片 451union  all select 11,'cc'
图片 452union  all select 12,'dd'
图片 453union  all select 21,'ee'
图片 454union  all select 22,'ff'
图片 455
图片 456--更新数据
图片 457update v_t set name=name+'_更新' where right(id,1)=1
图片 458
图片 459--删除测量试验
图片 460delete from v_t where right(id,1)=2
图片 461
图片 462--展现结果
图片 463select * from v_t
图片 464go
图片 465
图片 466--删除测量试验
图片 467drop table northwind.dbo.t_30,pubs.dbo.t_20,tempdb.dbo.t_10
图片 468drop view v_t
图片 469
图片 470图片 471/**//**//**//*--测验结果
图片 472
图片 473id          name       
图片 474----------- ---------- 
图片 4751           aa_更新
图片 47611          cc_更新
图片 47721          ee_更新
图片 478
图片 479(所影响的行数为 3 行卡塔尔国
图片 480==*/

 

图片 481select top m * into 临时表(或表变量) from tablename order by columnname -- 将top m笔插入
图片 482**set rowcount n
图片 483select * from 表变量 order by columnname desc**

本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:SQL操作收集,MSSql行列转换的Sql语法

关键词: