经典短小代码收集

SELECT * FROM TABLENAME
绝对经典,最常用的查询

固定列数的行列转换
如
student subject grade
--------- ---------- --------
student1 语文 80
student1 数学 70
student1 英语 60
student2 语文 90
student2 数学 80
student2 英语 100
……
转换为
语文数学英语
student1 80 70 60
student2 90 80 100
……
语句如下：select student,
sum(decode(subject,'语文', grade,null)) "语文",
sum(decode(subject,'数学', grade,null)) "数学",
sum(decode(subject,'英语', grade,null)) "英语"
from table
group by student;

--我来一段--SQL2005CREATE TABLE #t (id int IDENTITY(1,1) PRIMARY KEY,nums int)
GOINSERT INTO #t
SELECT abs(CHECKSUM(NEWID())%100000)
GO 10000 ----生成10000笔资料,可以是任意一数字SELECT * FROM #t
GO
DROP TABLE #t
GO

--日期转换参数,值得收藏
select CONVERT(varchar, getdate(), 120 )
2004-09-12 11:06:08select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')
20040912110608select CONVERT(varchar(12) , getdate(), 111 )
2004/09/12select CONVERT(varchar(12) , getdate(), 112 )
20040912select CONVERT(varchar(12) , getdate(), 102 )
2004.09.12其它我不常用的日期格式转换方法:select CONVERT(varchar(12) , getdate(), 101 )
09/12/2004select CONVERT(varchar(12) , getdate(), 103 )
12/09/2004select CONVERT(varchar(12) , getdate(), 104 )
12.09.2004select CONVERT(varchar(12) , getdate(), 105 )
12-09-2004select CONVERT(varchar(12) , getdate(), 106 )
12 09 2004select CONVERT(varchar(12) , getdate(), 107 )
09 12, 2004select CONVERT(varchar(12) , getdate(), 108 )
11:06:08select CONVERT(varchar(12) , getdate(), 109 )
09 12 2004 1select CONVERT(varchar(12) , getdate(), 110 )
09-12-2004select CONVERT(varchar(12) , getdate(), 113 )
12 09 2004 1select CONVERT(varchar(12) , getdate(), 114 )
11:06:08.177

/*
标题：普通行列转换(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-03-09
地点：广东深圳
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。问题：假设有张学生成绩表(tb)如下:
姓名课程分数
张三语文 74
张三数学 83
张三物理 93
李四语文 74
李四数学 84
李四物理 94
想变成(得到如下结果)：
姓名语文数学物理
---- ---- ---- ----
李四 74   84   94
张三 74   83   93
-------------------
*/create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')---------------------------------/*
问题：在上述结果的基础上加平均分，总分，得到如下结果：
姓名语文数学物理平均分总分
---- ---- ---- ---- ------ ----
李四 74   84   94   84.00  252
张三 74   83   93   83.33  250
*/--SQL SERVER 2000 静态SQL。
select 姓名姓名,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理,
  cast(avg(分数*1.0) as decimal(18,2)) 平均分,
  sum(分数) 总分
