本文不讨论数据库设计,不讨论性能优化,也不讨论存储过程(用好了语言套上特定语法那它就是存储过程),触发器,函数之类的.本文也不讲游标,循环(因为理论上,只要游标能实现的在前台程序中使用数据访问组件如ADO,ADO.NET等等都可以实现,并且借助应用程序语言所提供的条件,您拥有更灵活处理方式. 同时笔者也建议若没有特殊要求,建议您权衡某些功能的实现是否必须要放在数据库端完成)
为突出主题,本文只讲查询,并尽量试着用笔者认为效率最高的查询语句来完成,因为笔者水平有限并不确定使用的就是最有效的.
本文适合有一定基础并且希望能提高自己数据检索水平的朋友（您的水平不错的话，可以点右上角的X了.）. 本文旨在阐述数据检索心得,而不作为手册,所以不会面面俱到,阅读本文同时,请确保您有一本手册或联机丛书在傍边.
由于水平有限,文中难免有错误的地方, 欢迎指正.
Let’s go
(文中错误语句以绿色标识，有任何问题也可以一起讨论
MSN:fcuandy@163.net
http://Forum.devms.com
http://www.idotnet.org)
1,基础
(1) MSSQL为关系数据库,为此您必须了解一些概念
a.关系模型
二维表
记录（行）
字段（列，属性）
表之间的关系
ID GroupID UserName PassWord
1 3 James Doyouknowmypass
2 1 Merry Merry1984
3 1 Andy MyLove
以上是一个二维表 UserInfo, 请确认您已经确认它们在关系模型中的名称.
b.语言
数据定义 DDL CREATE,DROP,ALTER
数据操纵 DML SELECT,INSERT,UPDATE,DELETE
数据控制 DCL GRANT,REVOKE
T-SQL
文本讨论数据检索,所以主要会用到 DDL,DML,T-SQL这些. 请确认您已常握了常用的基础语法.能说出下面的语句各有什么功能.
SELECT * FROM UserInfo WHERE ID=1
SELECT UserName FROM UserInfo ORDER BY ID DESC
UPDATE UserInfo SET PassWord=’xxxxxx’ WHERE UserName=’James’
DELETE FROM UserInfo WHERE ID=2
DROP TABLE UserInfo
TRUNCATE TABLE UserInfo
针对您对二维表的理解,想象一下,为什么下面这个语句会出错
SELECT UserName,GroupID FROM UserInfo GROUP BY GroupID
您可能会说,这是语法报错.然而,严格的说在语法上这句是没有错误的. 原因是,当按GroupID分组时, GroupID与UserName成了一对多的关系,系统无法确定在GroupID=1的结果行中将UserName 取为Andy还是Merry. 实事上,系统不管您的数据表中的某个GroupID是否会对应多个UserName,均会一律对待,因为它不打算冒这个险.^^
(2)函数
您应该牢记一些函数 MIN,MAX,COUNT,RTRIM,GETDATE(),SUBSTRING, ISNULL,LEFT,CHARINDEX,REPLACE,ISNUMERIC,ISDATE等等.
您应该对一些函数至少有印象,知道他们是做什么用的,具体参数可以查手册,用的次数多后就会记下来. 当然有精力去记下它们更好.如
DATEDIFF,DATEADD,CONVERT,REPLICATE,STUFF,PATINDEX等等
(3)另外一些系统函数
虽然在本文中我们可能不会用到它,但是在您的实际工用中,它们并不可少如:@@rowcount,@@identity,object_id,NEWID,DATALENGTH等等
(4)一些语句或关健字或语句或系统存储过程用法
… WHERE UserName IS NULL
… WHERE UserName LIKE ‘Me%’
COLLATE ..
Sp_ExecuteSql
CHECKSUM
LEFT( OUTER) JOIN,INNER JOIN,CROSS JOIN等等,它们的区别是什么
等等. 这些语句的意思,或关健字或过程的用法在联机丛书上都有说明,如果您对某些的用法并不熟悉,请对照联机丛书的说明掌握它们的用法.
(5)一些需要注意的地方
SELECT TOP 2 ID FROM t1 ORDER BY ID
UNION ALL
SELECT TOP 2 ID FROM t2 ORDER BY ID
这句报错的原因在于 使用UNION运算符时, ORDER BY 对多个 SELECT Statement结果集的合集有效,因此 ORDER BY 不能出现在某个 SELECT Statement之后(最后的那个除外)
实现上述语句功能可以用下面的方法代替 1-5.1
SELECT * FROM (SELECT TOP 2 ID FROM t1 ORDER BY ID)
UNION ALL
SELECT * FROM (SELECT TOP 2 ID FROM t2 ORDER BY ID)
或者 1-5.2
SELECT * FROM
(SELECT ID,frm=’t1’ FROM t1
UNION ALL
SELECT ID,frm=’t2’ FROM t2) a
WHERE 2>(SELECT COUNT(1) FROM
(SELECT ID,frm=’t1’ FROM t1
UNION ALL
SELECT ID,frm=’t2’ FROM t2) b
WHERE a.frm=b.frm AND b.id<a.id
)
但从语句上来看,二者效率明显有差别. 但请注意,看起来效率低的实际运行中并不一定效率低,有些时候系统会进行语句优化成更有效的语句,您可以看执行计划来分析. 当然,这个例子确实效率有差别. 效率问题不再累赘.
(语句中的frm书面语称”别名”,我喜欢叫它虚字段,虚例,同样,子查询我喜欢称它为虚表.尽管叫法不够科学,但这样更有助于理解和描述.)
至于第二种写法思路缘何而来,在第二部分会有详细介绍
DECLARE @i INT,@s VARCHAR(100)
SET @s=’Me’
EXEC(‘SELECT @i=Count(*) FROM UserInfo WHERE UserName LIKE ’’’ + @s + ‘%’’’)
在EXEC外部定义的变量@i报错:变量未声明. EXEC内外部语句执行级别不同,编译不在同一空间,所以报错. 这里应该使用sp_executesql获取结果
还有一些其它要注意的问题,在些不一一列出.总之在您写查询语句被提示报错后,请冷静思考
a. 二维表的意义
b. 一些语句或操作内在需要注意的地方
c. 一些语句碰到意外值
d. 其它
2,解决个体(您可以使用的方法,您可以从这些基本的方法组合或扩展最终达到您的查询要求)
(1)一些简单问题中可以使用的技巧,您可以通过运行语句看看它们得到了什么
a.
------------------------------------------------------
DECLARE @str
SET @str=’aaa,bbbx,eee,vv’
SELECT LEN(REPLACE(@str,’,’,’,,’)-LEN(@str)
b.
------------------------------------------------------
DECLARE @b BIT
SET @b=0
SELECT SUBSTRING(‘否是’,@b+1,1)
c. (2-1.c)
------------------------------------------------------
DECLARE @ids
SET @ids=’1,3’
SELECT * FROM UserInfo WHERE CHARINDEX(‘,’+RTRIM(id) +’,’ , ‘,’+@ids+’,’)>0 --考滤一下,这里为什么要在字串及ID前后加上逗号
D
------------------------------------------------------
SELECT * INTO t1 FROM UserInfo WHERE 1=0
依然同上文,不一一列举
(2)常用解决问题的方法
a. 您需要准备一个Split函数
create function f_split(@str varchar(8000),@StrSeprate varchar(10))
returns @temp table(a varchar(100))
as
begin
declare @i int
set @str=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@str)
while @i>=1
begin
insert @temp values(left(@str,@i-1))
set @str=substring(@str,@i+1,len(@str)-@i) --当然,这里您也可以改写为STUFF,可以自己试着改写一下
set @i=charindex(@StrSeprate,@str)
end
if @SourceSql<>'\'
insert @temp values(@str)
return
end
--用法：select * from dbo.f_split('A:B:C:D:E',':')
请保留它,在很多场合,很多时候您都会用到它.因为它是个通用且有用的函数
b.利用创建临时表生成连续ID.
SELECT *,IDENTITY(INT) NID INTO #t FROM tb
利用临时表,当有了从 1--你的记录数连续的NID时,NID就是标识,用一个变量作指针 你就可以用循环来操作每一条记录了.
也可以跳至任一条记录.还可以利用NID来记录行之间的前后关系,这在一些查询中非常有效,比如先进先出的设计
c.利用sp_executesql得到某些查询结果再参与运算
d.使用INSERT SELECT 还是 SELECT INTO
e.CASE variable WHEN 或是 CASE WHE condition statement THEN
f. 利用COUNT生成序数
ID UserName ClassID Score
1 Aaa 9802 66
2 Bbb 9801 70
3 Ccc 9801 69
4 ddd 9801 88
设Score 不重复,得到每个 UserName 的 Score排名 2-2.f.1
------------------------------------------------------
SELECT UserName,
(SELECT COUNT(*) FROM Student WHERE Score>=a.Score)
FROM Student a
每查询一条记录时,从表中统计Score大于等于(因为设Score不重复,所以等于指它本身)当前记录的个数. 因此得到排名
当Score有重复值时, 名次也会有重复值,并且会出现名次断号,此时需要进一一步处理 2-2.f.2
SELECT DISTINCT Score INTO #t FROM Student
SELECT a.UserName,b.Index
FROM student a
INNER JOIN
(SELECT Score,
(SELECT COUNT(*) FROM #t WHERE Score>=a.Score) Index
FROM #t a
) b
ON a.Score=b.Score
DROP TABLE #t
这样得到了不间断的排名. 仔细看语句,这里临时表#t没有做任何处理,仅起到了暂存数据的作用, 以此来简化SQL查询语句. 您可以尝试将#t用生成它的那句SELECT查询代替,用一条SQL语句来实现这个功能(如果用了子查询代替#t, 参考我上面对虚表的说法,更容易理解, 临时表换成虚表).
(注.这里不是鼓励使用一条SQL查询完成,而是建议您在学习时这样做,提高自己的熟练程度. 而在实际应用中是否应该用一条语句要具体问题具体分析)
得到每个班级Score最高的前两名学生的记录 2-2.f.3
-----------------------------------------------------------
SELECT * FROM Student a
WHERE 2>(SELECT COUNT(1) FROM Student WHERE Score>a.Score AND ClassID=a.ClassID)
您还到想到其它简洁的写法吗?(当然有,提示 IN 关健字)
请对比2-2.f.1这条语句去理解2-2.f.3的语句思路. 思考,这语句有什么问题吗?
(提示.当同一ClassID的Score有重复值时,确定是每个班级2条记录吗? 如果不是,如何解决? 参见 2-2.f.2).
g.由 2-2.f.1您如何解决下面的问题:
Product 表中Num字段记录了每天的入库数,请更新 AllNum字段为截止当前的库存数
LogDate Num AllNum
2006-6-1 100
2006-6-3 20
2006-7-5 300
2-2-g.1
UPDATE a SET AllNUM=
(SELECT SUM(Num) FROM Product b WHERE b.LogDATE<=a.LogDate) FROM product a
它与2-2.f.1有区别吗? 理解之后,您应该会说”没有区别”
h.行转列.
网上有很多例子,随便都可以找一个单表的下来. 静态(要转的列固定)行转列比较容易理解也容易做到,动态行转列,只需您将构建的SQL字串PRINT仔细分析,相信聪明好学的您一定会发现原理. 理解了它的得来,再复杂的行转列也只是多连表,多统计,增加点语句长度而已.
i.递规查询变量 2-2.i.1
DECLARE @s VARCHAR(1000)
SET @s=’’
SELECT @s=@s + ‘,’ + RTRIM(id) FROM Student
SELECT @s=STUFF(@s,1,1,’’)
RPINT @s
/*得到结果
1,2,3,4
*/
为什么会得到这样的结果? 难道因为它叫递规查询变量? 那么它为什么要叫递规查询变量? 呵呵.
j.除上述方法外，您需要心中常记一些二维模型，遇到问题时看能否把问题转化.
2-2.j.1
二表
class1
------------------
id name total
1 aaa 90
2 bbb 98
3 ccc 89
------------------
class2
------------------
id name total
1 zzz 24
2 aaa 77
3 xxx 99
要这样结果:
id name total class
1 xxx 99 class2
2 bbb 98 class1
1 aaa 90 class1
3 ccc 89 class1
参照 1-5.2您可以写出查询来。
表名：table1
字段及内容：
id classtitle recorder
1 生活百科 科学
2 生活百科 饮食
3 体育在线 足球
4 体育在线 篮球
5 新闻周刊 国际
6 新闻周刊 国内
7 生活百科 穿衣
........
要求得到：
生活百科
--科学
--饮食
--穿衣
体育在线
--足球
--篮球
新闻周刊
--国际
--国内
心中想到模型(二级分类)
Id sid parentid
? 新闻周刊 新闻周刊
? 体育在线 体育在线
? 生活百科 生活百科
1 生活百科 科学
2 生活百科 饮食
3 体育在线 足球
4 体育在线 篮球
5 新闻周刊 国际
6 新闻周刊 国内
7 生活百科 穿衣
2-2.j.2
SELECT DISTINCT classtitle,ct=classtitle,dot='' FROM table1
UNION
SELECT classtitle,recorder,dot='--' FROM table1
ORDER BY classtitle
您需要保留记住一些常用二维模型，在解决问题时考滤问题是否能向相应的模型转换。
这一节也不再例举,more practise more progress.
(1)一些简单问题中可以使用的技巧,您可以通过运行语句看看它们得到了什么
a.
------------------------------------------------------
DECLARE @str
SET @str=’aaa,bbbx,eee,vv’
SELECT LEN(REPLACE(@str,’,’,’,,’)-LEN(@str)
b.
------------------------------------------------------
DECLARE @b BIT
SET @b=0
SELECT SUBSTRING(‘否是’,@b+1,1)
c. (2-1.c)
------------------------------------------------------
DECLARE @ids
SET @ids=’1,3’
SELECT * FROM UserInfo WHERE CHARINDEX(‘,’+RTRIM(id) +’,’ , ‘,’+@ids+’,’)>0 --考滤一下,这里为什么要在字串及ID前后加上逗号
D
------------------------------------------------------
SELECT * INTO t1 FROM UserInfo WHERE 1=0
依然同上文,不一一列举
(2)常用解决问题的方法
a. 您需要准备一个Split函数
create function f_split(@str varchar(8000),@StrSeprate varchar(10))
returns @temp table(a varchar(100))
as
begin
declare @i int
set @str=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@str)
while @i>=1
begin
insert @temp values(left(@str,@i-1))
set @str=substring(@str,@i+1,len(@str)-@i) --当然,这里您也可以改写为STUFF,可以自己试着改写一下
set @i=charindex(@StrSeprate,@str)
end
if @SourceSql<>'\'
insert @temp values(@str)
return
end
--用法：select * from dbo.f_split('A:B:C:D:E',':')
请保留它,在很多场合,很多时候您都会用到它.因为它是个通用且有用的函数
b.利用创建临时表生成连续ID.
SELECT *,IDENTITY(INT) NID INTO #t FROM tb
利用临时表,当有了从 1--你的记录数连续的NID时,NID就是标识,用一个变量作指针 你就可以用循环来操作每一条记录了.
也可以跳至任一条记录.还可以利用NID来记录行之间的前后关系,这在一些查询中非常有效,比如先进先出的设计
c.利用sp_executesql得到某些查询结果再参与运算
d.使用INSERT SELECT 还是 SELECT INTO
e.CASE variable WHEN 或是 CASE WHE condition statement THEN
f. 利用COUNT生成序数
ID UserName ClassID Score
1 Aaa 9802 66
2 Bbb 9801 70
3 Ccc 9801 69
4 ddd 9801 88
设Score 不重复,得到每个 UserName 的 Score排名 2-2.f.1
------------------------------------------------------
SELECT UserName,
(SELECT COUNT(*) FROM Student WHERE Score>=a.Score)
FROM Student a
每查询一条记录时,从表中统计Score大于等于(因为设Score不重复,所以等于指它本身)当前记录的个数. 因此得到排名
当Score有重复值时, 名次也会有重复值,并且会出现名次断号,此时需要进一一步处理 2-2.f.2
SELECT DISTINCT Score INTO #t FROM Student
SELECT a.UserName,b.Index
FROM student a
INNER JOIN
(SELECT Score,
(SELECT COUNT(*) FROM #t WHERE Score>=a.Score) Index
FROM #t a
) b
ON a.Score=b.Score
DROP TABLE #t
这样得到了不间断的排名. 仔细看语句,这里临时表#t没有做任何处理,仅起到了暂存数据的作用, 以此来简化SQL查询语句. 您可以尝试将#t用生成它的那句SELECT查询代替,用一条SQL语句来实现这个功能(如果用了子查询代替#t, 参考我上面对虚表的说法,更容易理解, 临时表换成虚表).
(注.这里不是鼓励使用一条SQL查询完成,而是建议您在学习时这样做,提高自己的熟练程度. 而在实际应用中是否应该用一条语句要具体问题具体分析)
得到每个班级Score最高的前两名学生的记录 2-2.f.3
-----------------------------------------------------------
SELECT * FROM Student a
WHERE 2>(SELECT COUNT(1) FROM Student WHERE Score>a.Score AND ClassID=a.ClassID)
您还到想到其它简洁的写法吗?(当然有,提示 IN 关健字)
请对比2-2.f.1这条语句去理解2-2.f.3的语句思路. 思考,这语句有什么问题吗?
(提示.当同一ClassID的Score有重复值时,确定是每个班级2条记录吗? 如果不是,如何解决? 参见 2-2.f.2).
g.由 2-2.f.1您如何解决下面的问题:
Product 表中Num字段记录了每天的入库数,请更新 AllNum字段为截止当前的库存数
LogDate Num AllNum
2006-6-1 100
2006-6-3 20
2006-7-5 300
2-2-g.1
UPDATE a SET AllNUM=
(SELECT SUM(Num) FROM Product b WHERE b.LogDATE<=a.LogDate) FROM product a
它与2-2.f.1有区别吗? 理解之后,您应该会说”没有区别”
h.行转列.
网上有很多例子,随便都可以找一个单表的下来. 静态(要转的列固定)行转列比较容易理解也容易做到,动态行转列,只需您将构建的SQL字串PRINT仔细分析,相信聪明好学的您一定会发现原理. 理解了它的得来,再复杂的行转列也只是多连表,多统计,增加点语句长度而已.
i.递规查询变量 2-2.i.1
DECLARE @s VARCHAR(1000)
SET @s=’’
SELECT @s=@s + ‘,’ + RTRIM(id) FROM Student
SELECT @s=STUFF(@s,1,1,’’)
RPINT @s
/*得到结果
1,2,3,4
*/
为什么会得到这样的结果? 难道因为它叫递规查询变量? 那么它为什么要叫递规查询变量? 呵呵.
j.除上述方法外，您需要心中常记一些二维模型，遇到问题时看能否把问题转化.
2-2.j.1
二表
class1
------------------
id name total
1 aaa 90
2 bbb 98
3 ccc 89
------------------
class2
------------------
id name total
1 zzz 24
2 aaa 77
3 xxx 99
要这样结果:
id name total class
1 xxx 99 class2
2 bbb 98 class1
1 aaa 90 class1
3 ccc 89 class1
参照 1-5.2您可以写出查询来。
表名：table1
字段及内容：
id classtitle recorder
1 生活百科 科学
2 生活百科 饮食
3 体育在线 足球
4 体育在线 篮球
5 新闻周刊 国际
6 新闻周刊 国内
7 生活百科 穿衣
........
要求得到：
生活百科
--科学
--饮食
--穿衣
体育在线
--足球
--篮球
新闻周刊
--国际
--国内
心中想到模型(二级分类)
Id sid parentid
? 新闻周刊 新闻周刊
? 体育在线 体育在线
? 生活百科 生活百科
1 生活百科 科学
2 生活百科 饮食
3 体育在线 足球
4 体育在线 篮球
5 新闻周刊 国际
6 新闻周刊 国内
7 生活百科 穿衣
2-2.j.2
SELECT DISTINCT classtitle,ct=classtitle,dot='' FROM table1
UNION
SELECT classtitle,recorder,dot='--' FROM table1
ORDER BY classtitle
您需要保留记住一些常用二维模型，在解决问题时考滤问题是否能向相应的模型转换。
这一节也不再例举,more practise more progress.
3,解题举例(常见问题.及引入思路)
用我们的知识开始我们的征途吧.
(1).常见问题. 下面我给出几个表,针对一些问题,看看如何解决.
CREATE TABLE Student(ID INT IDENTITY(1,1),Name VARCHAR(20),Sex BIT,ClassID VARCHAR(10))
INSERT Student(Name,Sex,ClassID) SELECT '张三',1,'9527'
UNION ALL SELECT '李四',1,'9527'
UNION ALL SELECT '王五',1,'2046'
UNION ALL SELECT 'MM',0,'2046'
UNION ALL SELECT '赵六',1,'2046'
UNION ALL SELECT 'GG',1,'2046'
CREATE TABLE Class(ClassID VARCHAR(10),ClassName VARCHAR(20))
INSERT class SELECT '2046','00级电器2班'
UNION ALL SELECT '9527','02级财快1班'
CREATE TABLE Score(ID INT IDENTITY(1,1),Score INT,SubjectID INT,StudentID INT)
INSERT Score (Score,SubjectID,StudentID)
SELECT 90,1,1
UNION ALL SELECT 80,1,2
UNION ALL SELECT 91,1,3
UNION ALL SELECT 59,1,4
UNION ALL SELECT 55,1,5
UNION ALL SELECT 90,1,6
UNION ALL SELECT 60,2,1
UNION ALL SELECT 53,2,2
UNION ALL SELECT 99,2,3
UNION ALL SELECT 90,2,4
UNION ALL SELECT 76,2,5
UNION ALL SELECT 87,2,6
CREATE TABLE Subject(ID INT IDENTITY(1,1),SubjectName VARCHAR(20))
INSERT Subject(SubjectName) SELECT '数学' UNION ALL SELECT '英语'
a.列出所有学生全信息:
学生编号,学生名,班级名，科目名,成绩,性别
3-1.a.1
SELECT a.Name,b.ClassName,c.Score,d.SubjectName,SUBSTRING('女男',a.Sex+1,1) Sex
FROM Student a
INNER JOIN Class b
ON a.ClassID=b.ClassID
INNER JOIN Score c
ON a.ID=c.StudentID
INNER JOIN Subject d
ON c.SubjectID=d.id
很简单吧,将几个表连接就可以了。但从上面的语句中您发现问题了吗? 考滤如果Score表随便少一条记录,是否就少了某个Student的成绩呢? 您会考滤：这里应该使用LEFT JOIN.没错，是应该使用LEFT JOIN,但您将 INNER 改为 LEFT 之后会发现问题仍没有改观，是何原因呢?仍是 11个结果行
因为没基表支持。我这里所谓的基于是指应该存在模型. 在这个问题中就指 所有的Student与所有的 Subject的组合.(不考滤实际应用时，也要考滤Class，但理论上实际应用中当某个Student存在时，那么它对应的Class就应该存在，所以这里不予考滤).
SELECT a.ID stdid,b.id subid
FROM student a
CROSS JOIN subject b
这样就生成了基表(虚表)—基准数据. 然后利用它与其它表左连即可.
3-1.a.2
SELECT a.Name,b.ClassName,c.Score,d.SubjectName,SUBSTRING('女男',a.Sex+1,1) Sex/*试着将Sex的得来用CASE WHEN语句或其它您知道的办法改写*/
FROM
(SELECT a.ID stdid,b.id subid
FROM student a
CROSS JOIN subject b) x
LEFT JOIN Student a
ON a.id=x.stdid
LEFT JOIN Class b
ON a.ClassID=b.ClassID
LEFT JOIN Score c
ON x.subid=c.SubjectID AND x.stdid=c.StudentID
LEFT JOIN Subject d
ON c.SubjectID=d.id
结果为12个行，显示了所有Student的所有Subject的Score. 当然，您可以用ISNULL处理一下没有成绩的学生的Score.
还有其它方法吗?请思考，用Student及Subject与Score表对比，补充上不存在的Subject或Student的记录,然后以 新生的 Score(虚表,子查询，临时)表 作为基表去左连其它表.
b.选出任一科目成绩不级格的学生的信息. 3-1.b.1
SELECT * FROM Sutdent a WHERE EXISTS(SELECT 1 FROM Score WHEE Score<60 AND StudentID=a.ID)
没错，作法是对了，但从2-1.a您考滤到了什么? 对，您很陪明，您想到了如果某个Student某科在Score表中没有记录，那么它成绩是0也属不及格，上面的语句无法选出他。但是您有什么好解决方法吗?
考滤，基表.可用基表与Score左连，得到所有学生所有学科成绩记录，然后应用3-1.b.1的语句. 也可以用双条件 一个就是3-1.b.1中的条件，另一个是用基表与Score表对比判断当前学生记录是否有成绩不在Score表中出现,当然聪明的您或许还有更高明的想法^^。 请自行写出语句.
c.选出任一科目不及格的人次超过2(>=2)的班级信息.
您写出下面的语句 3-1.c.1
SELECT b.ClassName,COUNT(1) CNT
FROM Score a
LEFT JOIN Student c
ON a.StudentID=c.ID
LEFT JOIN Class b
ON b.ClassID=c.ClassID
WHERE a.Score<60
GROUP BY b.ClassName
HAVING COUNT(1)>1
没错，连表后分组再利用 HAVING便可得到所需的结果. 同样，我们也面对着 a.1和b.1所具有的问题，请考滤某Student的Subject在Score表中无记录的情况.
本文旨在讨论思路，为突出主题，关于数据完整性的问题下文不再累赘，但在使际应用时您一定要考滤这些问题。
d.得到每个学生在各自班级的总分排名
3-1.d.1 看看下面的语句得到了什么
SELECT SUM(a.Score) SumScore,a.StudentID,c.ClassID
FROM student b
INNER JOIN Score a
ON a.Studentid=b.id
INNER JOIN Class c
ON b.ClassID=c.ClassID
GROUP BY a.StudentID,c.ClassID
是的，它得到了每个学生所在班级ID，每个学生的总分。
如何得到他的排名呢?
3-1.d.2 将上面的结果存入 #t临时表
SELECT SUM(a.Score) SumScore,a.StudentID,c.ClassID
INTO #t
FROM student b
INNER JOIN Score a
ON a.Studentid=b.id
INNER JOIN Class c
ON b.ClassID=c.ClassID
GROUP BY a.StudentID,c.ClassID
再回去看 2-2.f.1与2.2.f.2， 从#t中得到学生的排名，您能写出语句吗？ 加上一个条件，只在当前学生同班级的记录中统计总分大于他的，是否就得到了他在本班级的排名呢?
去掉临时表，用虚表(子查询)来代替它，您写出来了吗.
e. 对于这四个表的检索要求多种多样，您可以多想想有什么的要求,整理思路，得到解决办法.这里不再赘述。
(2)一些我们可以轻松解决的问题.(在解题之前多考滤，不要一看到题就想到游标或循环,有些时候用临时表比用游标好不到哪去，但很多时候可能会是三两条语句解决)
a. 有表 tb ,tel字段存放了多个电话号码以”,”分隔
ID tel
1 555,444
2 555,444,333
3 555
4 444,666
请编写存储过程，当传入一组电话号码字串时，找到相应的记录。
字串格式 ‘333,666,111,444’
可以查询到ID 为 1,2,4的记录
思考: 您在第二节时是否保留了那个Split函数呢? 这里它将发挥作用.
将传入串拆分，如果 tel中存在传入串中的某一项值，那么选出它. 3-2.d.1
DECLARE @mtel VARCHAR(100)
SET @mtel=’ 333,666,111,444’
SELECT a.tel FROM tb a
INNER JOIN dbo.Split(@mtel,',') b
ON CHARINDEX(',' + RTRIM(a) + ',' , ',' + tel + ',')>0
您可能会说，这不是存储过程，是的，它不是一个存储过程，但您将@mtel理解为存储过程的输入参数后，它就是一个存储过程，语句的改造就靠您了.
您考滤一下，有没有其它办法呢?不用这个Split函数。考滤，如果构造这样的查询语句呢? 3-2.d.2
SELECT tel FROM tb
WHERE ','+TEL+',' LIKE '%,333,%' /*这里为什么要前后加逗号呢? 2-1.c */
OR ','+Tel+',' LIKE '%,666,%'
OR ','+Tel+',' LIKE '%,111,%'
OR ‘,’+Tel+’,’ LIKE ‘%,666,%’
很好，您明白了这个语句，那么我们就来构造它.
DECLARE @mtel VARCHAR(100),@sql VARCHAR(2000)
SET @mtel=’ 333,666,111,444’
SET @sql='SELECT tel FROM tb WHERE '',''+TEL+'','' LIKE ''%,' + REPLACE(@mtel,',',',%'' OR '',''+Tel+'','' LIKE ''%,') + ',%'''
--PRINT @sql
EXEC(@sql)
b. 行号 表2取值或公式 项目
表1
h1 1 原料采购
h2 1 生产领用
h3 0 h1-h2
h4 1 国内销售
h5 1 国外销售
h6 0 h4+h5
表2:
项目 值
原料采购 7
生产领用 4
国内销售 11
国外销售 5
所得结果:
行号 值
h1 7
h2 4
h3 3
h4 11
h5 5
h6 16
先给出表及测试数据
CREATE TABLE t1 (a VARCHAR(10),b bit,c VARCHAR(20))
INSERT t1 SELECT 'h1' , 1, '原料采购'
UNION ALL SELECT 'h2' , 1, '生产领用'
UNION ALL SELECT 'h3' , 0 , 'h1-h2'
UNION ALL SELECT 'h4' , 1, '国内销售'
UNION ALL SELECT 'h5' , 1, '国外销售'
UNION ALL SELECT 'h6', 0, 'h4+h5'
CREATE TABLE t2 (c VARCHAR(20),num INT)
INSERT t2 SELECT '原料采购' , 7
UNION ALL SELECT '生产领用', 4
UNION ALL SELECT '国内销售' , 11
UNION ALL SELECT '国外销售' , 5
SELECT * FROM t1
SELECT * FROM t2
思考:
将表1中的公式用对应的项目替代
SELECT a.a a ,a.b b,REPLACE(REPLACE(a.c,b.a,b.c),c.a,c.c) c
FROM t1 a
INNER JOIN t1 b
ON CHARINDEX(' ' + b.a, ' ' + a.c)>0 /*公式左列对比*/
INNER JOIN t1 c
ON CHARINDEX('+' + c.a,a.c)>0 OR CHARINDEX('-' + c.a,a.c)>0 /*对左例作过替的公式进行右列对比*/
UNION ALL
SELECT a,b,c FROM t1 WHERE c NOT LIKE 'h[1-9]%' /*得到所有不含公式的记录*/
得到结果
h3 0 原料采购-生产领用
h6 0 国内销售+国外销售
h1 1 原料采购
h2 1 生产领用
h4 1 国内销售
h5 1 国外销售
很好，您能明白这个结果的得来，那么将操作再重复一次，用值去替代项目名称得到结果:
SELECT base.a,base.b,
CASE WHEN CHARINDEX('-',base.c)>0 THEN x.num-y.num
WHEN CHARINDEX('+',base.c)>0 THEN x.num+y.num
ELSE x.num END num
FROM
(SELECT a.a a ,a.b b,REPLACE(REPLACE(a.c,b.a,b.c),c.a,c.c) c
FROM t1 a
INNER JOIN t1 b
ON CHARINDEX(' ' + b.a, ' ' + a.c)>0
INNER JOIN t1 c
ON CHARINDEX('+' + c.a,a.c)>0 OR CHARINDEX('-' + c.a,a.c)>0
UNION ALL
SELECT a,b,c FROM t1 WHERE c NOT LIKE 'h[1-9]%'
) base
LEFT JOIN t2 x
ON CHARINDEX(' '+x.c,' '+base.c)>0
LEFT JOIN t2 y
ON CHARINDEX('+'+y.c,base.c)>0 OR CHARINDEX('-'+y.c,base.c)>0
ORDER BY base.a
c.递规查询变量解决的问题
1.有一个基表比如：
col_city col_code
HK 101
BJ 102
SZ 103
... ...
2.有一个字符串比如：
HK->BJ->SZ->HK->SZ
目的：用一个SQL语句去处理该字符串，用code替换掉描述。比如上面的替换完就成为：
101->102->103->101->103
DECLARE @s VARCHAR(100)
SELECT @s='->' + 'HK->BJ->SZ->HK->SZ' + '->'
SELECT @s=REPLACE(@s,'->'+col_city+'->','->'+col_code+'->') FROM 表
SELECT @s=SUBSTRING(@s,3,LEN(@s)-4)
您考滤过这个查询语句有什么问题吗?
4,解题举例
a. 数据是这样的
表1
发票号码 金额
A-1 1000.00
A-2 1500.00
A-3 1200.00
B-1 800.00
B-2 1000.00
B-3 900.00
B-4 1100.00
...
表2
发票号码 产品类型 金额
A P1 2000.00
A P2 1700.00
B P1 1500.00
B P2 1200.00
B P3 1100.00
...
希望产生下面的表:
发票号码 产品类型 金额
A-1 P1 1000.00
A-2 P1 1000.00
A-2 P2 500.00
A-3 P2 1200.00
B-1 P1 800.00
B-2 P1 700.00
B-2 P2 300.00
B-3 P2 900.00
B-4 P3 1100.00
DECLARE @t1 TABLE(ID1 INT IDENTITY(1,1),发票号码1 VARCHAR(10), 金额1 MONEY)
INSERT @t1(发票号码1,金额1) SELECT 'A-1' , 1000.00
UNION ALL SELECT 'A-2' , 1500.00
UNION ALL SELECT 'A-3' , 1200.00
UNION ALL SELECT 'B-1' , 800.00
UNION ALL SELECT 'B-2' , 1000.00
UNION ALL SELECT 'B-3' , 900.00
UNION ALL SELECT 'B-4', 1100.00
DECLARE @t2 TABLE(ID2 INT IDENTITY(1,1),发票号码2 VARCHAR(10),产品类型 VARCHAR(10),金额2 MONEY)
INSERT @t2(发票号码2,产品类型,金额2) SELECT 'A' , 'P1', 2000.00
UNION ALL SELECT 'A' , 'P2' , 1700.00
UNION ALL SELECT 'B' , 'P1' , 1500.00
UNION ALL SELECT 'B' , 'P2' , 1200.00
UNION ALL SELECT 'B' , 'P3' , 1100.00
SELECT *,AllC1=(SELECT SUM(金额1) FROM @t1 b WHERE b.ID1<=a.ID1) INTO #t1 FROM @t1 a
SELECT *,AllC2=(SELECT SUM(金额2) FROM @t2 b WHERE b.ID2<=a.ID2) INTO #t2 FROM @t2 a
/*因为这里的这句a.*,b.*,NID INTO #base 我懒的打字段名，所以在定义表变量时才给出了金额1,金额2这样的名字，
至于楼主自己，字段名不用变，只是下面这句语句需要写出字段列表
另外如果原表中如果没有ID1,ID2这样的标识列,那么可以先放入临时表生成标识列再进行同样的处理
*/
SELECT a.*,b.*,IDENTITY(INT) NID INTO #base
FROM #t1 a
LEFT JOIN #t2 b
ON (b.AllC2>=A.AllC1 AND 1>(SELECT COUNT(1) FROM #t2 c WHERE c.ID2<b.ID2 AND c.AllC2>=a.AllC1))
OR
(a.AllC1>=b.AllC2 AND (SELECT AllC1 FROM #t1 c WHERE c.ID1=a.ID1-1)<b.AllC2)
SELECT 发票号码1,发票号码2,产品类型,
CASE ABS(LC) WHEN 0 THEN 金额1 ELSE ABS(LC) END 金额
FROM
(SELECT *,LC=ISNULL((SELECT AllC2-AllC1 FROM #base b WHERE b.NID=a.NID-1),0) FROM #base a) base
/*结果
发票号码1 号码2 产品类型 金额
A-1AP11000.0000
A-2AP11000.0000
A-2AP2500.0000
A-3AP21200.0000
B-1BP1800.0000
B-2BP1700.0000
B-2BP2300.0000
B-3BP2900.0000
B-4BP31100.0000
今天写起来比昨晚在家轻松多了. 早上太忙,没顾得上.
*/
DROP TABLE #t1
DROP TABLE #t2
DROP TABLE #base
以上的语句，每产生一个临时表，请用SELECT * from 临时表名 看看结果是什么，您能找到我的思路
b. 合同表：contract
ID Amount
1 2000
2 1000
3 1200
4 1350
5 800
收款明细：collection
ID Amount OverDue(过期收款标志)
1 1000 Y
2 1000 N
3 500 Y
4 500 N
5 1000 Y
6 1550 N
7 800 Y
如何得到：contract_collection
ID OverDueAmount(过期收款) NaturalAmount(自然收款)
1 1000 1000
2 500 500
3 200 1000
4 1350 0
5 0 800
/* CREATE TABLE contract(ID INT IDENTITY(1,1),Amount INT)
INSERT contract(Amount)
SELECT 2000
UNION ALL SELECT 1000
UNION ALL SELECT 1200
UNION ALL SELECT 1350
UNION ALL SELECT 800
CREATE TABLE collection(ID INT IDENTITY(1,1),Amount INT,OverDue CHAR(1))
INSERT collection(Amount,OverDue)
SELECT 1000 ,'Y'
UNION ALL SELECT 1000, 'N'
UNION ALL SELECT 500 , 'Y'
UNION ALL SELECT 500 , 'N'
UNION ALL SELECT 1000 , 'Y'
UNION ALL SELECT 1550 , 'N'
UNION ALL SELECT 800 , 'Y'*/
SELECT ID aid,Amount cAmount,aAmount=(SELECT SUM(Amount) FROM contract b WHERE b.id<=a.id) INTO #ta FROM contract a
SELECT ID bid,Amount sglAmount,bAmount=(SELECT SUM(Amount) FROM collection b WHERE b.id<=a.id) INTO #tb FROM collection a
SELECT aid ,aAmount,bid,bAmount,cAmount,c.Amount sglAmount,id, Amount,OverDue,lAmount=bAmount-aAmount INTO #base
FROM #ta m
INNER JOIN #tb n
ON bAmount>=aAmount AND 1>(SELECT COUNT(1) FROM #tb a WHERE a.bAmount>=m.aAmount AND a.bid<n.bid)
INNER JOIN collection c
ON id<=bid
SELECT aid,aAmount,bAmount,cAmount,sglAmount,id,OverDue,lAmount INTO #s FROM #base a WHERE id>(SELECT ISNULL(MAX(bid),0) FROM #base b WHERE b.bid<a.bid)
SELECT aid,SUM(Overed),SUM(NoOver) FROM
(SELECT aid,
Overed=
CASE OverDue WHEN 'Y' THEN
CASE WHEN aAmount=bAmount THEN
CASE WHEN CurAmount>=sglAmount THEN
CASE WHEN CurAmount>=cAmount
THEN cAmount
ELSE sglAmount
END
ELSE CurAmount END
ELSE
CASE WHEN CurAmount>=cAmount THEN cAmount
ELSE
CASE WHEN CurAmount>=sglAmount THEN sglAmount
ELSE CurAmount END
END
END
ELSE
0
END
,
NoOver=
CASE OverDue WHEN 'N' THEN
CASE WHEN CurAmount>=sglAmount THEN
CASE WHEN 0<(SELECT COUNT(1) FROM
(SELECT * ,CurAmount=sglAmount + (CASE OverDue WHEN 'Y' THEN (SELECT ISNULL(MAX(b.lAmount),0) FROM #s b WHERE b.aid+1=a.aid) ELSE 0 END) FROM #s a)
a WHERE a.aid=base.aid AND a.CurAmount>=a.cAmount AND a.OverDue='Y')
THEN 0
ELSE
(SELECT ISNULL(MAX(a.cAmount)-SUM(a.sglAmount),0) FROM #s a WHERE a.aid=base.aid AND a.OverDue='Y')
END
ELSE CurAmount END
ELSE
0
END
FROM
(SELECT * ,CurAmount=sglAmount + (CASE OverDue WHEN 'Y' THEN (SELECT ISNULL(MAX(b.lAmount),0) FROM #s b WHERE b.aid+1=a.aid) ELSE 0 END) FROM #s a) base
) y
GROUP BY aid
--DROP TABLE contract
--DROP TABLE collection
DROP TABLE #s
DROP TABLE #base
DROP TABLE #ta
DROP TABLE #tb
二个例子有相似的地方
c. 怎么把这样的日期列表,转换成下面那样的啊?
日期 时间
2006-06-01 08:21:02
2006-06-01 12:03:36
2006-06-01 12:26:48
2006-06-01 17:41:36
2006-06-02 08:25:45
2006-06-02 12:03:22
2006-06-02 12:32:50
2006-06-02 17:35:03
2006-06-03 08:25:27
2006-06-03 12:05:17
2006-06-03 12:29:21
2006-06-03 17:37:04
2006-06-04 08:26:29
2006-06-04 12:10:41
2006-06-04 13:11:42
2006-06-04 17:34:05
2006-06-04 17:34:05
日期 时间1 时间2 时间3 时间4
2006-06-01 08:21:02 12:03:36 12:26:48 17:41:36
2006-06-01 ..........................................
.............................................
CREATE TABLE test(日期 DATETIME, 时间 VARCHAR(20))
INSERT test SELECT '2006-06-01','08:21:02'
UNION ALL SELECT '2006-06-01','12:03:36'
UNION ALL SELECT '2006-06-01','12:26:48'
UNION ALL SELECT '2006-06-01','17:41:36'
UNION ALL SELECT '2006-06-02','08:25:45'
UNION ALL SELECT '2006-06-02','12:03:22'
UNION ALL SELECT '2006-06-02','14:03:22'
UNION ALL SELECT '2006-06-02','19:03:22'
UNION ALL SELECT '2006-06-02','12:32:50'
UNION ALL SELECT '2006-06-02','17:35:03'
UNION ALL SELECT '2006-06-03','08:25:27'
UNION ALL SELECT '2006-06-03','12:05:17'
UNION ALL SELECT '2006-06-03','12:29:21'
UNION ALL SELECT '2006-06-03','17:37:04'
UNION ALL SELECT '2006-06-04','08:26:29'
UNION ALL SELECT '2006-06-04','12:10:41'
UNION ALL SELECT '2006-06-04','13:11:42'
UNION ALL SELECT '2006-06-04','17:34:05'
UNION ALL SELECT '2006-06-04','17:34:15'
SELECT a.日期,a.时间,b.cnt,IDENTITY(int) ID
INTO Test1
FROM Test a
INNER JOIN
(SELECT 日期,COUNT(*) cnt FROM test GROUP BY 日期) b
ON a.日期=b.日期
DECLARE @MaxFieldNum INT,@i INT
SELECT @i=0,@MaxFieldNum=MAX(cnt) FROM Test1
DECLARE @sql VARCHAR(8000)
SET @sql=''
WHILE @i<@MaxFieldNum
SELECT @sql=@sql + ',f' + RTRIM(@i) + '=' + '(SELECT 时间 FROM Test1 b WHERE b.日期=a.日期 AND ' + RTRIM(@i) + '=(SELECT COUNT(1) FROM Test1 c WHERE c.日期=b.日期 AND c.id<b.id))',@i=@i+1
SELECT @sql='SELECT CONVERT(VARCHAR(10),日期,120) 日期' + @sql + ' FROM Test1 a GROUP BY 日期'
EXEC(@sql)
DROP TABLE Test1
DROP TABLE test
/*------结果-----------------
日期f0f1f2f3f4f5f6
2006-06-0108:21:0212:03:3612:26:4817:41:36NULLNULL
2006-06-0208:25:4512:03:2214:03:2219:03:2212:32:5017:35:03
2006-06-0308:25:2712:05:1712:29:2117:37:04NULLNULL
2006-06-0408:26:2912:10:4113:11:4217:34:0517:34:15NULL
*/
用我们的知识开始我们的征途吧.
(1).常见问题. 下面我给出几个表,针对一些问题,看看如何解决.
CREATE TABLE Student(ID INT IDENTITY(1,1),Name VARCHAR(20),Sex BIT,ClassID VARCHAR(10))
INSERT Student(Name,Sex,ClassID) SELECT '张三',1,'9527'
UNION ALL SELECT '李四',1,'9527'
UNION ALL SELECT '王五',1,'2046'
UNION ALL SELECT 'MM',0,'2046'
UNION ALL SELECT '赵六',1,'2046'
UNION ALL SELECT 'GG',1,'2046'
CREATE TABLE Class(ClassID VARCHAR(10),ClassName VARCHAR(20))
INSERT class SELECT '2046','00级电器2班'
UNION ALL SELECT '9527','02级财快1班'
CREATE TABLE Score(ID INT IDENTITY(1,1),Score INT,SubjectID INT,StudentID INT)
INSERT Score (Score,SubjectID,StudentID)
SELECT 90,1,1
UNION ALL SELECT 80,1,2
UNION ALL SELECT 91,1,3
UNION ALL SELECT 59,1,4
UNION ALL SELECT 55,1,5
UNION ALL SELECT 90,1,6
UNION ALL SELECT 60,2,1
UNION ALL SELECT 53,2,2
UNION ALL SELECT 99,2,3
UNION ALL SELECT 90,2,4
UNION ALL SELECT 76,2,5
UNION ALL SELECT 87,2,6
CREATE TABLE Subject(ID INT IDENTITY(1,1),SubjectName VARCHAR(20))
INSERT Subject(SubjectName) SELECT '数学' UNION ALL SELECT '英语'
a.列出所有学生全信息:
学生编号,学生名,班级名，科目名,成绩,性别
3-1.a.1
SELECT a.Name,b.ClassName,c.Score,d.SubjectName,SUBSTRING('女男',a.Sex+1,1) Sex
FROM Student a
INNER JOIN Class b
ON a.ClassID=b.ClassID
INNER JOIN Score c
ON a.ID=c.StudentID
INNER JOIN Subject d
ON c.SubjectID=d.id
很简单吧,将几个表连接就可以了。但从上面的语句中您发现问题了吗? 考滤如果Score表随便少一条记录,是否就少了某个Student的成绩呢? 您会考滤：这里应该使用LEFT JOIN.没错，是应该使用LEFT JOIN,但您将 INNER 改为 LEFT 之后会发现问题仍没有改观，是何原因呢?仍是 11个结果行
因为没基表支持。我这里所谓的基于是指应该存在模型. 在这个问题中就指 所有的Student与所有的 Subject的组合.(不考滤实际应用时，也要考滤Class，但理论上实际应用中当某个Student存在时，那么它对应的Class就应该存在，所以这里不予考滤).
SELECT a.ID stdid,b.id subid
FROM student a
CROSS JOIN subject b
这样就生成了基表(虚表)—基准数据. 然后利用它与其它表左连即可.
3-1.a.2
SELECT a.Name,b.ClassName,c.Score,d.SubjectName,SUBSTRING('女男',a.Sex+1,1) Sex/*试着将Sex的得来用CASE WHEN语句或其它您知道的办法改写*/
FROM
(SELECT a.ID stdid,b.id subid
FROM student a
CROSS JOIN subject b) x
LEFT JOIN Student a
ON a.id=x.stdid
LEFT JOIN Class b
ON a.ClassID=b.ClassID
LEFT JOIN Score c
ON x.subid=c.SubjectID AND x.stdid=c.StudentID
LEFT JOIN Subject d
ON c.SubjectID=d.id
结果为12个行，显示了所有Student的所有Subject的Score. 当然，您可以用ISNULL处理一下没有成绩的学生的Score.
还有其它方法吗?请思考，用Student及Subject与Score表对比，补充上不存在的Subject或Student的记录,然后以 新生的 Score(虚表,子查询，临时)表 作为基表去左连其它表.
b.选出任一科目成绩不级格的学生的信息. 3-1.b.1
SELECT * FROM Sutdent a WHERE EXISTS(SELECT 1 FROM Score WHEE Score<60 AND StudentID=a.ID)
没错，作法是对了，但从2-1.a您考滤到了什么? 对，您很陪明，您想到了如果某个Student某科在Score表中没有记录，那么它成绩是0也属不及格，上面的语句无法选出他。但是您有什么好解决方法吗?
考滤，基表.可用基表与Score左连，得到所有学生所有学科成绩记录，然后应用3-1.b.1的语句. 也可以用双条件 一个就是3-1.b.1中的条件，另一个是用基表与Score表对比判断当前学生记录是否有成绩不在Score表中出现,当然聪明的您或许还有更高明的想法^^。 请自行写出语句.
c.选出任一科目不及格的人次超过2(>=2)的班级信息.
您写出下面的语句 3-1.c.1
SELECT b.ClassName,COUNT(1) CNT
FROM Score a
LEFT JOIN Student c
ON a.StudentID=c.ID
LEFT JOIN Class b
ON b.ClassID=c.ClassID
WHERE a.Score<60
GROUP BY b.ClassName
HAVING COUNT(1)>1
没错，连表后分组再利用 HAVING便可得到所需的结果. 同样，我们也面对着 a.1和b.1所具有的问题，请考滤某Student的Subject在Score表中无记录的情况.
本文旨在讨论思路，为突出主题，关于数据完整性的问题下文不再累赘，但在使际应用时您一定要考滤这些问题。
d.得到每个学生在各自班级的总分排名
3-1.d.1 看看下面的语句得到了什么
SELECT SUM(a.Score) SumScore,a.StudentID,c.ClassID
FROM student b
INNER JOIN Score a
ON a.Studentid=b.id
INNER JOIN Class c
ON b.ClassID=c.ClassID
GROUP BY a.StudentID,c.ClassID
是的，它得到了每个学生所在班级ID，每个学生的总分。
如何得到他的排名呢?
3-1.d.2 将上面的结果存入 #t临时表
SELECT SUM(a.Score) SumScore,a.StudentID,c.ClassID
INTO #t
FROM student b
INNER JOIN Score a
ON a.Studentid=b.id
INNER JOIN Class c
ON b.ClassID=c.ClassID
GROUP BY a.StudentID,c.ClassID
再回去看 2-2.f.1与2.2.f.2， 从#t中得到学生的排名，您能写出语句吗？ 加上一个条件，只在当前学生同班级的记录中统计总分大于他的，是否就得到了他在本班级的排名呢?
去掉临时表，用虚表(子查询)来代替它，您写出来了吗.
e. 对于这四个表的检索要求多种多样，您可以多想想有什么的要求,整理思路，得到解决办法.这里不再赘述。
(2)一些我们可以轻松解决的问题.(在解题之前多考滤，不要一看到题就想到游标或循环,有些时候用临时表比用游标好不到哪去，但很多时候可能会是三两条语句解决)
a. 有表 tb ,tel字段存放了多个电话号码以”,”分隔
ID tel
1 555,444
2 555,444,333
3 555
4 444,666
请编写存储过程，当传入一组电话号码字串时，找到相应的记录。
字串格式 ‘333,666,111,444’
可以查询到ID 为 1,2,4的记录
思考: 您在第二节时是否保留了那个Split函数呢? 这里它将发挥作用.
将传入串拆分，如果 tel中存在传入串中的某一项值，那么选出它. 3-2.d.1
DECLARE @mtel VARCHAR(100)
SET @mtel=’ 333,666,111,444’
SELECT a.tel FROM tb a
INNER JOIN dbo.Split(@mtel,',') b
ON CHARINDEX(',' + RTRIM(a) + ',' , ',' + tel + ',')>0
您可能会说，这不是存储过程，是的，它不是一个存储过程，但您将@mtel理解为存储过程的输入参数后，它就是一个存储过程，语句的改造就靠您了.
您考滤一下，有没有其它办法呢?不用这个Split函数。考滤，如果构造这样的查询语句呢? 3-2.d.2
SELECT tel FROM tb
WHERE ','+TEL+',' LIKE '%,333,%' /*这里为什么要前后加逗号呢? 2-1.c */
OR ','+Tel+',' LIKE '%,666,%'
OR ','+Tel+',' LIKE '%,111,%'
OR ‘,’+Tel+’,’ LIKE ‘%,666,%’
很好，您明白了这个语句，那么我们就来构造它.
DECLARE @mtel VARCHAR(100),@sql VARCHAR(2000)
SET @mtel=’ 333,666,111,444’
SET @sql='SELECT tel FROM tb WHERE '',''+TEL+'','' LIKE ''%,' + REPLACE(@mtel,',',',%'' OR '',''+Tel+'','' LIKE ''%,') + ',%'''
--PRINT @sql
EXEC(@sql)
b. 行号 表2取值或公式 项目
表1
h1 1 原料采购
h2 1 生产领用
h3 0 h1-h2
h4 1 国内销售
h5 1 国外销售
h6 0 h4+h5
表2:
项目 值
原料采购 7
生产领用 4
国内销售 11
国外销售 5
所得结果:
行号 值
h1 7
h2 4
h3 3
h4 11
h5 5
h6 16
先给出表及测试数据
CREATE TABLE t1 (a VARCHAR(10),b bit,c VARCHAR(20))
INSERT t1 SELECT 'h1' , 1, '原料采购'
UNION ALL SELECT 'h2' , 1, '生产领用'
UNION ALL SELECT 'h3' , 0 , 'h1-h2'
UNION ALL SELECT 'h4' , 1, '国内销售'
UNION ALL SELECT 'h5' , 1, '国外销售'
UNION ALL SELECT 'h6', 0, 'h4+h5'
CREATE TABLE t2 (c VARCHAR(20),num INT)
INSERT t2 SELECT '原料采购' , 7
UNION ALL SELECT '生产领用', 4
UNION ALL SELECT '国内销售' , 11
UNION ALL SELECT '国外销售' , 5
SELECT * FROM t1
SELECT * FROM t2
思考:
将表1中的公式用对应的项目替代
SELECT a.a a ,a.b b,REPLACE(REPLACE(a.c,b.a,b.c),c.a,c.c) c
FROM t1 a
INNER JOIN t1 b
ON CHARINDEX(' ' + b.a, ' ' + a.c)>0 /*公式左列对比*/
INNER JOIN t1 c
ON CHARINDEX('+' + c.a,a.c)>0 OR CHARINDEX('-' + c.a,a.c)>0 /*对左例作过替的公式进行右列对比*/
UNION ALL
SELECT a,b,c FROM t1 WHERE c NOT LIKE 'h[1-9]%' /*得到所有不含公式的记录*/
得到结果
h3 0 原料采购-生产领用
h6 0 国内销售+国外销售
h1 1 原料采购
h2 1 生产领用
h4 1 国内销售
h5 1 国外销售
很好，您能明白这个结果的得来，那么将操作再重复一次，用值去替代项目名称得到结果:
SELECT base.a,base.b,
CASE WHEN CHARINDEX('-',base.c)>0 THEN x.num-y.num
WHEN CHARINDEX('+',base.c)>0 THEN x.num+y.num
ELSE x.num END num
FROM
(SELECT a.a a ,a.b b,REPLACE(REPLACE(a.c,b.a,b.c),c.a,c.c) c
FROM t1 a
INNER JOIN t1 b
ON CHARINDEX(' ' + b.a, ' ' + a.c)>0
INNER JOIN t1 c
ON CHARINDEX('+' + c.a,a.c)>0 OR CHARINDEX('-' + c.a,a.c)>0
UNION ALL
SELECT a,b,c FROM t1 WHERE c NOT LIKE 'h[1-9]%'
) base
LEFT JOIN t2 x
ON CHARINDEX(' '+x.c,' '+base.c)>0
LEFT JOIN t2 y
ON CHARINDEX('+'+y.c,base.c)>0 OR CHARINDEX('-'+y.c,base.c)>0
ORDER BY base.a
c.递规查询变量解决的问题
1.有一个基表比如：
col_city col_code
HK 101
BJ 102
SZ 103
... ...
2.有一个字符串比如：
HK->BJ->SZ->HK->SZ
目的：用一个SQL语句去处理该字符串，用code替换掉描述。比如上面的替换完就成为：
101->102->103->101->103
DECLARE @s VARCHAR(100)
SELECT @s='->' + 'HK->BJ->SZ->HK->SZ' + '->'
SELECT @s=REPLACE(@s,'->'+col_city+'->','->'+col_code+'->') FROM 表
SELECT @s=SUBSTRING(@s,3,LEN(@s)-4)
您考滤过这个查询语句有什么问题吗?
4,解题举例
a. 数据是这样的
表1
发票号码 金额
A-1 1000.00
A-2 1500.00
A-3 1200.00
B-1 800.00
B-2 1000.00
B-3 900.00
B-4 1100.00
...
表2
发票号码 产品类型 金额
A P1 2000.00
A P2 1700.00
B P1 1500.00
B P2 1200.00
B P3 1100.00
...
希望产生下面的表:
发票号码 产品类型 金额
A-1 P1 1000.00
A-2 P1 1000.00
A-2 P2 500.00
A-3 P2 1200.00
B-1 P1 800.00
B-2 P1 700.00
B-2 P2 300.00
B-3 P2 900.00
B-4 P3 1100.00
DECLARE @t1 TABLE(ID1 INT IDENTITY(1,1),发票号码1 VARCHAR(10), 金额1 MONEY)
INSERT @t1(发票号码1,金额1) SELECT 'A-1' , 1000.00
UNION ALL SELECT 'A-2' , 1500.00
UNION ALL SELECT 'A-3' , 1200.00
UNION ALL SELECT 'B-1' , 800.00
UNION ALL SELECT 'B-2' , 1000.00
UNION ALL SELECT 'B-3' , 900.00
UNION ALL SELECT 'B-4', 1100.00
DECLARE @t2 TABLE(ID2 INT IDENTITY(1,1),发票号码2 VARCHAR(10),产品类型 VARCHAR(10),金额2 MONEY)
INSERT @t2(发票号码2,产品类型,金额2) SELECT 'A' , 'P1', 2000.00
UNION ALL SELECT 'A' , 'P2' , 1700.00
UNION ALL SELECT 'B' , 'P1' , 1500.00
UNION ALL SELECT 'B' , 'P2' , 1200.00
UNION ALL SELECT 'B' , 'P3' , 1100.00
SELECT *,AllC1=(SELECT SUM(金额1) FROM @t1 b WHERE b.ID1<=a.ID1) INTO #t1 FROM @t1 a
SELECT *,AllC2=(SELECT SUM(金额2) FROM @t2 b WHERE b.ID2<=a.ID2) INTO #t2 FROM @t2 a
/*因为这里的这句a.*,b.*,NID INTO #base 我懒的打字段名，所以在定义表变量时才给出了金额1,金额2这样的名字，
至于楼主自己，字段名不用变，只是下面这句语句需要写出字段列表
另外如果原表中如果没有ID1,ID2这样的标识列,那么可以先放入临时表生成标识列再进行同样的处理
*/
SELECT a.*,b.*,IDENTITY(INT) NID INTO #base
FROM #t1 a
LEFT JOIN #t2 b
ON (b.AllC2>=A.AllC1 AND 1>(SELECT COUNT(1) FROM #t2 c WHERE c.ID2<b.ID2 AND c.AllC2>=a.AllC1))
OR
(a.AllC1>=b.AllC2 AND (SELECT AllC1 FROM #t1 c WHERE c.ID1=a.ID1-1)<b.AllC2)
SELECT 发票号码1,发票号码2,产品类型,
CASE ABS(LC) WHEN 0 THEN 金额1 ELSE ABS(LC) END 金额
FROM
(SELECT *,LC=ISNULL((SELECT AllC2-AllC1 FROM #base b WHERE b.NID=a.NID-1),0) FROM #base a) base
/*结果
发票号码1 号码2 产品类型 金额
A-1AP11000.0000
A-2AP11000.0000
A-2AP2500.0000
A-3AP21200.0000
B-1BP1800.0000
B-2BP1700.0000
B-2BP2300.0000
B-3BP2900.0000
B-4BP31100.0000
今天写起来比昨晚在家轻松多了. 早上太忙,没顾得上.
*/
DROP TABLE #t1
DROP TABLE #t2
DROP TABLE #base
以上的语句，每产生一个临时表，请用SELECT * from 临时表名 看看结果是什么，您能找到我的思路
b. 合同表：contract
ID Amount
1 2000
2 1000
3 1200
4 1350
5 800
收款明细：collection
ID Amount OverDue(过期收款标志)
1 1000 Y
2 1000 N
3 500 Y
4 500 N
5 1000 Y
6 1550 N
7 800 Y
如何得到：contract_collection
ID OverDueAmount(过期收款) NaturalAmount(自然收款)
1 1000 1000
2 500 500
3 200 1000
4 1350 0
5 0 800
/* CREATE TABLE contract(ID INT IDENTITY(1,1),Amount INT)
INSERT contract(Amount)
SELECT 2000
UNION ALL SELECT 1000
UNION ALL SELECT 1200
UNION ALL SELECT 1350
UNION ALL SELECT 800
CREATE TABLE collection(ID INT IDENTITY(1,1),Amount INT,OverDue CHAR(1))
INSERT collection(Amount,OverDue)
SELECT 1000 ,'Y'
UNION ALL SELECT 1000, 'N'
UNION ALL SELECT 500 , 'Y'
UNION ALL SELECT 500 , 'N'
UNION ALL SELECT 1000 , 'Y'
UNION ALL SELECT 1550 , 'N'
UNION ALL SELECT 800 , 'Y'*/
SELECT ID aid,Amount cAmount,aAmount=(SELECT SUM(Amount) FROM contract b WHERE b.id<=a.id) INTO #ta FROM contract a
SELECT ID bid,Amount sglAmount,bAmount=(SELECT SUM(Amount) FROM collection b WHERE b.id<=a.id) INTO #tb FROM collection a
SELECT aid ,aAmount,bid,bAmount,cAmount,c.Amount sglAmount,id, Amount,OverDue,lAmount=bAmount-aAmount INTO #base
FROM #ta m
INNER JOIN #tb n
ON bAmount>=aAmount AND 1>(SELECT COUNT(1) FROM #tb a WHERE a.bAmount>=m.aAmount AND a.bid<n.bid)
INNER JOIN collection c
ON id<=bid
SELECT aid,aAmount,bAmount,cAmount,sglAmount,id,OverDue,lAmount INTO #s FROM #base a WHERE id>(SELECT ISNULL(MAX(bid),0) FROM #base b WHERE b.bid<a.bid)
SELECT aid,SUM(Overed),SUM(NoOver) FROM
(SELECT aid,
Overed=
CASE OverDue WHEN 'Y' THEN
CASE WHEN aAmount=bAmount THEN
CASE WHEN CurAmount>=sglAmount THEN
CASE WHEN CurAmount>=cAmount
THEN cAmount
ELSE sglAmount
END
ELSE CurAmount END
ELSE
CASE WHEN CurAmount>=cAmount THEN cAmount
ELSE
CASE WHEN CurAmount>=sglAmount THEN sglAmount
ELSE CurAmount END
END
END
ELSE
0
END
,
NoOver=
CASE OverDue WHEN 'N' THEN
CASE WHEN CurAmount>=sglAmount THEN
CASE WHEN 0<(SELECT COUNT(1) FROM
(SELECT * ,CurAmount=sglAmount + (CASE OverDue WHEN 'Y' THEN (SELECT ISNULL(MAX(b.lAmount),0) FROM #s b WHERE b.aid+1=a.aid) ELSE 0 END) FROM #s a)
a WHERE a.aid=base.aid AND a.CurAmount>=a.cAmount AND a.OverDue='Y')
THEN 0
ELSE
(SELECT ISNULL(MAX(a.cAmount)-SUM(a.sglAmount),0) FROM #s a WHERE a.aid=base.aid AND a.OverDue='Y')
END
ELSE CurAmount END
ELSE
0
END
FROM
(SELECT * ,CurAmount=sglAmount + (CASE OverDue WHEN 'Y' THEN (SELECT ISNULL(MAX(b.lAmount),0) FROM #s b WHERE b.aid+1=a.aid) ELSE 0 END) FROM #s a) base
) y
GROUP BY aid
--DROP TABLE contract
--DROP TABLE collection
DROP TABLE #s
DROP TABLE #base
DROP TABLE #ta
DROP TABLE #tb
二个例子有相似的地方
c. 怎么把这样的日期列表,转换成下面那样的啊?
日期 时间
2006-06-01 08:21:02
2006-06-01 12:03:36
2006-06-01 12:26:48
2006-06-01 17:41:36
2006-06-02 08:25:45
2006-06-02 12:03:22
2006-06-02 12:32:50
2006-06-02 17:35:03
2006-06-03 08:25:27
2006-06-03 12:05:17
2006-06-03 12:29:21
2006-06-03 17:37:04
2006-06-04 08:26:29
2006-06-04 12:10:41
2006-06-04 13:11:42
2006-06-04 17:34:05
2006-06-04 17:34:05
日期 时间1 时间2 时间3 时间4
2006-06-01 08:21:02 12:03:36 12:26:48 17:41:36
2006-06-01 ..........................................
.............................................
CREATE TABLE test(日期 DATETIME, 时间 VARCHAR(20))
INSERT test SELECT '2006-06-01','08:21:02'
UNION ALL SELECT '2006-06-01','12:03:36'
UNION ALL SELECT '2006-06-01','12:26:48'
UNION ALL SELECT '2006-06-01','17:41:36'
UNION ALL SELECT '2006-06-02','08:25:45'
UNION ALL SELECT '2006-06-02','12:03:22'
UNION ALL SELECT '2006-06-02','14:03:22'
UNION ALL SELECT '2006-06-02','19:03:22'
UNION ALL SELECT '2006-06-02','12:32:50'
UNION ALL SELECT '2006-06-02','17:35:03'
UNION ALL SELECT '2006-06-03','08:25:27'
UNION ALL SELECT '2006-06-03','12:05:17'
UNION ALL SELECT '2006-06-03','12:29:21'
UNION ALL SELECT '2006-06-03','17:37:04'
UNION ALL SELECT '2006-06-04','08:26:29'
UNION ALL SELECT '2006-06-04','12:10:41'
UNION ALL SELECT '2006-06-04','13:11:42'
UNION ALL SELECT '2006-06-04','17:34:05'
UNION ALL SELECT '2006-06-04','17:34:15'
SELECT a.日期,a.时间,b.cnt,IDENTITY(int) ID
INTO Test1
FROM Test a
INNER JOIN
(SELECT 日期,COUNT(*) cnt FROM test GROUP BY 日期) b
ON a.日期=b.日期
DECLARE @MaxFieldNum INT,@i INT
SELECT @i=0,@MaxFieldNum=MAX(cnt) FROM Test1
DECLARE @sql VARCHAR(8000)
SET @sql=''
WHILE @i<@MaxFieldNum
SELECT @sql=@sql + ',f' + RTRIM(@i) + '=' + '(SELECT 时间 FROM Test1 b WHERE b.日期=a.日期 AND ' + RTRIM(@i) + '=(SELECT COUNT(1) FROM Test1 c WHERE c.日期=b.日期 AND c.id<b.id))',@i=@i+1
SELECT @sql='SELECT CONVERT(VARCHAR(10),日期,120) 日期' + @sql + ' FROM Test1 a GROUP BY 日期'
EXEC(@sql)
DROP TABLE Test1
DROP TABLE test
/*------结果-----------------
日期f0f1f2f3f4f5f6
2006-06-0108:21:0212:03:3612:26:4817:41:36NULLNULL
2006-06-0208:25:4512:03:2214:03:2219:03:2212:32:5017:35:03
2006-06-0308:25:2712:05:1712:29:2117:37:04NULLNULL
2006-06-0408:26:2912:10:4113:11:4217:34:0517:34:15NULL
*/
说明：复制表(只复制结构,源表名：a 新表名：b)
select * into b from a where 1<>1
说明：拷贝表(拷贝数据,源表名：a 目标表名：b)
insert into b(a, b, c) select d,e,f from b;
说明：显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
说明：外连接查询(表名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
说明：日程安排提前五分钟提醒
select * from 日程安排 where datediff('minute',f开始时间,getdate())>5
说明：两张关联表，删除主表中已经在副表中没有的信息
delete from info where not exists ( select * from infobz where info.infid=infobz.infid )
说明：--
SQL:
SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE
FROM TABLE1,
(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE
FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND
FROM TABLE2
WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')) X,
(SELECT NUM, UPD_DATE, STOCK_ONHAND
FROM TABLE2
WHERE TO_CHAR(UPD_DATE,'YYYY/MM') =
TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM') || '/01','YYYY/MM/DD') - 1, 'YYYY/MM') ) Y,
WHERE X.NUM = Y.NUM （+）
AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B
WHERE A.NUM = B.NUM
说明：--
select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名称='"&strdepartmentname&"' and 专业名称='"&strprofessionname&"' order by 性别,生源地,高考总成绩
从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源）
SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') AS telyear,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '01', a.factration)) AS JAN,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '02', a.factration)) AS FRI,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '03', a.factration)) AS MAR,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '04', a.factration)) AS APR,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '05', a.factration)) AS MAY,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '06', a.factration)) AS JUE,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '07', a.factration)) AS JUL,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '08', a.factration)) AS AGU,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '09', a.factration)) AS SEP,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '10', a.factration)) AS OCT,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '11', a.factration)) AS NOV,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '12', a.factration)) AS DEC
FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration
FROM TELFEESTAND a, TELFEE b
WHERE a.tel = b.telfax) a
GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy')
说明：四表联查问题
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 .....
说明：得到表中最小的未使用的ID号
SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID FROM Handle WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)
一个SQL语句的问题:行列转换
select * from v_temp
上面的视图结果如下:
user_name role_name
-------------------------
系统管理员 管理员
feng 管理员
feng 一般用户
test 一般用户
想把结果变成这样:
user_name role_name
---------------------------
系统管理员 管理员
feng 管理员,一般用户
test 一般用户
===================
create table a_test(name varchar(20),role2 varchar(20))
insert into a_test values('李','管理员')
insert into a_test values('张','管理员')
insert into a_test values('张','一般用户')
insert into a_test values('常','一般用户')
create function join_str(@content varchar(100))
returns varchar(2000)
as
begin
declare @str varchar(2000)
set @str=''
select @str=@str+','+rtrim(role2) from a_test where [name]=@content
select @str=right(@str,len(@str)-1)
return @str
end
go
--调用：
select [name],dbo.join_str([name]) role2 from a_test group by [name]
--select distinct name,dbo.uf_test(name) from a_test
快速比较结构相同的两表
结构相同的两表，一表有记录3万条左右，一表有记录2万条左右，我怎样快速查找两表的不同记录？
============================
给你一个测试方法，从northwind中的orders表取数据。
select * into n1 from orders
select * into n2 from orders
select * from n1
select * from n2
--添加主键，然后修改n1中若干字段的若干条
alter table n1 add constraint pk_n1_id primary key (OrderID)
alter table n2 add constraint pk_n2_id primary key (OrderID)
select OrderID from (select * from n1 union select * from n2) a group by OrderID having count(*) > 1
应该可以，而且将不同的记录的ID显示出来。
下面的适用于双方记录一样的情况，
select * from n1 where orderid in (select OrderID from (select * from n1 union select * from n2) a group by OrderID having count(*) > 1)
至于双方互不存在的记录是比较好处理的
--删除n1,n2中若干条记录
delete from n1 where orderID in ('10728','10730')
delete from n2 where orderID in ('11000','11001')
--*************************************************************
-- 双方都有该记录却不完全相同
select * from n1 where orderid in(select OrderID from (select * from n1 union select * from n2) a group by OrderID having count(*) > 1)
union
--n2中存在但在n1中不存的在10728,10730
select * from n1 where OrderID not in (select OrderID from n2)
union
--n1中存在但在n2中不存的在11000,11001
select * from n2 where OrderID not in (select OrderID from n1)
四种方法取表里n到m条纪录：
1.
select top m * into 临时表(或表变量) from tablename order by columnname -- 将top m笔插入
set rowcount n
select * from 表变量 order by columnname desc
2.
select top n * from (select top m * from tablename order by columnname) a order by columnname desc
3.如果tablename里没有其他identity列，那么：
select identity(int) id0,* into #temp from tablename
取n到m条的语句为：
select * from #temp where id0 >=n and id0 <= m
如果你在执行select identity(int) id0,* into #temp from tablename这条语句的时候报错,那是因为你的DB中间的select into/bulkcopy属性没有打开要先执行：
exec sp_dboption 你的DB名字,'select into/bulkcopy',true
4.如果表里有identity属性，那么简单：
select * from tablename where identitycol between n and m
如何删除一个表中重复的记录？
create table a_dist(id int,name varchar(20))
insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')
exec up_distinct 'a_dist','id'
select * from a_dist
create procedure up_distinct(@t_name varchar(30),@f_key varchar(30))
--f_key表示是分组字段﹐即主键字段
as
begin
declare @max integer,@id varchar(30) ,@sql varchar(7999) ,@type integer
select @sql = 'declare cur_rows cursor for select '+@f_key+' ,count(*) from ' +@t_name +' group by ' +@f_key +' having count(*) > 1'
exec(@sql)
open cur_rows
fetch cur_rows into @id,@max
while @@fetch_status=0
begin
select @max = @max -1
set rowcount @max
select @type = xtype from syscolumns where id=object_id(@t_name) and name=@f_key
if @type=56
select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+ @id
if @type=167
select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+''''+ @id +''''
exec(@sql)
fetch cur_rows into @id,@max
end
close cur_rows
deallocate cur_rows
set rowcount 0
end
select * from systypes
select * from syscolumns where id = object_id('a_dist')
查询数据的最大排序问题（只能用一条语句写）
CREATE TABLE hard (qu char (11) ,co char (11) ,je numeric(3, 0))
insert into hard values ('A','1',3)
insert into hard values ('A','2',4)
insert into hard values ('A','4',2)
insert into hard values ('A','6',9)
insert into hard values ('B','1',4)
insert into hard values ('B','2',5)
insert into hard values ('B','3',6)
insert into hard values ('C','3',4)
insert into hard values ('C','6',7)
insert into hard values ('C','2',3)
要求查询出来的结果如下：
qu co je
----------- ----------- -----
A 6 9
A 2 4
B 3 6
B 2 5
C 6 7
C 3 4
就是要按qu分组，每组中取je最大的前2位！！
而且只能用一句sql语句！！！
select * from hard a where je in (select top 2 je from hard b where a.qu=b.qu order by je)
求删除重复记录的sql语句？
怎样把具有相同字段的纪录删除，只留下一条。
例如，表test里有id,name字段
如果有name相同的记录 只留下一条，其余的删除。
name的内容不定，相同的记录数不定。
有没有这样的sql语句？
==============================
A:一个完整的解决方案：
将重复的记录记入temp1表:
select [标志字段id],count(*) into temp1 from [表名]
group by [标志字段id]
having count(*)>1
2、将不重复的记录记入temp1表:
insert temp1 select [标志字段id],count(*) from [表名] group by [标志字段id] having count(*)=1
3、作一个包含所有不重复记录的表：
select * into temp2 from [表名] where 标志字段id in(select 标志字段id from temp1)
4、删除重复表:
delete [表名]
5、恢复表：
insert [表名] select * from temp2
6、删除临时表:
drop table temp1
drop table temp2
================================
B:
create table a_dist(id int,name varchar(20))
insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')
exec up_distinct 'a_dist','id'
select * from a_dist
create procedure up_distinct(@t_name varchar(30),@f_key varchar(30))
--f_key表示是分组字段﹐即主键字段
as
begin
declare @max integer,@id varchar(30) ,@sql varchar(7999) ,@type integer
select @sql = 'declare cur_rows cursor for select '+@f_key+' ,count(*) from ' +@t_name +' group by ' +@f_key +' having count(*) > 1'
exec(@sql)
open cur_rows
fetch cur_rows into @id,@max
while @@fetch_status=0
begin
select @max = @max -1
set rowcount @max
select @type = xtype from syscolumns where id=object_id(@t_name) and name=@f_key
if @type=56
select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+ @id
if @type=167
select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+''''+ @id +''''
exec(@sql)
fetch cur_rows into @id,@max
end
close cur_rows
deallocate cur_rows
set rowcount 0
end
select * from systypes
select * from syscolumns where id = object_id('a_dist')
行列转换--普通
假设有张学生成绩表(CJ)如下
Name Subject Result
张三 语文 80
张三 数学 90
张三 物理 85
李四 语文 85
李四 数学 92
李四 物理 82
想变成
姓名 语文 数学 物理
张三 80 90 85
李四 85 92 82
declare @sql varchar(4000)
set @sql = 'select Name'
select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']'
from (select distinct Subject from CJ) as a
select @sql = @sql+' from test group by name'
exec(@sql)
行列转换--合并
有表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
创建一个合并的函数
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
如何取得一个数据表的所有列名
方法如下：先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOLUMN表中取得该数据表的所有列名。
SQL语句如下：
declare @objid int,@objname char(40)
set @objname = 'tablename'
select @objid = id from sysobjects where id = object_id(@objname)
select 'Column_name' = name from syscolumns where id = @objid order by colid
或
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='users'
通过SQL语句来更改用户的密码
修改别人的,需要sysadmin role
EXEC sp_password NULL, 'newpassword', 'User'
如果帐号为SA执行EXEC sp_password NULL, 'newpassword', sa
怎么判断出一个表的哪些字段不允许为空？
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE='NO' and TABLE_NAME=tablename
如何在数据库里找到含有相同字段的表？
a. 查已知列名的情况
SELECT b.name as TableName,a.name as columnname
From syscolumns a INNER JOIN sysobjects b
ON a.id=b.id
AND b.type='U'
AND a.name='你的字段名字'
未知列名查所有在不同表出现过的列名
Select o.name As tablename,s1.name As columnname
From syscolumns s1, sysobjects o
Where s1.id = o.id
And o.type = 'U'
And Exists (
Select 1 From syscolumns s2
Where s1.name = s2.name
And s1.id <> s2.id
)
查询第xxx行数据
假设id是主键：
select * from (select top xxx * from yourtable) aa where not exists(select 1 from (select top xxx-1 * from yourtable) bb where aa.id=bb.id)
如果使用游标也是可以的
fetch absolute [number] from [cursor_name]
行数为绝对行数
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))。
获取表结构[把 'sysobjects' 替换 成 'tablename' 即可]
SELECT CASE IsNull(I.name, '')
When '' Then ''
Else '*'
End as IsPK,
Object_Name(A.id) as t_name,
A.name as c_name,
IsNull(SubString(M.text, 1, 254), '') as pbc_init,
T.name as F_DataType,
CASE IsNull(TYPEPROPERTY(T.name, 'Scale'), '')
WHEN '' Then Cast(A.prec as varchar)
ELSE Cast(A.prec as varchar) + ',' + Cast(A.scale as varchar)
END as F_Scale,
A.isnullable as F_isNullAble
FROM Syscolumns as A
JOIN Systypes as T
ON (A.xType = T.xUserType AND A.Id = Object_id('sysobjects') )
LEFT JOIN ( SysIndexes as I
JOIN Syscolumns as A1
ON ( I.id = A1.id and A1.id = object_id('sysobjects') and (I.status & 0x800) = 0x800 AND A1.colid <= I.keycnt) )
ON ( A.id = I.id AND A.name = index_col('sysobjects', I.indid, A1.colid) )
LEFT JOIN SysComments as M
ON ( M.id = A.cdefault and ObjectProperty(A.cdefault, 'IsConstraint') = 1 )
ORDER BY A.Colid ASC
提取数据库内所有表的字段详细说明的SQL语句
SELECT
(case when a.colorder=1 then d.name else '' end) N'表名',
a.colorder N'字段序号',
a.name N'字段名',
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else ''
end) N'标识',
(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) N'主键',
b.name N'类型',
a.length N'占用字节数',
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',
(case when a.isnullable=1 then '√'else '' end) N'允许空',
isnull(e.text,'') N'默认值',
isnull(g.[value],'') AS N'字段说明'
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 object_name(a.id),a.colorder
快速获取表test的记录总数[对大容量表非常有效]
快速获取表test的记录总数:
select rows from sysindexes where id = object_id('test') and indid in (0,1)
update 2 set KHXH=(ID+1)\2 2行递增编号
update [23] set id1 = 'No.'+right('00000000'+id,6) where id not like 'No%' //递增
update [23] set id1= 'No.'+right('00000000'+replace(id1,'No.',''),6) //补位递增
delete from [1] where (id%2)=1
奇数
替换表名字段
update [1] set domurl = replace(domurl,'Upload/Imgswf/','Upload/Photo/') where domurl like '%Upload/Imgswf/%'
截位
SELECT LEFT(表名, 5)
select * into b from a where 1<>1
说明：拷贝表(拷贝数据,源表名：a 目标表名：b)
insert into b(a, b, c) select d,e,f from b;
说明：显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
说明：外连接查询(表名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
说明：日程安排提前五分钟提醒
select * from 日程安排 where datediff('minute',f开始时间,getdate())>5
说明：两张关联表，删除主表中已经在副表中没有的信息
delete from info where not exists ( select * from infobz where info.infid=infobz.infid )
说明：--
SQL:
SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE
FROM TABLE1,
(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE
FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND
FROM TABLE2
WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')) X,
(SELECT NUM, UPD_DATE, STOCK_ONHAND
FROM TABLE2
WHERE TO_CHAR(UPD_DATE,'YYYY/MM') =
TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM') || '/01','YYYY/MM/DD') - 1, 'YYYY/MM') ) Y,
WHERE X.NUM = Y.NUM （+）
AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B
WHERE A.NUM = B.NUM
说明：--
select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名称='"&strdepartmentname&"' and 专业名称='"&strprofessionname&"' order by 性别,生源地,高考总成绩
从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源）
SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') AS telyear,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '01', a.factration)) AS JAN,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '02', a.factration)) AS FRI,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '03', a.factration)) AS MAR,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '04', a.factration)) AS APR,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '05', a.factration)) AS MAY,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '06', a.factration)) AS JUE,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '07', a.factration)) AS JUL,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '08', a.factration)) AS AGU,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '09', a.factration)) AS SEP,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '10', a.factration)) AS OCT,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '11', a.factration)) AS NOV,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '12', a.factration)) AS DEC
FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration
FROM TELFEESTAND a, TELFEE b
WHERE a.tel = b.telfax) a
GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy')
说明：四表联查问题
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 .....
说明：得到表中最小的未使用的ID号
SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID FROM Handle WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)
一个SQL语句的问题:行列转换
select * from v_temp
上面的视图结果如下:
user_name role_name
-------------------------
系统管理员 管理员
feng 管理员
feng 一般用户
test 一般用户
想把结果变成这样:
user_name role_name
---------------------------
系统管理员 管理员
feng 管理员,一般用户
test 一般用户
===================
create table a_test(name varchar(20),role2 varchar(20))
insert into a_test values('李','管理员')
insert into a_test values('张','管理员')
insert into a_test values('张','一般用户')
insert into a_test values('常','一般用户')
create function join_str(@content varchar(100))
returns varchar(2000)
as
begin
declare @str varchar(2000)
set @str=''
select @str=@str+','+rtrim(role2) from a_test where [name]=@content
select @str=right(@str,len(@str)-1)
return @str
end
go
--调用：
select [name],dbo.join_str([name]) role2 from a_test group by [name]
--select distinct name,dbo.uf_test(name) from a_test
快速比较结构相同的两表
结构相同的两表，一表有记录3万条左右，一表有记录2万条左右，我怎样快速查找两表的不同记录？
============================
给你一个测试方法，从northwind中的orders表取数据。
select * into n1 from orders
select * into n2 from orders
select * from n1
select * from n2
--添加主键，然后修改n1中若干字段的若干条
alter table n1 add constraint pk_n1_id primary key (OrderID)
alter table n2 add constraint pk_n2_id primary key (OrderID)
select OrderID from (select * from n1 union select * from n2) a group by OrderID having count(*) > 1
应该可以，而且将不同的记录的ID显示出来。
下面的适用于双方记录一样的情况，
select * from n1 where orderid in (select OrderID from (select * from n1 union select * from n2) a group by OrderID having count(*) > 1)
至于双方互不存在的记录是比较好处理的
--删除n1,n2中若干条记录
delete from n1 where orderID in ('10728','10730')
delete from n2 where orderID in ('11000','11001')
--*************************************************************
-- 双方都有该记录却不完全相同
select * from n1 where orderid in(select OrderID from (select * from n1 union select * from n2) a group by OrderID having count(*) > 1)
union
--n2中存在但在n1中不存的在10728,10730
select * from n1 where OrderID not in (select OrderID from n2)
union
--n1中存在但在n2中不存的在11000,11001
select * from n2 where OrderID not in (select OrderID from n1)
四种方法取表里n到m条纪录：
1.
select top m * into 临时表(或表变量) from tablename order by columnname -- 将top m笔插入
set rowcount n
select * from 表变量 order by columnname desc
2.
select top n * from (select top m * from tablename order by columnname) a order by columnname desc
3.如果tablename里没有其他identity列，那么：
select identity(int) id0,* into #temp from tablename
取n到m条的语句为：
select * from #temp where id0 >=n and id0 <= m
如果你在执行select identity(int) id0,* into #temp from tablename这条语句的时候报错,那是因为你的DB中间的select into/bulkcopy属性没有打开要先执行：
exec sp_dboption 你的DB名字,'select into/bulkcopy',true
4.如果表里有identity属性，那么简单：
select * from tablename where identitycol between n and m
如何删除一个表中重复的记录？
create table a_dist(id int,name varchar(20))
insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')
exec up_distinct 'a_dist','id'
select * from a_dist
create procedure up_distinct(@t_name varchar(30),@f_key varchar(30))
--f_key表示是分组字段﹐即主键字段
as
begin
declare @max integer,@id varchar(30) ,@sql varchar(7999) ,@type integer
select @sql = 'declare cur_rows cursor for select '+@f_key+' ,count(*) from ' +@t_name +' group by ' +@f_key +' having count(*) > 1'
exec(@sql)
open cur_rows
fetch cur_rows into @id,@max
while @@fetch_status=0
begin
select @max = @max -1
set rowcount @max
select @type = xtype from syscolumns where id=object_id(@t_name) and name=@f_key
if @type=56
select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+ @id
if @type=167
select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+''''+ @id +''''
exec(@sql)
fetch cur_rows into @id,@max
end
close cur_rows
deallocate cur_rows
set rowcount 0
end
select * from systypes
select * from syscolumns where id = object_id('a_dist')
查询数据的最大排序问题（只能用一条语句写）
CREATE TABLE hard (qu char (11) ,co char (11) ,je numeric(3, 0))
insert into hard values ('A','1',3)
insert into hard values ('A','2',4)
insert into hard values ('A','4',2)
insert into hard values ('A','6',9)
insert into hard values ('B','1',4)
insert into hard values ('B','2',5)
insert into hard values ('B','3',6)
insert into hard values ('C','3',4)
insert into hard values ('C','6',7)
insert into hard values ('C','2',3)
要求查询出来的结果如下：
qu co je
----------- ----------- -----
A 6 9
A 2 4
B 3 6
B 2 5
C 6 7
C 3 4
就是要按qu分组，每组中取je最大的前2位！！
而且只能用一句sql语句！！！
select * from hard a where je in (select top 2 je from hard b where a.qu=b.qu order by je)
求删除重复记录的sql语句？
怎样把具有相同字段的纪录删除，只留下一条。
例如，表test里有id,name字段
如果有name相同的记录 只留下一条，其余的删除。
name的内容不定，相同的记录数不定。
有没有这样的sql语句？
==============================
A:一个完整的解决方案：
将重复的记录记入temp1表:
select [标志字段id],count(*) into temp1 from [表名]
group by [标志字段id]
having count(*)>1
2、将不重复的记录记入temp1表:
insert temp1 select [标志字段id],count(*) from [表名] group by [标志字段id] having count(*)=1
3、作一个包含所有不重复记录的表：
select * into temp2 from [表名] where 标志字段id in(select 标志字段id from temp1)
4、删除重复表:
delete [表名]
5、恢复表：
insert [表名] select * from temp2
6、删除临时表:
drop table temp1
drop table temp2
================================
B:
create table a_dist(id int,name varchar(20))
insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')
exec up_distinct 'a_dist','id'
select * from a_dist
create procedure up_distinct(@t_name varchar(30),@f_key varchar(30))
--f_key表示是分组字段﹐即主键字段
as
begin
declare @max integer,@id varchar(30) ,@sql varchar(7999) ,@type integer
select @sql = 'declare cur_rows cursor for select '+@f_key+' ,count(*) from ' +@t_name +' group by ' +@f_key +' having count(*) > 1'
exec(@sql)
open cur_rows
fetch cur_rows into @id,@max
while @@fetch_status=0
begin
select @max = @max -1
set rowcount @max
select @type = xtype from syscolumns where id=object_id(@t_name) and name=@f_key
if @type=56
select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+ @id
if @type=167
select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+''''+ @id +''''
exec(@sql)
fetch cur_rows into @id,@max
end
close cur_rows
deallocate cur_rows
set rowcount 0
end
select * from systypes
select * from syscolumns where id = object_id('a_dist')
行列转换--普通
假设有张学生成绩表(CJ)如下
Name Subject Result
张三 语文 80
张三 数学 90
张三 物理 85
李四 语文 85
李四 数学 92
李四 物理 82
想变成
姓名 语文 数学 物理
张三 80 90 85
李四 85 92 82
declare @sql varchar(4000)
set @sql = 'select Name'
select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']'
from (select distinct Subject from CJ) as a
select @sql = @sql+' from test group by name'
exec(@sql)
行列转换--合并
有表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
创建一个合并的函数
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
如何取得一个数据表的所有列名
方法如下：先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOLUMN表中取得该数据表的所有列名。
SQL语句如下：
declare @objid int,@objname char(40)
set @objname = 'tablename'
select @objid = id from sysobjects where id = object_id(@objname)
select 'Column_name' = name from syscolumns where id = @objid order by colid
或
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='users'
通过SQL语句来更改用户的密码
修改别人的,需要sysadmin role
EXEC sp_password NULL, 'newpassword', 'User'
如果帐号为SA执行EXEC sp_password NULL, 'newpassword', sa
怎么判断出一个表的哪些字段不允许为空？
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE='NO' and TABLE_NAME=tablename
如何在数据库里找到含有相同字段的表？
a. 查已知列名的情况
SELECT b.name as TableName,a.name as columnname
From syscolumns a INNER JOIN sysobjects b
ON a.id=b.id
AND b.type='U'
AND a.name='你的字段名字'
未知列名查所有在不同表出现过的列名
Select o.name As tablename,s1.name As columnname
From syscolumns s1, sysobjects o
Where s1.id = o.id
And o.type = 'U'
And Exists (
Select 1 From syscolumns s2
Where s1.name = s2.name
And s1.id <> s2.id
)
查询第xxx行数据
假设id是主键：
select * from (select top xxx * from yourtable) aa where not exists(select 1 from (select top xxx-1 * from yourtable) bb where aa.id=bb.id)
如果使用游标也是可以的
fetch absolute [number] from [cursor_name]
行数为绝对行数
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))。
获取表结构[把 'sysobjects' 替换 成 'tablename' 即可]
SELECT CASE IsNull(I.name, '')
When '' Then ''
Else '*'
End as IsPK,
Object_Name(A.id) as t_name,
A.name as c_name,
IsNull(SubString(M.text, 1, 254), '') as pbc_init,
T.name as F_DataType,
CASE IsNull(TYPEPROPERTY(T.name, 'Scale'), '')
WHEN '' Then Cast(A.prec as varchar)
ELSE Cast(A.prec as varchar) + ',' + Cast(A.scale as varchar)
END as F_Scale,
A.isnullable as F_isNullAble
FROM Syscolumns as A
JOIN Systypes as T
ON (A.xType = T.xUserType AND A.Id = Object_id('sysobjects') )
LEFT JOIN ( SysIndexes as I
JOIN Syscolumns as A1
ON ( I.id = A1.id and A1.id = object_id('sysobjects') and (I.status & 0x800) = 0x800 AND A1.colid <= I.keycnt) )
ON ( A.id = I.id AND A.name = index_col('sysobjects', I.indid, A1.colid) )
LEFT JOIN SysComments as M
ON ( M.id = A.cdefault and ObjectProperty(A.cdefault, 'IsConstraint') = 1 )
ORDER BY A.Colid ASC
提取数据库内所有表的字段详细说明的SQL语句
SELECT
(case when a.colorder=1 then d.name else '' end) N'表名',
a.colorder N'字段序号',
a.name N'字段名',
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else ''
end) N'标识',
(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) N'主键',
b.name N'类型',
a.length N'占用字节数',
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',
(case when a.isnullable=1 then '√'else '' end) N'允许空',
isnull(e.text,'') N'默认值',
isnull(g.[value],'') AS N'字段说明'
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 object_name(a.id),a.colorder
快速获取表test的记录总数[对大容量表非常有效]
快速获取表test的记录总数:
select rows from sysindexes where id = object_id('test') and indid in (0,1)
update 2 set KHXH=(ID+1)\2 2行递增编号
update [23] set id1 = 'No.'+right('00000000'+id,6) where id not like 'No%' //递增
update [23] set id1= 'No.'+right('00000000'+replace(id1,'No.',''),6) //补位递增
delete from [1] where (id%2)=1
奇数
替换表名字段
update [1] set domurl = replace(domurl,'Upload/Imgswf/','Upload/Photo/') where domurl like '%Upload/Imgswf/%'
截位
SELECT LEFT(表名, 5)
3333
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
