有代表性的数据操作语句
来源:互联网 发布:蓄水池算法 面试 编辑:程序博客网 时间:2024/05/01 08:28
1: 列转为行:
eg1:
Create table test (name char(10),km char(10),cj int)
go
insert test values('张三','语文',80)
insert test values('张三','数学',86)
insert test values('张三','英语',75)
insert test values('李四','语文',78)
insert test values('李四','数学',85)
insert test values('李四','英语',78)
想变成
姓名 语文 数学 英语
张三 80 86 75
李四 78 85 78
declare @sql varchar(8000)
set @sql = 'select name'
select @sql = @sql + ',sum(case km when '''+km+''' then cj end) ['+km+']'
from (select distinct km from test) as a
select @sql = @sql+' from test group by name'
exec(@sql)
drop table test
eg2:
有表A,
id pid
1 1
1 2
1 3
2 1
2 2
3 1
如何化成表B:
id pid
1 1,2,3
2 1,2
3 1
或者是从表B变成A(不要用游标)
以前有相似的列子,现在找不到了,帮帮忙!
--1.创建一个合并的函数
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+cast(pid as varchar) from 表A where id=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go
--调用自定义函数得到结果
select distinct id,dbo.fmerg(id) from 表A
2:
/*********** 行转列 *****************/
测试:
create table t1 (a int,b int,c int,d int,e int,f int,g int,h int)
insert t1 values(15, 9, 1, 0, 1, 2, 2, 0)
declare @ varchar(8000)
set @=''
select @=@+rtrim(name)+' from t1 union all select ' from syscolumns where id=object_id('t1')
set @=left(@,len(@)-len(' from t1 union all select '))
--print @
exec('select '+@+' from t1')
a
-----------
15
9
1
0
1
2
2
0
====================================================================================================
动态SQL语句 选择自 txlicenhe 的 Blog
关键字 动态SQL EXEC SP_EXECUTESQL
出处
1:
普通SQL语句可以用Exec执行
eg: Select * from tableName
Exec('select * from tableName')
sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N
2:
字段名,表名,数据库名之类作为变量时,必须用动态SQL
eg:
declare @fname varchar(20)
set @fname = '[name]'
Select @fname from sysobjects -- 错误
Exec('select ' + @fname + ' from sysobjects') -- 请注意 加号前后的 单引号的边上要加空格
exec sp_executesql N' select ' + @fname + ' from sysobjects'
当然将字符串改成变量的形式也可
declare @s varchar(1000)
set @s = 'select ' + @fname + ' from sysobjects'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句会报错
declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from sysobjects'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句正确,
3: 输出参数
eg:
declare @num,
@sqls
set @sqls='select count(*) from ' + @servername + '.a.dbo.b'
exec(@sqls)
我如何能将exec执行的结果存入变量@num中
declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from '+@servername+'.a.dbo.b'
exec sp_executesql @sqls,N'@a int output',@num output
select @num
=========================================================================================
介紹取一表前N筆記錄的各种數据庫的寫法...
作者﹕CCBZZP
1. ORACLE
SELECT * FROM TABLE1 WHERE ROWNUM<=N
2. INFORMIX
SELECT FIRST N * FROM TABLE1
3. DB2
SELECT * ROW_NUMBER() OVER(ORDER BY COL1 DESC) AS ROWNUM WHERE ROWNUM<=N
或者
SELECT COLUMN FROM TABLE FETCH FIRST N ROWS ONLY
4. SQL SERVER
SELECT TOP N * FROM TABLE1
5. SYBASE
SET ROWCOUNT N
GO
SELECT * FROM TABLE1
6. MYSQL
SELECT * FROM TABLE1 LIMIT N
7. FOXPRO
SELECT * TOP N FROM TABLE ORDER BY COLUMN
===================================================================================
create procedure SP_GET_TABLE_INFO
@ObjName varchar(128) /* The table to generate sql script */
as
declare @Script varchar(255)
declare @ColName varchar(30)
declare @ColID TinyInt
declare @UserType smallint
declare @TypeName sysname
declare @Length TinyInt
declare @Prec TinyInt
declare @Scale TinyInt
declare @Status TinyInt
declare @cDefault int
declare @DefaultID TinyInt
declare @Const_Key varchar(255)
declare @IndID SmallInt
declare @IndStatus Int
declare @Index_Key varchar(255)
declare @DBName varchar(30)
declare @strPri_Key varchar (255)
/*
** Check to see the the table exists and initialize @objid.
*/
if not Exists(Select name from sysobjects where name = @ObjName)
begin
select @DBName = db_name()
raiserror(15009,-1,-1,@ObjName,@DBName)
return (1)
end
create table #spscript
(
id int IDENTITY not null,
Script Varchar(255) NOT NULL,
LastLine tinyint
)
declare Cursor_Column INSENSITIVE CURSOR
for Select a.name,a.ColID,a.usertype,b.name,a.length,a.prec,a.scale,a.Status, a.cDefault,
case a.cdefault when 0 then ' ' else (select c.Text from syscomments c where a.cdefault = c.id) end const_key
from syscolumns a, systypes b where object_name(a.id) = @ObjName
and a.usertype = b.usertype order by a.ColID
set nocount on
Select @Script = 'Create table ' + @ObjName + '('
Insert into #spscript values(@Script,0)
/* Get column information */
open Cursor_Column
fetch next from Cursor_Column into @ColName,@ColID,@UserType,@TypeName,@Length,@Prec,@Scale,
@Status,@cDefault,@Const_Key
Select @Script = ''
while (@@FETCH_STATUS <> -1)
begin
if (@@FETCH_STATUS <> -2)
begin
Select @Script = @ColName + ' ' + @TypeName
if @UserType in (1,2,3,4)
Select @Script = @Script + '(' + Convert(char(3),@Length) + ') '
else if @UserType in (24)
Select @Script = @Script + '(' + Convert(char(3),@Prec) + ','
+ Convert(char(3),@Scale) + ') '
else
Select @Script = @Script + ' '
if ( @Status & 0x80 ) > 0
Select @Script = @Script + ' IDENTITY(1,1) '
if ( @Status & 0x08 ) > 0
Select @Script = @Script + ' NULL '
else
Select @Script = @Script + ' NOT NULL '
if @cDefault > 0
Select @Script = @Script + ' DEFAULT ' + @Const_Key
end
fetch next from Cursor_Column into @ColName,@ColID,@UserType,@TypeName,@Length,@Prec,@Scale,
@Status,@cDefault,@Const_Key
if @@FETCH_STATUS = 0
begin
Select @Script = @Script + ','
Insert into #spscript values(@Script,0)
end
else
begin
Insert into #spscript values(@Script,1)
Insert into #spscript values(')',0)
end
end
Close Cursor_Column
Deallocate Cursor_Column
/* Get index information */
Declare Cursor_Index INSENSITIVE CURSOR
for Select name,IndID,status from sysindexes where object_name(id)=@ObjName
and IndID > 0 and IndID<>255 order by IndID /*增加了对InDid为255的判断*/
Open Cursor_Index
Fetch Next from Cursor_Index into @ColName, @IndID, @IndStatus
while (@@FETCH_STATUS <> -1)
begin
if @@FETCH_STATUS <> -2
begin
declare @i TinyInt
declare @thiskey varchar(50)
declare @IndDesc varchar(68) /* string to build up index desc in */
Select @i = 1
while (@i <= 16)
begin
select @thiskey = index_col(@ObjName, @IndID, @i)
if @thiskey is null
break
if @i = 1
select @Index_Key = index_col(@ObjName, @IndID, @i)
else
select @Index_Key = @Index_Key + ', ' + index_col(@ObjName, @IndID, @i)
select @i = @i + 1
end
if (@IndStatus & 0x02) > 0
Select @Script = 'Create unique '
else
Select @Script = 'Create '
if @IndID = 1
select @Script = @Script + ' clustered '
if (@IndStatus & 0x800) > 0
select @strPri_Key = ' PRIMARY KEY (' + @Index_Key + ')'
else
select @strPri_Key = ''
if @IndID > 1
select @Script = @Script + ' nonclustered '
Select @Script = @Script + ' index ' + @ColName + ' ON '+ @ObjName
+ '(' + @Index_Key + ')'
Select @IndDesc = ''
/*
** See if the index is ignore_dupkey (0x01).
*/
if @IndStatus & 0x01 = 0x01
Select @IndDesc = @IndDesc + ' IGNORE_DUP_KEY' + ','
/*
** See if the index is ignore_dup_row (0x04).
*/
/* if @IndStatus & 0x04 = 0x04 */
/* Select @IndDesc = @IndDesc + ' IGNORE_DUP_ROW' + ',' */ /* 2000 不在支持*/
/*
** See if the index is allow_dup_row (0x40).
*/
if @IndStatus & 0x40 = 0x40
Select @IndDesc = @IndDesc + ' ALLOW_DUP_ROW' + ','
if @IndDesc <> ''
begin
Select @IndDesc = SubString( @IndDesc, 1, DataLength(@IndDesc) - 1 )
Select @Script = @Script + ' WITH ' + @IndDesc
end
/*
** Add the location of the data.
*/
end
if (@strPri_Key = '')
Insert into #spscript values(@Script,0)
else
update #spscript set Script = Script + @strPri_Key where LastLine = 1
Fetch Next from Cursor_Index into @ColName, @IndID, @IndStatus
end
Close Cursor_Index
Deallocate Cursor_Index
Select Script from #spscript
set nocount off
return (0)
==============================================================================================
收藏几段SQL Server语句和存储过程
-- ======================================================
--列出SQL SERVER 所有表,字段名,主键,类型,长度,小数位数等信息
--在查询分析器里运行即可,可以生成一个表,导出到EXCEL中
-- ======================================================
SELECT
(case when a.colorder=1 then d.name else '' end)表名,
a.colorder 字段序号,
a.name 字段名,
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识,
(case when (SELECT count(*)
FROM sysobjects
WHERE (name in
(SELECT name
FROM sysindexes
WHERE (id = a.id) AND (indid in
(SELECT indid
FROM sysindexkeys
WHERE (id = a.id) AND (colid in
(SELECT colid
FROM syscolumns
WHERE (id = a.id) AND (name = a.name))))))) AND
(xtype = 'PK'))>0 then '√' else '' end) 主键,
b.name 类型,
a.length 占用字节数,
COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,
(case when a.isnullable=1 then '√'else '' end) 允许空,
isnull(e.text,'') 默认值,
isnull(g.[value],'') AS 字段说明
FROM syscolumns a left join systypes b
on a.xtype=b.xusertype
inner join sysobjects d
on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e
on a.cdefault=e.id
left join sysproperties g
on a.id=g.id AND a.colid = g.smallid
order by a.id,a.colorder
-------------------------------------------------------------------------------------------------
列出SQL SERVER 所有表、字段定义,类型,长度,一个值等信息
并导出到Excel 中
-- -- Export all user tables definition and one sample value
-- jan-13-2003,Dr.Zhang
-- 在查询分析器里运行:
SET ANSI_NULLS OFF
GO
SET NOCOUNT ON
GO
SET LANGUAGE 'Simplified Chinese'
go
DECLARE @tbl nvarchar(200),@fld nvarchar(200),@sql nvarchar(4000),@maxlen int,@sample nvarchar(40)
SELECT d.name TableName,a.name FieldName,b.name TypeName,a.length Length,a.isnullable IS_NULL INTO #t
FROM syscolumns a, systypes b,sysobjects d
WHERE a.xtype=b.xusertype and a.id=d.id and d.xtype='U'
DECLARE read_cursor CURSOR
FOR SELECT TableName,FieldName FROM #t
SELECT TOP 1 '_TableName ' TableName,
'FieldName ' FieldName,'TypeName ' TypeName,
'Length' Length,'IS_NULL' IS_NULL,
'MaxLenUsed' AS MaxLenUsed,'Sample Value ' Sample,
'Comment ' Comment INTO #tc FROM #t
OPEN read_cursor
FETCH NEXT FROM read_cursor INTO @tbl,@fld
WHILE (@@fetch_status <> -1) --- failes
BEGIN
IF (@@fetch_status <> -2) -- Missing
BEGIN
SET @sql=N'SET @maxlen=(SELECT max(len(cast('+@fld+' as nvarchar))) FROM '+@tbl+')'
--PRINT @sql
EXEC SP_EXECUTESQL @sql,N'@maxlen int OUTPUT',@maxlen OUTPUT
--print @maxlen
SET @sql=N'SET @sample=(SELECT TOP 1 cast('+@fld+' as nvarchar) FROM '+@tbl+' WHERE len(cast('+@fld+' as nvarchar))='+convert(nvarchar(5),@maxlen)+')'
EXEC SP_EXECUTESQL @sql,N'@sample varchar(30) OUTPUT',@sample OUTPUT
--for quickly
--SET @sql=N'SET @sample=convert(varchar(20),(SELECT TOP 1 '+@fld+' FROM '+
--@tbl+' order by 1 desc ))'
PRINT @sql
print @sample
print @tbl
EXEC SP_EXECUTESQL @sql,N'@sample nvarchar(30) OUTPUT',@sample OUTPUT
INSERT INTO #tc SELECT *,ltrim(ISNULL(@maxlen,0)) as MaxLenUsed,
convert(nchar(20),ltrim(ISNULL(@sample,' '))) as Sample,' ' Comment FROM #t where TableName=@tbl and FieldName=@fld
END
FETCH NEXT FROM read_cursor INTO @tbl,@fld
END
CLOSE read_cursor
DEALLOCATE read_cursor
GO
SET ANSI_NULLS ON
GO
SET NOCOUNT OFF
GO
select count(*) from #t
DROP TABLE #t
GO
select count(*)-1 from #tc
select * into ##tx from #tc order by tablename
DROP TABLE #tc
--select * from ##tx
declare @db nvarchar(60),@sql nvarchar(3000)
set @db=db_name()
--请修改用户名和口令 导出到Excel 中
set @sql='exec master.dbo.xp_cmdshell ''bcp ..dbo.##tx out c:/'+@db+'_exp.xls -w -C936 -Usa -Psa '''
print @sql
exec(@sql)
GO
DROP TABLE ##tx
GO
-- 根据表中数据生成insert语句的存储过程
--建立存储过程,执行 spGenInsertSQL 表名
--感谢playyuer
-- CREATE proc spGenInsertSQL (@tablename varchar(256))
as
begin
declare @sql varchar(8000)
declare @sqlValues varchar(8000)
set @sql =' ('
set @sqlValues = 'values (''+'
select @sqlValues = @sqlValues + cols + ' + '','' + ' ,@sql = @sql + '[' + name + '],'
from
(select case
when xtype in (48,52,56,59,60,62,104,106,108,122,127)
then 'case when '+ name +' is null then ''NULL'' else ' + 'cast('+ name + ' as varchar)'+' end'
when xtype in (58,61)
then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast('+ name +' as varchar)'+ '+'''''''''+' end'
when xtype in (167)
then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
when xtype in (231)
then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
when xtype in (175)
then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end'
when xtype in (239)
then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end'
else '''NULL'''
end as Cols,name
from syscolumns
where id = object_id(@tablename)
) T
set @sql ='select ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' from '+@tablename
--print @sql
exec (@sql)
end
GO
-- --根据表中数据生成insert语句的存储过程
--建立存储过程,执行 proc_insert 表名
--感谢Sky_blue
--
CREATE proc proc_insert (@tablename varchar(256))
as
begin
set nocount on
declare @sqlstr varchar(4000)
declare @sqlstr1 varchar(4000)
declare @sqlstr2 varchar(4000)
select @sqlstr='select ''insert '+@tablename
select @sqlstr1=''
select @sqlstr2=' ('
select @sqlstr1= ' values ( ''+'
select @sqlstr1=@sqlstr1+col+'+'',''+' ,@sqlstr2=@sqlstr2+name +',' from (select case
-- when a.xtype =173 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
when a.xtype =104 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(1),'+a.name +')'+' end'
when a.xtype =175 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
when a.xtype =61 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'
when a.xtype =106 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'+' end'
when a.xtype =62 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'
when a.xtype =56 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(11),'+a.name +')'+' end'
when a.xtype =60 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'
when a.xtype =239 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
when a.xtype =108 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'+' end'
when a.xtype =231 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
when a.xtype =59 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'
when a.xtype =58 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'
when a.xtype =52 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(12),'+a.name +')'+' end'
when a.xtype =122 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'
when a.xtype =48 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(6),'+a.name +')'+' end'
-- when a.xtype =165 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
when a.xtype =167 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
else '''NULL'''
end as col,a.colid,a.name
from syscolumns a where a.id = object_id(@tablename) and a.xtype <>189 and a.xtype <>34 and a.xtype <>35 and a.xtype <>36
)t order by colid
select @sqlstr=@sqlstr+left(@sqlstr2,len(@sqlstr2)-1)+') '+left(@sqlstr1,len(@sqlstr1)-3)+')'' from '+@tablename
-- print @sqlstr
exec( @sqlstr)
set nocount off
end
GO
说明:本贴纯属收藏,目的在于大家交流,在此对作者表示感谢!
- 有代表性的数据操作语句
- 15个很具代表性的MDX查询语句
- 15个很具代表性的MDX查询语句
- 有代表性的并发服务器端模型
- 牛人的求职面试经验,很有代表性!
- 牛人的求职面试经验,很有代表性!
- 一个很有代表性的boost状态机实例
- 解决数据内有'的sql语句
- mysql 关于查询时间的中工作中遇到的一些问题 有代表性的
- [转帖]查询及删除重复记录的sql 语句(具有代表性)
- 看到论坛上的一个对于js this关键字的问题,比较有代表性
- 《数据结构》同学链表程序的调试修改代码示例(有代表性,请看看)
- 论证是一门学问 如何让你的观点有说服力-8 规则8 使用有代表性的例子
- 对于MYSQL数据操作语句的总结
- MySQL数据操作的经典语句
- Django操作已有数据库的数据
- 常用数据操作语句
- 数据操作语句-1
- 我的电脑CPU风扇吱吱响 还不能上油谁知道怎么回事指点我下
- IT人 不要一辈子靠技术生存
- 使用javascript在html中获取url参数的脚本
- Windows CE 模拟器和远程调试工具
- DNS在linux下的配置,相当精辟
- 有代表性的数据操作语句
- Google公司的面试
- asp.net 的上传前浏览(js)
- 服务器提交协议冲突 Section=ResponseStatusLine 的解决办法
- 杯子测试
- js技巧收集
- 用pascal写的最简单的winsock程序 --收藏转贴
- 检查WebService是否可用
- Linux提交命令至后台运行