from tb
group by 姓名--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m ,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')drop table tb ------------------
------------------/*
问题：如果上述两表互相换一下：即表结构和数据为：
姓名语文数学物理
张三 74　　83　　93
李四 74　　84　　94
想变成(得到如下结果)：
姓名课程分数
---- ---- ----
李四语文 74
李四数学 84
李四物理 94
张三语文 74
张三数学 83
张三物理 93
--------------
*/create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go--SQL SERVER 2000 静态SQL。
select * from
(
select 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns
where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。--------------------
/*
问题：在上述的结果上加个平均分，总分，得到如下结果：
姓名课程   分数
---- ------ ------
李四语文   74.00
李四数学   84.00
李四物理   94.00
李四平均分 84.00
李四总分   252.00
张三语文   74.00
张三数学   83.00
张三物理   93.00
张三平均分 83.33
张三总分   250.00
------------------
*/select * from
(
select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb
union all
select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb
union all
select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 enddrop table tb

--按某一字段分组取最大(小)值所在行的数据
--(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-10-23于浙江杭州)
/*
数据如下：
name val memo
a    2   a2(a的第二个值)
a    1   a1--a的第一个值
a    3   a3:a的第三个值
b    1   b1--b的第一个值
b    3   b3:b的第三个值
b    2   b2b2b2b2
b    4   b4b4
b    5   b5b5b5b5b5
*/
--创建表并插入数据：
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a',    2,   'a2(a的第二个值)')
insert into tb values('a',    1,   'a1--a的第一个值')
insert into tb values('a',    3,   'a3:a的第三个值')
insert into tb values('b',    1,   'b1--b的第一个值')
insert into tb values('b',    3,   'b3:b的第三个值')
insert into tb values('b',    2,   'b2b2b2b2')
insert into tb values('b',    4,   'b4b4')
insert into tb values('b',    5,   'b5b5b5b5b5')
go--一、按name分组取val最大的值所在行的数据。
--方法1：
select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
--方法2：
select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)
--方法3：
select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4：
select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name
/*
name       val         memo
---------- ----------- --------------------
a          3           a3:a的第三个值
b          5           b5b5b5b5b5
*/--二、按name分组取val最小的值所在行的数据。
--方法1：
select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name
--方法2：
select a.* from tb a where not exists(select 1 from tb where name = a.name and val < a.val)
--方法3：
select a.* from tb a,(select name,min(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4：
select a.* from tb a inner join (select name , min(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val < a.val) order by a.name
/*
name       val         memo
---------- ----------- --------------------
a          1           a1--a的第一个值
b          1           b1--b的第一个值
*/--三、按name分组取第一次出现的行所在的数据。
select a.* from tb a where val = (select top 1 val from tb where name = a.name) order by a.name
/*
name       val         memo
---------- ----------- --------------------
a          2           a2(a的第二个值)
b          1           b1--b的第一个值
*/--四、按name分组随机取一条数据。
select a.* from tb a where val = (select top 1 val from tb where name = a.name order by newid()) order by a.name
/*
name       val         memo
---------- ----------- --------------------
a          1           a1--a的第一个值
b          5           b5b5b5b5b5
*/--五、按name分组取最小的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val < a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val < a.val having Count(*) < 2) order by a.name
/*
name       val         memo
---------- ----------- --------------------
a          1           a1--a的第一个值
a          2           a2(a的第二个值)
b          1           b1--b的第一个值
b          2           b2b2b2b2
*/--六、按name分组取最大的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val desc) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val > a.val having Count(*) < 2) order by a.name
/*
name       val         memo
---------- ----------- --------------------
a          2           a2(a的第二个值)
a          3           a3:a的第三个值
b          4           b4b4
b          5           b5b5b5b5b5
*/
--七，如果整行数据有重复，所有的列都相同。
/*
数据如下：
name val memo
a    2   a2(a的第二个值)
a    1   a1--a的第一个值
a    1   a1--a的第一个值
a    3   a3:a的第三个值
a    3   a3:a的第三个值
b    1   b1--b的第一个值
b    3   b3:b的第三个值
b    2   b2b2b2b2
b    4   b4b4
b    5   b5b5b5b5b5
*/
--在sql server 2000中只能用一个临时表来解决，生成一个自增列，先对val取最大或最小，然后再通过自增列来取数据。
--创建表并插入数据：
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a',    2,   'a2(a的第二个值)')
insert into tb values('a',    1,   'a1--a的第一个值')
insert into tb values('a',    1,   'a1--a的第一个值')
insert into tb values('a',    3,   'a3:a的第三个值')
insert into tb values('a',    3,   'a3:a的第三个值')
insert into tb values('b',    1,   'b1--b的第一个值')
insert into tb values('b',    3,   'b3:b的第三个值')
insert into tb values('b',    2,   'b2b2b2b2')
insert into tb values('b',    4,   'b4b4')
insert into tb values('b',    5,   'b5b5b5b5b5')
goselect * , px = identity(int,1,1) into tmp from tbselect m.name,m.val,m.memo from
(
  select t.* from tmp t where val = (select min(val) from tmp where name = t.name)
) m where px = (select min(px) from
(
  select t.* from tmp t where val = (select min(val) from tmp where name = t.name)
) n where n.name = m.name)drop table tb,tmp/*
name       val         memo
---------- ----------- --------------------
a          1           a1--a的第一个值
b          1           b1--b的第一个值(2 行受影响)
*/
--在sql server 2005中可以使用row_number函数，不需要使用临时表。
--创建表并插入数据：
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a',    2,   'a2(a的第二个值)')
insert into tb values('a',    1,   'a1--a的第一个值')
insert into tb values('a',    1,   'a1--a的第一个值')
insert into tb values('a',    3,   'a3:a的第三个值')
insert into tb values('a',    3,   'a3:a的第三个值')
insert into tb values('b',    1,   'b1--b的第一个值')
insert into tb values('b',    3,   'b3:b的第三个值')
insert into tb values('b',    2,   'b2b2b2b2')
insert into tb values('b',    4,   'b4b4')
insert into tb values('b',    5,   'b5b5b5b5b5')
goselect m.name,m.val,m.memo from
(
  select * , px = row_number() over(order by name , val) from tb
) m where px = (select min(px) from
(
  select * , px = row_number() over(order by name , val) from tb
) n where n.name = m.name)drop table tb/*
name       val         memo
---------- ----------- --------------------
a          1           a1--a的第一个值
b          1           b1--b的第一个值(2 行受影响)
*/

合并列值
原著：邹建
改编：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)  2007-12-16  广东深圳表结构，数据如下：
id    value
----- ------
1     aa
1     bb
2     aaa
2     bbb
2     ccc需要得到结果：
id     values
------ -----------
1      aa,bb
2      aaa,bbb,ccc
即：group by id, 求 value 的和（字符串相加）1. 旧的解决方法(在sql server 2000中只能用函数解决。)
--1. 创建处理函数
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
goCREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @r varchar(8000)
    SET @r = ''
    SELECT @r = @r + ',' + value FROM tb WHERE id=@id
    RETURN STUFF(@r, 1, 1, '')
END
GO-- 调用函数
SELECt id, value = dbo.f_str(id) FROM tb GROUP BY iddrop table tb
drop function dbo.f_str/*
id          value
----------- -----------
1           aa,bb
2           aaa,bbb,ccc
（所影响的行数为 2 行）
*/--2、另外一种函数.
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go--创建一个合并的函数
create function f_hb(@id int)
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + ',' + cast(value as varchar) from tb where id = @id
  set @str = right(@str , len(@str) - 1)
  return(@str)
End
go--调用自定义函数得到结果：
select distinct id ,dbo.f_hb(id) as value from tbdrop table tb
drop function dbo.f_hb/*
id          value
----------- -----------
1           aa,bb
2           aaa,bbb,ccc
（所影响的行数为 2 行）
*/2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。)
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
-- 查询处理
SELECT * FROM(SELECT DISTINCT id FROM tb)A OUTER APPLY(
        SELECT [values]= STUFF(REPLACE(REPLACE(
            (
                SELECT value FROM tb N
                WHERE id = A.id
                FOR XML AUTO
            ), '<N value="', ','), '"/>', ''), 1, 1, '')
)N
drop table tb/*
id          values
----------- -----------
1           aa,bb
2           aaa,bbb,ccc(2 行受影响)
*/--SQL2005中的方法2
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
goselect id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '')
from tb
group by id/*
id          values
----------- --------------------
1           aa,bb
2           aaa,bbb,ccc(2 row(s) affected)*/drop table tb

分拆列值原著：邹建
改编：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)  2007-12-16  广东深圳有表tb, 如下:
id          value
----------- -----------
1           aa,bb
2           aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id          value
----------- --------
1           aa
1           bb
2           aaa
2           bbb
2           ccc1. 旧的解决方法(sql server 2000)
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b SELECT A.id, SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[values], B.id, 1) = ','DROP TABLE #2. 新的解决方法(sql server 2005) create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go
SELECT A.id, B.value
FROM(
    SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb
)A
OUTER APPLY(
    SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v)
)BDROP TABLE tb/*
id          value
----------- ------------------------------
1           aa
1           bb
2           aaa
2           bbb
2           ccc(5 行受影响)
*/

