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