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.
浙公网安备 33010602011771号