/*
标题：分解字符串并查询相关数据
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-03-18
地点：广东深圳
说明：通过使用函数等方法分解字符串查询相关数据。问题：通过分解一个带某种符号分隔的字符串在数据库中查找相关数据。
例如 @str = '1,2,3',查询下表得到记录1,4,5,6
ID TypeID
1  1,2,3,4,5,6,7,8,9,10,11,12
2  2,3
3  3,7,8,9
4  2,6
5  4,5
6  6,7
*/
-----------------------------
create table tb (ID int , TypeID varchar(30))
insert into tb values(1 , '1,2,3,4,5,6,7,8,9,10,11,12')
insert into tb values(2 , '2,3')
insert into tb values(3 , '3,7,8,9')
insert into tb values(4 , '2,6')
insert into tb values(5 , '4,5')
insert into tb values(6 , '6,7')
go
-----------------------------
--如果仅仅是一个,如@str = '1'.
declare @str as varchar(30)
set @str = '1'
select * from tb where charindex(',' + @str + ',' , ',' + TypeID + ',') > 0
select * from tb where ',' + TypeID + ',' like '%,' + @str + ',%'
/*
ID          TypeID
----------- ------------------------------
1           1,2,3,4,5,6,7,8,9,10,11,12
（所影响的行数为 1 行）
*/-----------------------------
--如果包含两个,如@str = '1,2'.
declare @str as varchar(30)
set @str = '1,2'
select * from tb where charindex(',' + left(@str , charindex(',' , @str) - 1) + ',' , ',' + typeid + ',') > 0 or
  charindex(',' + substring(@str , charindex(',' , @str) + 1 , len(@str)) + ',' , ',' + typeid + ',') > 0
select * from tb where ',' + typeid + ',' like '%,' + left(@str , charindex(',' , @str) - 1) + ',%' or
  ',' + typeid + ',' like '%,' + substring(@str , charindex(',' , @str) + 1 , len(@str)) + ',%'
/*
ID          TypeID
----------- ------------------------------
1           1,2,3,4,5,6,7,8,9,10,11,12
2           2,3
4           2,6
（所影响的行数为 3 行）
*/-------------------------------------------
--如果包含三个或四个，用PARSENAME函数来处理.
declare @str as varchar(30)
set @str = '1,2,3,4'
select * from tb where
  charindex(',' + parsename(replace(@str , ',' , '.') , 4) + ',' , ',' + typeid + ',') > 0 or
  charindex(',' + parsename(replace(@str , ',' , '.') , 3) + ',' , ',' + typeid + ',') > 0 or
  charindex(',' + parsename(replace(@str , ',' , '.') , 2) + ',' , ',' + typeid + ',') > 0 or
  charindex(',' + parsename(replace(@str , ',' , '.') , 1) + ',' , ',' + typeid + ',') > 0
select * from tb where
  ',' + typeid + ',' like '%,' + parsename(replace(@str , ',' , '.') , 4) + ',%' or
  ',' + typeid + ',' like '%,' + parsename(replace(@str , ',' , '.') , 3) + ',%' or
  ',' + typeid + ',' like '%,' + parsename(replace(@str , ',' , '.') , 2) + ',%' or
  ',' + typeid + ',' like '%,' + parsename(replace(@str , ',' , '.') , 1) + ',%'
/*
ID          TypeID
----------- ------------------------------
1           1,2,3,4,5,6,7,8,9,10,11,12
2           2,3
3           3,7,8,9
4           2,6
5           4,5
（所影响的行数为 5 行）
*/---------------------------------------
--如果超过四个，则只能使用函数或动态SQL来分解并查询数据。
/*
名称：fn_split函数.
功能：实现字符串分隔功能的函数
*/
create function dbo.fn_split(@inputstr varchar(8000), @seprator varchar(10))
returns @temp table (a varchar(200))
as
begin
  declare @i int
  set @inputstr = rtrim(ltrim(@inputstr))
  set @i = charindex(@seprator , @inputstr)
  while @i >= 1
  begin
    insert @temp values(left(@inputstr , @i - 1))
    set @inputstr = substring(@inputstr , @i + 1 , len(@inputstr) - @i)
    set @i = charindex(@seprator , @inputstr)
  end
  if @inputstr <> '\'
  insert @temp values(@inputstr)
  return
end
go--调用
declare @str as varchar(30)
set @str = '1,2,3,4,5'select distinct m.* from tb m,
(select * from dbo.fn_split(@str,',')) n
where charindex(',' + n.a + ',' , ',' + m.typeid + ',') > 0drop table tb
drop function dbo.fn_split /*
ID          TypeID
----------- ------------------------------
1           1,2,3,4,5,6,7,8,9,10,11,12
2           2,3
3           3,7,8,9
4           2,6
5           4,5
（所影响的行数为 5 行）
*/------------------------------------------
--使用动态SQL的语句。
declare @str varchar(200)
declare @sql as varchar(1000)
set @str = '1,2,3,4,5'
set @sql = 'select ''' + replace(@str , ',' , ''' as id union all select ''')
set @sql = @sql + ''''
set @sql = 'select distinct a.* from tb a , (' + @sql + ') b where charindex(' + ''','' + b.id + ' + ''',''' + ' , ' + ''','' + a.typeid + ' + ''',''' + ') > 0 '
exec (@sql)
/*
ID          TypeID
----------- ------------------------------
1           1,2,3,4,5,6,7,8,9,10,11,12
2           2,3
3           3,7,8,9
4           2,6
5           4,5
（所影响的行数为 5 行）
*/

--返程问题,找出雇员从本地出发后直接返回的情况
create table trav(name nvarchar(10),date datetime,comefrom nvarchar(10),destin nvarchar(10),id int)
insert into trav select '张三','2007-01-01','上海','广州',1
insert into trav select '李四','2007-01-01','上海','广州',2
insert into trav select '李四','2007-02-01','上海','成都',3
insert into trav select '张三','2007-01-15','广州','上海',4
insert into trav select '张三','2007-02-06','上海','广州',5
insert into trav select '张三','2007-02-18','广州','上海',6
go
select a.name,a.date,a.comefrom,a.destin,b.date,b.comefrom,b.destin
from trav a inner join trav b on a.name=b.name and a.comefrom=b.destin and a.destin=b.comefrom where a.id<b.id
and not exists(select 1 from trav where comefrom=b.comefrom and date<b.date and date>a.date)
go
drop table trav
/*
name       date                    comefrom   destin     date                    comefrom   destin
---------- ----------------------- ---------- ---------- ----------------------- ---------- ----------
张三         2007-01-01 00:00:00.000 上海         广州         2007-01-15 00:00:00.000 广州         上海
张三         2007-02-06 00:00:00.000 上海         广州         2007-02-18 00:00:00.000 广州         上海(2 行受影响)
*/

--库存先进先出简单例子：create table t(
id int identity(1,1),
name varchar(50),--商品名称
j int,        --入库数量
c int,        --出库数量
jdate datetime --入库时间
)
insert into t(name,j,c,jdate) select  'A',100,0,'2007-12-01'
insert into t(name,j,c,jdate) select  'A',200,0,'2008-01-07'
insert into t(name,j,c,jdate) select  'B',320,0,'2007-12-21'
insert into t(name,j,c,jdate) select  'A',100,0,'2008-01-15'
insert into t(name,j,c,jdate) select  'B',90,0,'2008-02-03'
insert into t(name,j,c,jdate) select  'A',460,0,'2008-02-01'
insert into t(name,j,c,jdate) select  'A',510,0,'2008-03-01'
gocreate proc wsp
@name varchar(50),--商品名称
@cost int         --销售量
as
--先得出该货物的库存是否够
declare @spare float --剩余库存
select @spare=sum(j)-sum(c) from t where name=@name
if(@spare>=@cost)
begin
    --根据入库日期采用先进先出原则对货物的库存进行处理
    update t set c=
    case when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where name=@name and jdate<=a.jdate and j!=c)>=0
    then a.j
    else
        case when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where name=@name and jdate<a.jdate and j!=c)<0 then 0
        else (select @cost-isnull(sum(j),0)+isnull(sum(c),0)+a.c from t where name=@name and jdate<a.jdate and j!=c)
        end
    end
    from t a where name=@name and j!=c
end
else
    raiserror('库存不足',16,1)
    return
go
--测试：exec wsp @name='A',@cost=180
select * from t
--drop table t
--drop proc wsp

1. 查看数据库的版本
select @@version 2. 查看数据库所在机器操作系统参数
exec master..xp_msver 3. 查看数据库启动的参数
sp_configure 4. 查看数据库启动时间
select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1 查看数据库服务器名和实例名
print 'Server Name...............: ' + convert(varchar(30),@@SERVERNAME)
print 'Instance..................: ' + convert(varchar(30),@@SERVICENAME)
5. 查看所有数据库名称及大小
sp_helpdb 重命名数据库用的SQL
sp_renamedb 'old_dbname', 'new_dbname' 6. 查看所有数据库用户登录信息
sp_helplogins 查看所有数据库用户所属的角色信息
sp_helpsrvrolemember 修复迁移服务器时孤立用户时,可以用的fix_orphan_user脚本或者LoneUser过程更改某个数据对象的用户属主
sp_changeobjectowner [@objectname =] 'object', [@newowner =] 'owner' 注意: 更改对象名的任一部分都可能破坏脚本和存储过程。把一台服务器上的数据库用户登录信息备份出来可以用add_login_to_aserver脚本 7. 查看链接服务器
sp_helplinkedsrvlogin 查看远端数据库用户登录信息
sp_helpremotelogin 8.查看某数据库下某个数据对象的大小
sp_spaceused @objname 还可以用sp_toptables过程看最大的N(默认为50)个表查看某数据库下某个数据对象的索引信息
sp_helpindex @objname 还可以用SP_NChelpindex过程查看更详细的索引情况
SP_NChelpindex @objname clustered索引是把记录按物理顺序排列的，索引占的空间比较少。
对键值DML操作十分频繁的表我建议用非clustered索引和约束，fillfactor参数都用默认值。
查看某数据库下某个数据对象的的约束信息
sp_helpconstraint @objname 9.查看数据库里所有的存储过程和函数
use @database_name
sp_stored_procedures
查看存储过程和函数的源代码
sp_helptext '@procedure_name' 查看包含某个字符串@str的数据对象名称
select distinct object_name(id) from syscomments where text like '%@str%' 创建加密的存储过程或函数在AS前面加WITH ENCRYPTION参数解密加密过的存储过程和函数可以用sp_decrypt过程 10.查看数据库里用户和进程的信息
sp_who
查看SQL Server数据库里的活动用户和进程的信息
sp_who 'active'
查看SQL Server数据库里的锁的情况
sp_lock 进程号1--50是SQL Server系统内部用的,进程号大于50的才是用户的连接进程.
spid是进程编号,dbid是数据库编号,objid是数据对象编号
查看进程正在执行的SQL语句
dbcc inputbuffer () 推荐大家用经过改进后的sp_who3过程可以直接看到进程运行的SQL语句
sp_who3 检查死锁用sp_who_lock过程
sp_who_lock 11.收缩数据库日志文件的方法
收缩简单恢复模式数据库日志，收缩后@database_name_log的大小单位为M
backup log @database_name with no_log
dbcc shrinkfile (@database_name_log, 5)
12.分析SQL Server SQL 语句的方法: set statistics time {on | off}
set statistics io {on | off}
图形方式显示查询执行计划在查询分析器->查询->显示估计的评估计划(D)-Ctrl-L 或者点击工具栏里的图形文本方式显示查询执行计划
set showplan_all {on | off} set showplan_text { on | off }
set statistics profile { on | off } 13.出现不一致错误时，NT事件查看器里出3624号错误，修复数据库的方法先注释掉应用程序里引用的出现不一致性错误的表，然后在备份或其它机器上先恢复然后做修复操作 alter database [@error_database_name] set single_user 修复出现不一致错误的表 dbcc checktable('@error_table_name',repair_allow_data_loss) 或者可惜选择修复出现不一致错误的小型数据库名 dbcc checkdb('@error_database_name',repair_allow_data_loss)
alter database [@error_database_name] set multi_user
CHECKDB 有3个参数:
repair_allow_data_loss 包括对行和页进行分配和取消分配以改正分配错误、结构行或页的错误，
以及删除已损坏的文本对象，这些修复可能会导致一些数据丢失。
修复操作可以在用户事务下完成以允许用户回滚所做的更改。
如果回滚修复，则数据库仍会含有错误，应该从备份进行恢复。
如果由于所提供修复等级的缘故遗漏某个错误的修复，则将遗漏任何取决于该修复的修复。
修复完成后，请备份数据库。
repair_fast 进行小的、不耗时的修复操作，如修复非聚集索引中的附加键。
这些修复可以很快完成，并且不会有丢失数据的危险。
repair_rebuild 执行由 repair_fast 完成的所有修复，包括需要较长时间的修复（如重建索引）。
执行这些修复时不会有丢失数据的危险。以上脚本来源于: http://www.51windows.net/data/files/file_797.asp

--bom结构，查找节点下所有子节点：create table os(id int,parentid int,desn varchar(10))
insert into os select 1,0,'体育用品'
insert into os select 2,0,'户外运动'
insert into os select 3,1,'篮球'
insert into os select 4,1,'足球'
insert into os select 5,2,'帐篷'
insert into os select 6,2,'登山鞋'
insert into os select 7,0,'男士用品'
insert into os select 8,7,'刮胡刀'
insert into os select 9,3,'大号篮球'--求个节点下所有子节点：
create function f_cid(@id int)
returns varchar(500)
as
begin
     declare @t table(id int,parentid int,desn varchar(10),lev int)
     declare @lev int
     set @lev=1
     insert into @t select *,@lev from  os where id=@id
     while(@@rowcount>0)
     begin
          set @lev=@lev+1
          insert into @t select a.*,@lev from os a,@t b
          where a.parentid=b.id and b.lev=@lev-1
     end
     declare @cids varchar(500)
     select @cids=isnull(@cids+',','')+ltrim(id) from @t order by lev
     return @cids
end
go--调用函数
select *,ids=dbo.f_cid(id) from os
--得到每个节点路径：
create proc wsp2
@id int
as
select *,cast(' ' as varchar(10)) fullpath  into #os from os
DECLARE @i int,@j int
set @i=0
set @j=1
select @i=max(parentid) from #os
update #os set fullpath=id
while @j<=@i
begin
       update #os set fullpath=a.fullpath+','+ltrim(#os.id)
            from #os inner join #os a on #os.parentid=a.id
       where #os.parentid=@j
       set @j=@j+1
end
select * from #os
go
--调用存储过程
exec wsp2 1

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER trigger [tri_updatesalary_Mem_Type]
on [dbo].[SalaryIncrease]
after  insert
as
declare @i int
set @i=@@identity

update m
set m.Mem_Type=case when s.SMark>500000 then '退休会员'--500000
                   when s.SMark>400000 then '钻石五级'--400000
                   when s.SMark>300000 then '钻石四级'--300000
                   when s.SMark>200000 then '钻石三级'--200000
                   when s.SMark>100000 then '钻石二级'--100000
                   when s.SMark>50000 then '钻石一级'--50000
                   when s.SMark>40000 then '五星级'--40000
                   when s.SMark>30000 then '四星级'--30000
                   when s.SMark>20000 then '三星级'--20000
                   when s.SMark>10000 then '二星级'--10000
                   when s.SMark>5000 then '一星级'--5000
                   else '一般VIP会员'
               end
from MemberInfo m
  join (select s.SCardName,sum(s.SMark) as SMark
          from SalaryIncrease s join inserted i on s.SCardName=i.SCardName group by s.SCardName) s
    on m.Mem_Num=s.SCardName

--if exists(select * from MemberInfo m join inserted i on m.Mem_Num=i.SCardName and m.Mem_Mark>100 )
if exists (select  m.SCardName,sum(m.ShopMark)as  from(select m.SCardName,m.ShopMark  from ShoppingMark m join inserted i on m.SCardName=i.SCardName
where year(m.SDate)=year(getdate()) and month(m.SDate)=month(getdate())) m
group by m.SCardName  having sum(m.ShopMark)>100)
/**/
/* --最新的(我写的,上面是整合魅影的)select * from ShoppingMark SM join (select m.SCardName,sum(m.ShopMark) as Mark from ShoppingMark m join inserted i on m.SCardName=i.SCardName group by m.SCardName) s
on SM.SCardName=s.ScardName where month(SDate)=month(getdate()) and year(SDate)=year(getdate()) and s.Mark>100*/
begin
update s
   set s.SIncease=case
                      --积分大于1000就是星级.所以不用判断是否是星级
                   when m.Mem_Type<>'一般VIP会员' then s.SMark*0.3
                   else case
                          when s.SMark>4000 then
                               s.SMark*0.3
                        --  when s.SMark>4000 then
                             --  200*0.2+200*0.23+200*0.25+200*0.28+(s.SMark-800)*0.3
                          when s.SMark>3000 then
                               1000*0.2+1000*0.23+1000*0.25+(s.SMark-600)*0.28
                          when s.SMark>2000 then
                               1000*0.2+100*0.23+(s.SMark-400)*0.25
                          when s.SMark>1000 then
                               (s.SMark-200)*.023+1000*0.2
                          else  s.SMark*0.2
                       end
                  end
from SalaryIncrease as s
join inserted i
    on s.SCardName=i.SCardName
  join MemberInfo m
    on (i.SCardName=m.Mem_Num and s.SID=@i) or (i.SCardName=m.Mem_Num and s.SIncease=0)
    end
--go

create table tb(id varchar(3) , pid varchar(3) , name varchar(10))
insert into tb values('001' , null  , '广东省')
insert into tb values('002' , '001' , '广州市')
insert into tb values('003' , '001' , '深圳市')
insert into tb values('004' , '002' , '天河区')
insert into tb values('005' , '003' , '罗湖区')
insert into tb values('006' , '003' , '福田区')
insert into tb values('007' , '003' , '宝安区')
insert into tb values('008' , '007' , '西乡镇')
insert into tb values('009' , '007' , '龙华镇')
insert into tb values('010' , '007' , '松岗镇')
go --查询指定节点及其所有子节点的函数
create function f_cid(@ID varchar(3)) returns @t_level table(id varchar(3) , level int)
as
begin
  declare @level int
  set @level = 1
  insert into @t_level select @id , @level
  while @@ROWCOUNT > 0
  begin
    set @level = @level + 1
    insert into @t_level select a.id , @level
    from tb a , @t_Level b
    where a.pid = b.id and b.level = @level - 1
  end
  return
end
go --调用函数查询001(广东省)及其所有子节点
select a.* from tb a , f_cid('001') b where a.id = b.id order by a.id
/*
id  pid  name
---- ---- ----------
001  NULL 广东省
002  001  广州市
003  001  深圳市
004  002  天河区
005  003  罗湖区
006  003  福田区
007  003  宝安区
008  007  西乡镇
009  007  龙华镇
010  007  松岗镇（所影响的行数为 10 行）
*/ --调用函数查询002(广州市)及其所有子节点
select a.* from tb a , f_cid('002') b where a.id = b.id order by a.id
/*
id  pid  name
---- ---- ----------
002  001  广州市
004  002  天河区（所影响的行数为 2 行）
*/ --调用函数查询003(深圳市)及其所有子节点
select a.* from tb a , f_cid('003') b where a.id = b.id order by a.id
/*
id  pid  name
---- ---- ----------
003  001  深圳市
005  003  罗湖区
006  003  福田区
007  003  宝安区
008  007  西乡镇
009  007  龙华镇
010  007  松岗镇（所影响的行数为 7 行）
*/ drop table tb
drop function f_cid

/*
标题：日期分表查询(version 1.0)
网名：悬崖边的舞者
时间：2008-07-14
地点：天津
说明：以日期或月份分表进行多日、多月查询连表查询的有关写法
*/Create Procedure  Search
@date1 datetime, --起始时间
@date2 datetime  --终止时间
as
declare @date1New datetime,
@rq varchar(6),
@i int , --记录循环次数
@s varchar(5000) --根据表多少可以扩大它
set @rq=convert(varchar,@date1,12) --把时间格式化比如2008-06-20 变成了080620
set @s='select * from sensor'+@rq --初始化 @s='select * from sensor080620' 这样第一个表就有了

set @i=datediff(d,@date1,@date2) --日期相减while @i>0
begin
--如果是非常重要的系统可以在这里加上该物理表是否存在的语句
--if   exists(select   *   from   dbo.sysobjects   where
--id   =   object_id(N'c')   and   OBJECTPROPERTY(id,N'IsUserTable')=1)
--begin
--SELECT '存在 '
--end

set @date1New= dateadd(day,@i,@date1)
set @rq=convert(varchar,@date1New,12)
--如果是按月进行查询就修改上面
set @s= @s+' union all select * from sensor'+@rq
set @i=@i-1 --累加
end

select @s
--exec(@S)
go
exec Search '2008-06-20 00:11:11.000','2008-07-25 00:11:11.000'
  drop Procedure  Search

/*
08.09.20知识点：SQL函数主要分为两中类型 1.单行函数(也叫标量函数) 2.多行函数
标量函数：对单一值操作，返回单一值。只要表达式有效即可使用标量函数。我们把标量函数的分类列出来：
配置函数       返回当前配置信息
游标函数       返回游标信息
日期和时间函数   对日期和时间输入值进行操作，返回一个字符串、数字或日期和时间值
数学函数       对作为函数参数提供的输入值进行计算，返回一个数字值
元数据函数     返回有关数据库和数据库对象的信息
安全函数       返回有关用户和角色的信息
字符串函数     对字符串(char或varchar)输入值执行操作,返回一个字符串或数字值
系统函数       执行操作并返回有关Microsoft SQL Server中的值、对象和设置的信息
系统统计函数   返回系统的统计信息
文本和图象函数   对文本或图象进行操作并返回有关信息聚合函数是对一组值进行操作，跟标量函数不同.
行集函数是可以向SQL语句中表引用一样使用，具体看文档了....
下面具体看例子了
*/select emp_id,UPPER(lname),job_id
from employee
where lname='chang'
/*大小写处理函数*/
select emp_id,LOWER(lname),job_id
from employee
where lname='chang'select emp_id,fname+lname NAME,  /*使用字符处理函数*/
job_id,LEN(lname) Length,
CHARINDEX('a',lname)"Contains 'a'?"
from employee
where SUBSTRING(emp_id,1,2)='MA'
/*
运算符 + :连接值在一起
substring :选取给定位置和长度的子字符串
len  :以数字值显示一个字符串的长度
charIndex  :找到一个给定字符的数字位置
Replicate  :用给定的字符替换给定长度的字符串
Ltrim和Rtrim   :从一个字符串中去除头或尾的字符
*/select ROUND(2008.092023,2),/*数字函数ROUND四舍五入*/
ROUND(2008.092023,0),
ROUND(2008.092023,-1)
/*注意奇数偶数的进位不同它也可以用语日期函数处理*/select lname,job_lvl,job_lvl%50/*使用%(模)函数经常用与确定一个值是奇数还是偶数*/
from employee
where job_lvl=5select lname,hire_date/*日期的使用*/
from employee
where lname like 'A%'select lname,hire_date+365 AS "One Year"/*用日期做算术运算该例显示所有job_id=10的雇员的名字和其受雇满一年的日期*/
from employee
where job_id=10select lname,  /*CONVERT函数用于日期转换*/
CONVERT(varchar(10),hire_date,20)
AS HIREDATE
from employeeselect SUBSTRING(title,1,30) AS Title,ytd_sales/*对数字使用CAST函数*/
from titles
where CAST(ytd_sales AS char(20)) LIKE '3%'
/*
检索书名：这些图书的截止当前销售额的第一数字为3，并将这些图书的ytd_sales转换为char(20)
CAST(number,数据类型)
*/select CONVERT(varchar(10),price,0) PRICE/*对数字使用CONVERT函数*/
from titles
where title_id='BU1111'select CAST('2008-09-20 23:57' AS datetime)
/*使用CAST或CONVERT函数转换字符串到日期*/
select CONVERT(datetime,'2008-09-20 23:59')select title,ytd_sales/*使用带有LIKE子句的CAST*/
from titles
where
CAST(ytd_sales AS char(20)) LIKE '15%'
AND type='trad_cook'select title,ISNULL(price,0) Price,/*使用ISNULL函数*/
ISNULL(price*12,0) 'Ten Price'
from titles
/*为了计算所有图书的价格，在进行算术运算之前必须转换空值为一个数，ISNULL函数用来转换空值为零*/
select                      /*使用CASE表达式*/
CASE
when price IS NULL THEN 'Not yet priced'
when price<10 THEN 'Very Reasonable Title'
when price>=10 and price<20 THEN 'Coffee Table Title'
else 'Expensive book!'
END AS 'Price Category',  /*这个逗号不敢忘*/
CONVERT(varchar(20),title) AS "shorttened Title"
from pubs.dbo.titles
ORDER BY price
/*
使用CASE表达式使得if-then-else条件判断很容易实现
*/

select          /*使用CASE处理条件数据*/
CASE type
when 'popular_comp' then 'Popular Computing'
when 'mod_cook' then 'Modern Coking'
        when 'business' then 'Business'
when 'psychology' then 'Psychology'
when 'trad_cook' then 'Traditional Cooking'
else 'Not yet categorized'
END AS Category,
CONVERT(varchar(30),titles) AS "Shortened Title",
Price AS price
from titles
where price IS NOT NULL
ORDER BY 1
/*
小结下：掌握用函数对数据的计算
用函数修饰不同的数据项
用函数改变数据格式
用函数改变列数据类型
使用ISNULL函数
使用if-then-else逻辑
*/
/*最后一个例子有问题先不管了掌握语法就OK*/

/*08.09.20*/select * from employeeuse master /*显示表结构*/
exec sp_helpdb
GOuse pubs
exec sp_help
GOuse pubs
exec sp_help employee
GOselect lname,job_lvl
from employee
where job_lvl<=50select lname,job_lvl/*使用BETWEEN条件*/
from employee
where job_lvl BETWEEN 100 AND 200select lname,job_id,job_lvl/*使用IN成员条件测试在列表中的值*/
from employee
where job_lvl IN (100,170,200)select emp_id,lname,job_id,job_lvl/*使用NOT操作*/
from employee
where job_lvl NOT IN(100,170,200)select title_id,title,type,price
from titles
where price IS NULLselect lname,job_lvl,job_lvl/*优先规则AND在前*/
from employee
where job_lvl=5
OR job_lvl=7
AND job_lvl>160select lname,job_lvl,job_lvl/*用圆括号强制优先权*/
from employee
where (job_lvl=5
OR job_lvl=7)
AND job_lvl>160select lname,job_id,job_lvl,hire_date/*用ORDER BY子句进行排序(升序排列)*/
from employee
ORDER BY hire_date select lname,job_id,job_lvl,hire_date/*用DESC降序排列*/
from employee
ORDER BY hire_date DESCselect emp_id,lname,job_lvl*2 "Double job_lvl"/*用列别名排序*/
from employee
ORDER BY "Double job_lvl"select lname,job_id,job_lvl/*多列排序*/
from employee
ORDER BY job_id,job_lvl DESC/*
小结下：主要掌握WHERE子句限制输出行
使用比较条件
使用BETWEEN，IN，AND,NOT,OR操作
最后掌握使用ORDER BY子句排序输出行
所有操作在数据库pubs里进行
*/

--改变用户的锁定状态 (可适合一切 bit 类型的字段)UPDATE tb_Table SET IsLook=Abs(IsLook-1) WHERE Id=@id

1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)
法一：select * into b from a where 1 <>1
法二：select top 0 * into b from a 2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)
insert into b(a, b, c) select d,e,f from b; 3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
例子：..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. 4、说明：子查询(表名1：a 表名2：b)
select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3) 5、说明：显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b 6、说明：外连接查询(表名1：a 表名2：b)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c 7、说明：在线视图查询(表名1：a )
select * from (SELECT a,b,c FROM a) T where t.a > 1; 8、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 数值1 and 数值2 9、说明：in 的使用方法
select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’) 10、说明：两张关联表，删除主表中已经在副表中没有的信息
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) 11、说明：四表联查问题：
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where ..... 12、说明：日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 13、说明：一条sql 语句搞定数据库分页
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 14、说明：前10条记录
select top 10 * form table1 where 范围 15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) 16、说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表
(select a from tableA ) except (select a from tableB) except (select a from tableC) 17、说明：随机取出10条数据
select top 10 * from tablename order by newid() 18、说明：随机选择记录
select newid() 19、说明：删除重复记录
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) 20、说明：列出数据库里所有的表名
select name from sysobjects where type='U' 21、说明：列出表里的所有的
select name from syscolumns where id=object_id('TableName') 22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。
select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type
显示结果：
type vender pcs
电脑 A 1
电脑 A 1
光盘 B 2
光盘 A 2
手机 B 3
手机 C 3 23、说明：初始化表table1
TRUNCATE TABLE table1 24、说明：选择从10到15的记录
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc

--功能概述：显示某一表的结构DECLARE @tableName nvarchar(100)
SET @tableName ='brand'--mssql2005
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 sys.extended_properties g  ON a.id=g.major_id AND a.colid = g.major_id
WHERE d.name=@tableName
ORDER BY a.id,a.colorder

--功能概述:重新编译一个表上的所有索引alter index all on order_master rebuild

select 一日三餐 from daylylife

--查看数据库中所有的表
sp_MSforeachtable 'Select Top 1 * From ?'
--所有数据库的
sp_MSforeachdb

请问那个 Go 10000 如何理解啊？？？？？
------------------------------------------------------------CREATE TABLE #t (id int IDENTITY(1,1) PRIMARY KEY,nums int)
GOINSERT INTO #t
SELECT abs(CHECKSUM(NEWID())%100000)
GO 10000 ----生成10000笔资料,可以是任意一数字
------------------------------------------------------------

*--比较两个数据库的表结构差异 --*/
/*--调用示例 exec p_comparestructure 'xzkh_model','xzkh_new'
--*/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_comparestructure]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_comparestructure]
GO create proc p_comparestructure
@dbname1 varchar(250), --要比较的数据库名1
@dbname2 varchar(250) --要比较的数据库名2
as
create table #tb1(表名1 varchar(250),字段名 varchar(250),序号 int,标识 bit,主键 bit,类型 varchar(250),
占用字节数 int,长度 int,小数位数 int,允许空 bit,默认值 varchar(500),字段说明 varchar(500)) create table #tb2(表名2 varchar(250),字段名 varchar(250),序号 int,标识 bit,主键 bit,类型 varchar(250),
占用字节数 int,长度 int,小数位数 int,允许空 bit,默认值 varchar(500),字段说明 varchar(500)) --得到数据库1的结构
exec('insert into #tb1 SELECT
表名=d.name,字段名=a.name,序号=a.colid,
标识=case when a.status=0x80 then 1 else 0 end,
主键=case when exists(SELECT 1 FROM '+@dbname1+'..sysobjects where xtype=''PK'' and name in (
  SELECT name FROM '+@dbname1+'..sysindexes WHERE indid in(
  SELECT indid FROM '+@dbname1+'..sysindexkeys WHERE id = a.id AND colid=a.colid
  ))) then 1 else 0 end,
类型=b.name, 占用字节数=a.length,长度=a.prec,小数位数=a.scale, 允许空=a.isnullable,
默认值=isnull(e.text,''''''),字段说明=isnull(g.[value],'''''')
FROM '+@dbname1+'..syscolumns a
left join '+@dbname1+'..systypes b on a.xtype=b.xusertype
inner join '+@dbname1+'..sysobjects d on a.id=d.id  and d.xtype=''U'' and  d.name <>''dtproperties''
left join '+@dbname1+'..syscomments e on a.cdefault=e.id
left join '+@dbname1+'..sysproperties g on a.id=g.id and a.colid=g.smallid
order by a.id,a.colorder') --得到数据库2的结构
exec('insert into #tb2 SELECT
表名=d.name,字段名=a.name,序号=a.colid,
标识=case when a.status=0x80 then 1 else 0 end,
主键=case when exists(SELECT 1 FROM '+@dbname2+'..sysobjects where xtype=''PK'' and name in (
  SELECT name FROM '+@dbname2+'..sysindexes WHERE indid in(
  SELECT indid FROM '+@dbname2+'..sysindexkeys WHERE id = a.id AND colid=a.colid
  ))) then 1 else 0 end,
类型=b.name, 占用字节数=a.length,长度=a.prec,小数位数=a.scale, 允许空=a.isnullable,
默认值=isnull(e.text,''''''),字段说明=isnull(g.[value],'''''')
FROM '+@dbname2+'..syscolumns a
left join '+@dbname2+'..systypes b on a.xtype=b.xusertype
inner join '+@dbname2+'..sysobjects d on a.id=d.id  and d.xtype=''U'' and  d.name <>''dtproperties''
left join '+@dbname2+'..syscomments e on a.cdefault=e.id
left join '+@dbname2+'..sysproperties g on a.id=g.id and a.colid=g.smallid
order by a.id,a.colorder')
--and not exists(select 1 from #tb2 where 表名2=a.表名1)
select 比较结果=case when a.表名1 is null and b.序号=1 then '库1缺少表：'+b.表名2
  when b.表名2 is null and a.序号=1 then '库2缺少表:'+a.表名1
  when a.字段名 is null and exists(select 1 from #tb1 where 表名1=b.表名2) then '库1 ['+b.表名2+'] 缺少字段：'+b.字段名
  when b.字段名 is null and exists(select 1 from #tb2 where 表名2=a.表名1) then '库2 ['+a.表名1+'] 缺少字段：'+a.字段名
  when a.标识 <>b.标识 then '标识不同'
  when a.主键 <>b.主键 then '主键设置不同'
  when a.类型 <>b.类型 then '字段类型不同'
  when a.占用字节数 <>b.占用字节数 then '占用字节数'
  when a.长度 <>b.长度 then '长度不同'
  when a.小数位数 <>b.小数位数 then '小数位数不同'
  when a.允许空 <>b.允许空 then '是否允许空不同'
  when a.默认值 <>b.默认值 then '默认值不同'
  when a.字段说明 <>b.字段说明 then '字段说明不同'
else '' end,
*
from #tb1 a
full join #tb2 b on a.表名1=b.表名2 and a.字段名=b.字段名
where a.表名1 is null or a.字段名 is null or b.表名2 is null or b.字段名 is null
or a.标识 <>b.标识 or a.主键 <>b.主键 or a.类型 <>b.类型
or a.占用字节数 <>b.占用字节数 or a.长度 <>b.长度 or a.小数位数 <>b.小数位数
or a.允许空 <>b.允许空 or a.默认值 <>b.默认值 or a.字段说明 <>b.字段说明
order by isnull(a.表名1,b.表名2),isnull(a.序号,b.序号)--isnull(a.字段名,b.字段名)
go

printf("hello world");
够经典吧

...
hao meng

对查询结果进行随机排序
select * from dbo.Class order by newid()

求字串中所含某字符或某子串的个数
declare @a varchar(100),@b varchar(20)
select @a='abcdefbcmnbcde',@b='bc'
select (len(@a)-len(replace(@a,@b,'')))/len(@b)

hehe，没有个明确主题，内容杂七杂八，好一个乱字了得。读过，有些有趣的东西，值得一看

String you;
if(you == man){
doWork();
}
else{
goHomeToBornBaby();
}

print convert(char(8),getdate(), 112)

//删除表
if exists (select * from dbo.sysobjects
  where id = object_id(N'[dbo].[TB_STATION]')
  and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TB_TRADE]//删除索引
if exists (select * from dbo.sysobjects
  where id = object_id(N'[dbo].[IX_TD_TRADE]'))
drop INDEX [dbo].[IX_TD_TRADE]
GO//建表
Create TABLE [dbo].[TB_TRADE](
ZID int IDENTITY not null, //自增主键
...
ZDATE datetime default GetDate(),             /* 制单日期默认系统日期 */
constraint PK_TD_CONSUMER  primary key (ZID)
...)
//建立索引
CREATE INDEX IX_TD_TRADE ON TD_TRADE(ZSTATION_ID, ZTRADETYPE, ZCID, ZUSER_ID)
go

看见N多的例子最起码是5年前就出现了，估计我Copy一下后就能变成我写的了。

for(int i=0;i<count;i++)
{

}

调试易

经典短小代码收集

解决方案 »