查看:427|回复：2
select&&t2.FNAME as 客户名,T3.FCITY as 城市,SUM(t4.FDISCOUNT) as 出厂销售额,SUM(t5.FBILLALLAMOUNT) as 店铺销售额,sum(t5.FBILLALLAMOUNT)-sum(t4.FDISCOUNT) as 店铺利润 from
T_SAL_ORDER T1
inner join T_BD_CUSTOMER_L T2 ON T1.FCUSTID=T2.FCUSTID
inner join T_BD_CUSTOMEREXT T3 on T1.FCUSTID=T3.FCUSTID
inner join T_SAL_ORDERENTRY_F T4 on T1.FID=T4.FID
inner join T_SAL_ORDERFIN T5 on T1.FID=t5.FID
WHERE FDATE&='' AND FDATE&=''&&
group by t2.FNAME,t3.FCITY,t4.FDISCOUNT,t5.FBILLALLAMOUNT
我把客户名和城市分组&&查询结果却不对& &我哪里写的不对？
我都求和了，出厂销售额和店铺销售额 怎么还是分了这么多行呢？
还有我想在查询前面增加一个序号列&&应该怎么写呢？
下面是查询结果
出厂销售额
店铺销售额
沈阳工业大学北门店
沈阳工业大学北门店
沈阳工业大学北门店
沈阳工业大学北门店
沈阳工业大学北门店
沈阳工业大学北门店
沈阳工业大学北门店
沈阳广宜店
沈阳广宜店
沈阳广宜店
沈阳广宜店
沈阳广宜店
沈阳广宜店
沈阳广宜店
沈阳广宜店
本帖最后由 zwx860617 于
10:05 编辑
论坛首席搬砖工程师
你的图片挂了，重新编辑一下
51CTO信息安全交流群: 入群请说明论坛ID
是语句报错，还是结果不对？
SQL Server MCITP, MVP, MCT, DBA2016年11月 VB大版内专家分月排行榜第二2016年1月 MS-SQL Server大版内专家分月排行榜第二2015年12月 MS-SQL Server大版内专家分月排行榜第二2015年11月 MS-SQL Server大版内专家分月排行榜第二2015年5月 MS-SQL Server大版内专家分月排行榜第二2015年4月 MS-SQL Server大版内专家分月排行榜第二2015年1月 VB大版内专家分月排行榜第二2015年1月 MS-SQL Server大版内专家分月排行榜第二2014年12月 VB大版内专家分月排行榜第二2014年11月 MS-SQL Server大版内专家分月排行榜第二2014年9月 MS-SQL Server大版内专家分月排行榜第二2013年8月 VB大版内专家分月排行榜第二2013年6月 VB大版内专家分月排行榜第二2013年5月 VB大版内专家分月排行榜第二2010年4月 VB大版内专家分月排行榜第二2010年3月 VB大版内专家分月排行榜第二2008年12月 VB大版内专家分月排行榜第二2008年8月 VB大版内专家分月排行榜第二2008年7月 VB大版内专家分月排行榜第二2007年11月 VB大版内专家分月排行榜第二
2017年1月 MS-SQL Server大版内专家分月排行榜第三2015年6月 MS-SQL Server大版内专家分月排行榜第三2015年3月 MS-SQL Server大版内专家分月排行榜第三2015年2月 MS-SQL Server大版内专家分月排行榜第三2014年12月 MS-SQL Server大版内专家分月排行榜第三2014年10月 VB大版内专家分月排行榜第三2014年10月 MS-SQL Server大版内专家分月排行榜第三2014年8月 MS-SQL Server大版内专家分月排行榜第三2013年9月 VB大版内专家分月排行榜第三2013年3月 VB大版内专家分月排行榜第三2012年5月 VB大版内专家分月排行榜第三2012年4月 VB大版内专家分月排行榜第三2010年10月 VB大版内专家分月排行榜第三2010年8月 VB大版内专家分月排行榜第三2010年7月 VB大版内专家分月排行榜第三2009年5月 VB大版内专家分月排行榜第三2008年9月 VB大版内专家分月排行榜第三2008年4月 VB大版内专家分月排行榜第三2008年2月 VB大版内专家分月排行榜第三
2016年11月 VB大版内专家分月排行榜第二2016年1月 MS-SQL Server大版内专家分月排行榜第二2015年12月 MS-SQL Server大版内专家分月排行榜第二2015年11月 MS-SQL Server大版内专家分月排行榜第二2015年5月 MS-SQL Server大版内专家分月排行榜第二2015年4月 MS-SQL Server大版内专家分月排行榜第二2015年1月 VB大版内专家分月排行榜第二2015年1月 MS-SQL Server大版内专家分月排行榜第二2014年12月 VB大版内专家分月排行榜第二2014年11月 MS-SQL Server大版内专家分月排行榜第二2014年9月 MS-SQL Server大版内专家分月排行榜第二2013年8月 VB大版内专家分月排行榜第二2013年6月 VB大版内专家分月排行榜第二2013年5月 VB大版内专家分月排行榜第二2010年4月 VB大版内专家分月排行榜第二2010年3月 VB大版内专家分月排行榜第二2008年12月 VB大版内专家分月排行榜第二2008年8月 VB大版内专家分月排行榜第二2008年7月 VB大版内专家分月排行榜第二2007年11月 VB大版内专家分月排行榜第二
2017年1月 MS-SQL Server大版内专家分月排行榜第三2015年6月 MS-SQL Server大版内专家分月排行榜第三2015年3月 MS-SQL Server大版内专家分月排行榜第三2015年2月 MS-SQL Server大版内专家分月排行榜第三2014年12月 MS-SQL Server大版内专家分月排行榜第三2014年10月 VB大版内专家分月排行榜第三2014年10月 MS-SQL Server大版内专家分月排行榜第三2014年8月 MS-SQL Server大版内专家分月排行榜第三2013年9月 VB大版内专家分月排行榜第三2013年3月 VB大版内专家分月排行榜第三2012年5月 VB大版内专家分月排行榜第三2012年4月 VB大版内专家分月排行榜第三2010年10月 VB大版内专家分月排行榜第三2010年8月 VB大版内专家分月排行榜第三2010年7月 VB大版内专家分月排行榜第三2009年5月 VB大版内专家分月排行榜第三2008年9月 VB大版内专家分月排行榜第三2008年4月 VB大版内专家分月排行榜第三2008年2月 VB大版内专家分月排行榜第三
2016年11月 VB大版内专家分月排行榜第二2016年1月 MS-SQL Server大版内专家分月排行榜第二2015年12月 MS-SQL Server大版内专家分月排行榜第二2015年11月 MS-SQL Server大版内专家分月排行榜第二2015年5月 MS-SQL Server大版内专家分月排行榜第二2015年4月 MS-SQL Server大版内专家分月排行榜第二2015年1月 VB大版内专家分月排行榜第二2015年1月 MS-SQL Server大版内专家分月排行榜第二2014年12月 VB大版内专家分月排行榜第二2014年11月 MS-SQL Server大版内专家分月排行榜第二2014年9月 MS-SQL Server大版内专家分月排行榜第二2013年8月 VB大版内专家分月排行榜第二2013年6月 VB大版内专家分月排行榜第二2013年5月 VB大版内专家分月排行榜第二2010年4月 VB大版内专家分月排行榜第二2010年3月 VB大版内专家分月排行榜第二2008年12月 VB大版内专家分月排行榜第二2008年8月 VB大版内专家分月排行榜第二2008年7月 VB大版内专家分月排行榜第二2007年11月 VB大版内专家分月排行榜第二
2017年1月 MS-SQL Server大版内专家分月排行榜第三2015年6月 MS-SQL Server大版内专家分月排行榜第三2015年3月 MS-SQL Server大版内专家分月排行榜第三2015年2月 MS-SQL Server大版内专家分月排行榜第三2014年12月 MS-SQL Server大版内专家分月排行榜第三2014年10月 VB大版内专家分月排行榜第三2014年10月 MS-SQL Server大版内专家分月排行榜第三2014年8月 MS-SQL Server大版内专家分月排行榜第三2013年9月 VB大版内专家分月排行榜第三2013年3月 VB大版内专家分月排行榜第三2012年5月 VB大版内专家分月排行榜第三2012年4月 VB大版内专家分月排行榜第三2010年10月 VB大版内专家分月排行榜第三2010年8月 VB大版内专家分月排行榜第三2010年7月 VB大版内专家分月排行榜第三2009年5月 VB大版内专家分月排行榜第三2008年9月 VB大版内专家分月排行榜第三2008年4月 VB大版内专家分月排行榜第三2008年2月 VB大版内专家分月排行榜第三
2016年11月 VB大版内专家分月排行榜第二2016年1月 MS-SQL Server大版内专家分月排行榜第二2015年12月 MS-SQL Server大版内专家分月排行榜第二2015年11月 MS-SQL Server大版内专家分月排行榜第二2015年5月 MS-SQL Server大版内专家分月排行榜第二2015年4月 MS-SQL Server大版内专家分月排行榜第二2015年1月 VB大版内专家分月排行榜第二2015年1月 MS-SQL Server大版内专家分月排行榜第二2014年12月 VB大版内专家分月排行榜第二2014年11月 MS-SQL Server大版内专家分月排行榜第二2014年9月 MS-SQL Server大版内专家分月排行榜第二2013年8月 VB大版内专家分月排行榜第二2013年6月 VB大版内专家分月排行榜第二2013年5月 VB大版内专家分月排行榜第二2010年4月 VB大版内专家分月排行榜第二2010年3月 VB大版内专家分月排行榜第二2008年12月 VB大版内专家分月排行榜第二2008年8月 VB大版内专家分月排行榜第二2008年7月 VB大版内专家分月排行榜第二2007年11月 VB大版内专家分月排行榜第二
2017年1月 MS-SQL Server大版内专家分月排行榜第三2015年6月 MS-SQL Server大版内专家分月排行榜第三2015年3月 MS-SQL Server大版内专家分月排行榜第三2015年2月 MS-SQL Server大版内专家分月排行榜第三2014年12月 MS-SQL Server大版内专家分月排行榜第三2014年10月 VB大版内专家分月排行榜第三2014年10月 MS-SQL Server大版内专家分月排行榜第三2014年8月 MS-SQL Server大版内专家分月排行榜第三2013年9月 VB大版内专家分月排行榜第三2013年3月 VB大版内专家分月排行榜第三2012年5月 VB大版内专家分月排行榜第三2012年4月 VB大版内专家分月排行榜第三2010年10月 VB大版内专家分月排行榜第三2010年8月 VB大版内专家分月排行榜第三2010年7月 VB大版内专家分月排行榜第三2009年5月 VB大版内专家分月排行榜第三2008年9月 VB大版内专家分月排行榜第三2008年4月 VB大版内专家分月排行榜第三2008年2月 VB大版内专家分月排行榜第三
匿名用户不能发表回复！|
每天回帖即可获得10分可用分！小技巧：
你还可以输入10000个字符
(Ctrl+Enter)
请遵守CSDN，不得违反国家法律法规。
转载文章请注明出自“CSDN（www.csdn.net）”。如是商业用途请联系原作者。2010年5月 Java大版内专家分月排行榜第一2010年2月 Java大版内专家分月排行榜第一2010年1月 Java大版内专家分月排行榜第一2010年1月 Oracle大版内专家分月排行榜第一2009年12月 Java大版内专家分月排行榜第一2009年12月 Oracle大版内专家分月排行榜第一
2010年2月 Oracle大版内专家分月排行榜第三
2010年5月 Java大版内专家分月排行榜第一2010年2月 Java大版内专家分月排行榜第一2010年1月 Java大版内专家分月排行榜第一2010年1月 Oracle大版内专家分月排行榜第一2009年12月 Java大版内专家分月排行榜第一2009年12月 Oracle大版内专家分月排行榜第一
2010年2月 Oracle大版内专家分月排行榜第三
本帖子已过去太久远了，不再提供回复功能。SQL语句多表查询
我的图书馆
SQL语句多表查询
这里我们建几个SQL表，我们用个图来说明关系
这里tid的值和教师表中的id相匹配，pid和院系表中的id相匹配,也就是说这样建表就把三张表的关系给建立起来了。学生表在最低层，院系表在最高层。
&而这里我们就要讲一个外键的概念：外键是作为与主表的主键相关联的键值，外键可以有多个，同时有外键的表称作对应主键所在表的从表，那主键所在的表就作主表。图中，tid是教师表中id的外键，也就学生表是教师表的从表，同理，教师表也就是院系表的从表。
&&外键的主要作用是保持数据一致性，完整性，主要目的是控制存储在外键表中的数据。
&&当然在这时我们还不需要去建立真正的外键联系关系
&&现在我们要查询学生表，教师表，院系表的所有信息：
* from student,teacher,part&//查询这三个表中的所有信息
&&我们现在要查询某学生的教师是的名字：
teaname from student,teacher where (student.id ='') and (teacher.id
= student.tid)
//这里有两个约束条件，一个是约束学生的id号，一个是约束学生表和教师表的数据要相对应，也就是，这样查询的结果，就是该学生tid所对应教师表中id号的teaname字段的数据，也就是这位学生的教师名字了
&&现在有一个问题，我想问这个学生所在院系是什么？这个问题就应该迎刃而解了吧。
&&当然这只是简单的多表查询，其实在真正多表查询时，是用的以下方法：
&&&这里讲一个join方法
&&&该语句是实现多对对查询或一对一查询的方法，当我们在用简单多表查询的时候，它的匹配是无逻辑的，也就是没有具体的约束条件，它不会按照逻辑关系来约束。有可能这个学生会和一个无关第教师联系起来，或者和一个无关的院系联系起来。
&&&我们用一个简单的join方法查询：
teacher.teaname from student join teacher on(student.tid =
teacher.id)where (student.id='1')
&//查询id号为1的学生的教师的名字
&&这就是join语句的语法，它是个嵌套语句，也就是把一个表嵌套在另一个表中，一般都是自下而上的嵌套，如上例，就是学生表去加入教师表的集合。也就是从表加入主表。
&&这种join有两个参数，我们上例用的是缺省参数--innor,当我们直接用join时，就默认是这个参数，还有一个left，这两个参数在用时有很大的区别。
&&&&select
* from student left join teacher on(student.tid =
teacher.id)
这上面这个例子中，用left和不用left参数是有很大区别的。用如果在学生表中，有几个新报道的学生还未分配教师，那么，用left参数查询的时候也会显示出来，尽管他的tid为空，和教师表中的数据不匹配，但也会显示出来。&&若不用left
，那么就不会显示这几个新学生。
&&&&通过这个例子应该能更好的理解left的用处了吧，在处理中，我们要根据需求来进行设计查询语句。
&&当然，我们先前讲的外键这个时候我们也需要拿出来用了。下图所示：
&&&&我们现在建好了这个表，我们有如下设计需求，当我在删掉这个类别的时候，我们需要把该类别下的产品也删掉。我们可以用最原始的方法，也就是先查询所有该类别的产品，然后把类别下的产品全删完后才删除该类别，但这样，不显得很麻烦吗？
&&&&这里就该用上外键的功能了，首先我们建立好外键，也就是把cid设为类别表中id的外键
TABLE product ADD CONSTRAINT fk_product_cid
&&&&FOREIGN
&&&&REFERENCES
category(id);
//第一行是在product(产品表)中添加一个外键，第二行是此外键对应的字段是cid，第三行是此外键对应的键值为category(类别表)中的id.
&&那么这就建立好了外键的关系，但是这样还是不行，我们还需要了解一下外键的级联操作的相关信息：
&&&CASCADE（连锁关系，当主表变更，从表的相应信息也会变更）
&&&RESTRICT(禁止主表变更)
NULL(子表相应字段设置为空)
&&&。。。。。
&&一般我们用的最多的是连锁关系，而上例所讲的设计我们就需要用到连锁关系
TABLE product ADD CONSTRAINT fk_product_cid
&&&&FOREIGN
&&&&REFERENCES
category(id);
UPDATE CASCADE;
DELETE CASCADE;
//这就实现了更新的连锁和删除的连锁。也就是当主表的信息变更，从表的相关信息也会变更。当主表的信息删除，从表的相关联信息也会删除
--------------------------------------------------------------------------------------------------------------------------------------------
问题及描述：
--1.学生表
Student(S#,Sname,Sage,Ssex) --S# 学生编号,Sname 学生姓名,Sage 出生年月,Ssex
--2.课程表&
Course(C#,Cname,T#) --C# --课程编号,Cname 课程名称,T# 教师编号
--3.教师表&
Teacher(T#,Tname) --T# 教师编号,Tname 教师姓名
--4.成绩表&
SC(S#,C#,score) --S# 学生编号,C# 课程编号,score 分数
*/--创建测试数据create&table&Student(S#&varchar(10),Sname&nvarchar(10),Sage&datetime,Ssex&nvarchar(10))
insert&into&Student&values('01'&,
N'赵雷'&,&''&,
N'男')insert&into&Student&values('02'&,
N'钱电'&,&''&,
N'男')insert&into&Student&values('03'&,
N'孙风'&,&''&,
N'男')insert&into&Student&values('04'&,
N'李云'&,&''&,
N'男')insert&into&Student&values('05'&,
N'周梅'&,&''&,
N'女')insert&into&Student&values('06'&,
N'吴兰'&,&''&,
N'女')insert&into&Student&values('07'&,
N'郑竹'&,&''&,
N'女')insert&into&Student&values('08'&,
N'王菊'&,&''&,
N'女')create&table&Course(C#&varchar(10),Cname&nvarchar(10),T#&varchar(10))
insert&into&Course&values('01'&,
N'语文'&,&'02')
insert&into&Course&values('02'&,
N'数学'&,&'01')
insert&into&Course&values('03'&,
N'英语'&,&'03')
create&table&Teacher(T#&varchar(10),Tname&nvarchar(10))
insert&into&Teacher&values('01'&,
N'张三')insert&into&Teacher&values('02'&,
N'李四')insert&into&Teacher&values('03'&,
N'王五')create&table&SC(S#&varchar(10),C#&varchar(10),score&decimal(18,1))
insert&into&SC&values('01'&,&'01'&,&80)
insert&into&SC&values('01'&,&'02'&,&90)
insert&into&SC&values('01'&,&'03'&,&99)
insert&into&SC&values('02'&,&'01'&,&70)
insert&into&SC&values('02'&,&'02'&,&60)
insert&into&SC&values('02'&,&'03'&,&80)
insert&into&SC&values('03'&,&'01'&,&80)
insert&into&SC&values('03'&,&'02'&,&80)
insert&into&SC&values('03'&,&'03'&,&80)
insert&into&SC&values('04'&,&'01'&,&50)
insert&into&SC&values('04'&,&'02'&,&30)
insert&into&SC&values('04'&,&'03'&,&20)
insert&into&SC&values('05'&,&'01'&,&76)
insert&into&SC&values('05'&,&'02'&,&87)
insert&into&SC&values('06'&,&'01'&,&31)
insert&into&SC&values('06'&,&'03'&,&34)
insert&into&SC&values('07'&,&'02'&,&89)
insert&into&SC&values('07'&,&'03'&,&98)
--1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
--1.1、查询同时存在"01"课程和"02"课程的情况select&a.*&,
b.score&[课程'01'的分数],c.score&[课程'02'的分数]&from&Student a
, SC b , SC c&where&a.S#&=&b.S#&and&a.S#&=&c.S#&and&b.C#&=&'01'&and&c.C#&=&'02'&and&b.score&&&c.score
--1.2、查询同时存在"01"课程和"02"课程的情况和存在"01"课程但可能不存在"02"课程的情况(不存在时显示为null)(以下存在相同内容时不再解释)
select&a.*&,
b.score&[课程"01"的分数],c.score&[课程"02"的分数]&from&Student
a&left&join&SC
b&on&a.S#&=&b.S#&and&b.C#&=&'01'
left&join&SC
c&on&a.S#&=&c.S#&and&c.C#&=&'02'
where&b.score&&&isnull(c.score,0)
--2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
--2.1、查询同时存在"01"课程和"02"课程的情况select&a.*&,
b.score&[课程'01'的分数],c.score&[课程'02'的分数]&from&Student a
, SC b , SC c&where&a.S#&=&b.S#&and&a.S#&=&c.S#&and&b.C#&=&'01'&and&c.C#&=&'02'&and&b.score&&&c.score
--2.2、查询同时存在"01"课程和"02"课程的情况和不存在"01"课程但存在"02"课程的情况
select&a.*&,
b.score&[课程"01"的分数],c.score&[课程"02"的分数]&from&Student
a&left&join&SC
b&on&a.S#&=&b.S#&and&b.C#&=&'01'
left&join&SC
c&on&a.S#&=&c.S#&and&c.C#&=&'02'
where&isnull(b.score,0)&&&c.score
--3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select&a.S# , a.Sname
,&cast(avg(b.score)&as&decimal(18,2))
avg_scorefrom&Student a
, sc bwhere&a.S#&=&b.S#
group&by&a.S# ,
a.Snamehaving&cast(avg(b.score)&as&decimal(18,2))&&=&60&
order&by&a.S#
--4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
--4.1、查询在sc表存在成绩的学生信息的SQL语句。select&a.S# , a.Sname
,&cast(avg(b.score)&as&decimal(18,2))
avg_scorefrom&Student a
, sc bwhere&a.S#&=&b.S#
group&by&a.S# ,
a.Snamehaving&cast(avg(b.score)&as&decimal(18,2))&&&60&
order&by&a.S#--4.2、查询在sc表中不存在成绩的学生信息的SQL语句。
select&a.S# , a.Sname
,&isnull(cast(avg(b.score)&as&decimal(18,2)),0)
avg_scorefrom&Student
a&left&join&sc
bon&a.S#&=&b.S#
group&by&a.S# ,
a.Snamehaving&isnull(cast(avg(b.score)&as&decimal(18,2)),0)&&&60&
order&by&a.S#
--5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
--5.1、查询所有有成绩的SQL。select&a.S#&[学生编号],
a.Sname&[学生姓名],&count(b.C#)
选课总数,&sum(score)&[所有课程的总成绩]from&Student a , SC
b&where&a.S#&=&b.S#&
group&by&a.S#,a.Sname&
order&by&a.S#--5.2、查询所有(包括有成绩和无成绩)的SQL。
select&a.S#&[学生编号],
a.Sname&[学生姓名],&count(b.C#)
选课总数,&sum(score)&[所有课程的总成绩]from&Student
a&left&join&SC
b&on&a.S#&=&b.S#&
group&by&a.S#,a.Sname&
order&by&a.S#
--6、查询"李"姓老师的数量&
--方法1select&count(Tname)&["李"姓老师的数量]&from&Teacher&where&Tname&like&N'李%'
--方法2select&count(Tname)&["李"姓老师的数量]&from&Teacher&where&left(Tname,1)&=&N'李'
--7、查询学过"张三"老师授课的同学的信息&
select&distinct&Student.*&from&Student ,
SC , Course , Teacher&where&Student.S#&=&SC.S#&and&SC.C#&=&Course.C#&and&Course.T#&=&Teacher.T#&and&Teacher.Tname&=&N'张三'
order&by&Student.S#
--8、查询没学过"张三"老师授课的同学的信息&
select&m.*&from&Student
m&where&S#&not&in&(select&distinct&SC.S#&from&SC
, Course , Teacher&where&SC.C#&=Course.C#&and&Course.T#&=&Teacher.T#&and&Teacher.Tname&=&N'张三')&order&by&m.S#
--9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
--方法1select&Student.*&from&Student ,
SC&where&Student.S#&=&SC.S#&and&SC.C#&=&'01'&and&exists&(Select&1&from&SC
SC_2&where&SC_2.S#&=&SC.S#&and&SC_2.C#&=&'02')&order&by&Student.S#
--方法2select&Student.*&from&Student ,
SC&where&Student.S#&=&SC.S#&and&SC.C#&=&'02'&and&exists&(Select&1&from&SC
SC_2&where&SC_2.S#&=&SC.S#&and&SC_2.C#&=&'01')&order&by&Student.S#
--方法3select&m.*&from&Student
m&where&S#&in
&&select&S#&from
&&&&select&distinct&S#&from&SC&where&C#&=&'01'
&&&&union&all
&&&&select&distinct&S#&from&SC&where&C#&=&'02'
t&group&by&S#&having&count(1)&=&2&
)order&by&m.S#
--10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
--方法1select&Student.*&from&Student ,
SC&where&Student.S#&=&SC.S#&and&SC.C#&=&'01'&and&not&exists&(Select&1&fromSC
SC_2&where&SC_2.S#&=&SC.S#&and&SC_2.C#&=&'02')&order&by&Student.S#
--方法2select&Student.*&from&Student ,
SC&where&Student.S#&=&SC.S#&and&SC.C#&=&'01'&and&Student.S#&not&in&(SelectSC_2.S#&from&SC
SC_2&where&SC_2.S#&=&SC.S#&and&SC_2.C#&=&'02')&order&by&Student.S#
--11、查询没有学全所有课程的同学的信息&
--11.1、select&Student.*from&Student ,
SC&where&Student.S#&=&SC.S#&
group&by&Student.S# , Student.Sname ,
Student.Sage , Student.Ssex&having&count(C#)&&&(select&count(C#)from&Course)&
--11.2select&Student.*from&Student&left&join&SC&
on&Student.S#&=&SC.S#&
group&by&Student.S# , Student.Sname ,
Student.Sage , Student.Ssex&having&count(C#)&&&(select&count(C#)from&Course)&
--12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息&
select&distinct&Student.*&from&Student ,
SC&where&Student.S#&=&SC.S#&and&SC.C#&in&(select&C#&from&SC&whereS#&=&'01')&and&Student.S#&&&&'01'
--13、查询和"01"号的同学学习的课程完全相同的其他同学的信息&
select&Student.*&from&Student&where&S#&in
(select&distinct&SC.S#&from&SC&where&S#&&&&'01'&and&SC.C#&in&(select&distinct&C#&from&SC&where&S#&=&'01')&
group&by&SC.S#&having&count(1)&=&(select&count(1)&from&SC&where&S#='01'))&
--14、查询没学过"张三"老师讲授的任一门课程的学生姓名&
select&student.*&from&student&where&student.S#&not&in&
(select&distinct&sc.S#&from&sc
, course , teacher&where&sc.C#&=&course.C#&and&course.T#&=&teacher.T#&andteacher.tname&=&N'张三')
order&by&student.S#
--15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩&
select&student.S# , student.sname
,&cast(avg(score)&as&decimal(18,2))
avg_score&from&student ,
sc&where&student.S#&=&SC.S#&and&student.S#&in&(select&S#&from&SC&where&score&&&60&group&by&S#&havingcount(1)&&=&2)
group&by&student.S# ,
student.sname
--16、检索"01"课程分数小于60，按分数降序排列的学生信息
select&student.*&, sc.C# ,
sc.score&from&student ,
sc&where&student.S#&=&SC.S#&and&sc.score&&&60&and&sc.C#&=&'01'
order&by&sc.score&desc&&
--17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
--17.1 SQL 2000
静态&select&a.S# 学生编号 , a.Sname 学生姓名
&&&&&&&max(case&<ame&when&N'语文'&then&b.score&else&null&end)&[语文],
&&&&&&&max(case&<ame&when&N'数学'&then&b.score&else&null&end)&[数学],
&&&&&&&max(case&<ame&when&N'英语'&then&b.score&else&null&end)&[英语],
&&&&&&&cast(avg(b.score)&as&decimal(18,2))
平均分from&Student
a&left&join&SC
b&on&a.S#&=&b.S#
left&join&Course
c&on&b.C#&=&c.C#
group&by&a.S# ,
a.Snameorder&by&平均分&desc
--17.2 SQL 2000
动态&declare&@sql&nvarchar(4000)
set&@sql&=&'select
a.S#&'&+&N'学生编号'&+&'&,
a.Sname&'&+&N'学生姓名'select&@sql&=&@sql&+&',max(ame when N'''+Cname+'''&then b.score else null end)
['+Cname+']'from&(select&distinct&Cname&from&Course)&as&t
set&@sql&=&@sql&+&'&, cast(avg(b.score) as
decimal(18,2))&'&+&N'平均分'&+&'&from
Student a left join SC b on a.S# = b.S# left join Course c on b.C#
group by a.S# , a.Sname order
by&'&+&N'平均分'&+&'&desc'exec(@sql)
--24、查询学生平均成绩及其名次&
--24.1 查询学生的平均成绩并进行排名，sql
2000用子查询完成，分平均成绩重复时保留名次空缺和不保留名次空缺两种。select&t1.*&,
px&=&(select&count(1)&from&
&&select&m.S#&[学生编号]&,&
m.Sname&[学生姓名]&,
&&&&&&&&&isnull(cast(avg(score)&as&decimal(18,2)),0)&[平均成绩]
&&from&Student
m&left&join&SC
n&on&m.S#&=&n.S#&
&&group&by&m.S# ,
) t2&where&平均成绩&&&t1.平均成绩)&+&1&from&
&&select&m.S#&[学生编号]&,&
m.Sname&[学生姓名]&,
&&&&&&&&&isnull(cast(avg(score)&as&decimal(18,2)),0)&[平均成绩]
&&from&Student
m&left&join&SC
n&on&m.S#&=&n.S#&
&&group&by&m.S# ,
order&by&px
select&t1.*&,
px&=&(select&count(distinct&平均成绩)&from&
&&select&m.S#&[学生编号]&,&
m.Sname&[学生姓名]&,
&&&&&&&&&isnull(cast(avg(score)&as&decimal(18,2)),0)&[平均成绩]
&&from&Student
m&left&join&SC
n&on&m.S#&=&n.S#&
&&group&by&m.S# ,
) t2&where&平均成绩&&=&t1.平均成绩)&from&
&&select&m.S#&[学生编号]&,&
m.Sname&[学生姓名]&,
&&&&&&&&&isnull(cast(avg(score)&as&decimal(18,2)),0)&[平均成绩]
&&from&Student
m&left&join&SC
n&on&m.S#&=&n.S#&
&&group&by&m.S# ,
order&by&px
--24.2 查询学生的平均成绩并进行排名，sql
2005用rank,DENSE_RANK完成，分平均成绩重复时保留名次空缺和不保留名次空缺两种。
select&t.*&,
px&=&rank()&over(order&by&[平均成绩]&desc)&from
&&select&m.S#&[学生编号]&,&
m.Sname&[学生姓名]&,
&&&&&&&&&isnull(cast(avg(score)&as&decimal(18,2)),0)&[平均成绩]
&&from&Student
m&left&join&SC
n&on&m.S#&=&n.S#&
&&group&by&m.S# ,
order&by&px
select&t.*&,
px&=&DENSE_RANK()&over(order&by&[平均成绩]&desc)&from
&&select&m.S#&[学生编号]&,&
m.Sname&[学生姓名]&,
&&&&&&&&&isnull(cast(avg(score)&as&decimal(18,2)),0)&[平均成绩]
&&from&Student
m&left&join&SC
n&on&m.S#&=&n.S#&
&&group&by&m.S# ,
order&by&px
--25、查询各科成绩前三名的记录
分数重复时保留名次空缺select&m.*&,
n.C# , n.score&from&Student m, SC
n&where&m.S#&=&n.S#&and&n.score&in&
(select&top&3&score&from&sc&where&C#&=&n.C#&order&by&score&desc)&order&by&n.C#
, n.score&desc
分数重复时不保留名次空缺，合并名次
2000用子查询实现select&*&from&(select&t.*&,
px&=&(select&count(distinct&score)&from&SC&where&C#&=&t.C#&and&score&&=&t.score)&fromsc
t) m&where&px&between&1&and&3&order&by&m.c#
2005用DENSE_RANK实现select&*&from&(select&t.*&,
px&=&DENSE_RANK()&over(partition&by&c#&order&by&score&desc)&from&sc
t) m&where&pxbetween&1&and&3&order&by&m.C#
--26、查询每门课程被选修的学生数&
,&count(S#)[学生数]&from&sc&group&by&C#
--27、查询出只有两门课程的全部学生的学号和姓名&
select&Student.S# ,
Student.Sname
from&Student ,
where&Student.S#&=&SC.S#&
group&by&Student.S# ,
Student.Sname
having&count(SC.C#)&=&2
order&by&Student.S#
--28、查询男生、女生人数&
select&count(Ssex)&as&男生人数&from&Student&where&Ssex&=&N'男'
select&count(Ssex)&as&女生人数&from&Student&where&Ssex&=&N'女'
select&sum(case&when&Ssex&=&N'男'&then&1&else&0&end)&[男生人数],sum(case&when&Ssex&=&N'女'&then&1&else&0end)&[女生人数]&from&student
select&case&when&Ssex&=&N'男'&then&N'男生人数'&else&N'女生人数'&end&[男女情况]&,&count(1)&[人数]&from&studentgroup&by&case&when&Ssex&=&N'男'&then&N'男生人数'&else&N'女生人数'&end
--29、查询名字中含有"风"字的学生信息
select&*&from&student&where&sname&like&N'%风%'
select&*&from&student&where&charindex(N'风'&,
sname)&&&0
--30、查询同名同性学生名单，并统计同名人数&
select&Sname&[学生姓名],&count(*)&[人数]&from&Student&group&by&Sname&having&count(*)&&&1
--31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)&
select&*&from&Student&where&year(sage)&=&1990
select&*&from&Student&where&datediff(yy,sage,'')&=&0
select&*&from&Student&where&datepart(yy,sage)&=&1990
select&*&from&Student&where&convert(varchar(4),sage,120)&=&'1990'
--32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列&
select&m.C# , m.Cname
,&cast(avg(n.score)&as&decimal(18,2))
from&Course m, SC
where&m.C#&=&n.C#&&&&
group&by&m.C# ,
order&by&avg_score&desc,
--33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩&
select&a.S# , a.Sname
,&cast(avg(b.score)&as&decimal(18,2))
from&Student a , sc
where&a.S#&=&b.S#
group&by&a.S# ,
having&cast(avg(b.score)&as&decimal(18,2))&&=&85&
order&by&a.S#
--34、查询课程名称为"数学"，且分数低于60的学生姓名和分数&
select&sname ,
from&Student , SC ,
where&SC.S#&=&Student.S#&and&SC.C#&=&Course.C#&and&<ame&=&N'数学'&and&score&&&60&
--35、查询所有学生的课程及分数情况；&
select&Student.*&,
ame , SC.C# ,
SC.score&&
from&Student, SC ,
where&Student.S#&=&SC.S#&and&SC.C#&=&Course.C#&
order&by&Student.S# ,
--36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；&
select&Student.*&,
ame , SC.C# ,
SC.score&&
from&Student, SC ,
where&Student.S#&=&SC.S#&and&SC.C#&=&Course.C#&and&SC.score&&=&70&
order&by&Student.S# ,
--37、查询不及格的课程select&Student.*&,
ame , SC.C# ,
SC.score&&
from&Student, SC ,
where&Student.S#&=&SC.S#&and&SC.C#&=&Course.C#&and&SC.score&&&60&
order&by&Student.S# ,
--38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；&
select&Student.*&,
ame , SC.C# ,
SC.score&&
from&Student, SC ,
where&Student.S#&=&SC.S#&and&SC.C#&=&Course.C#&and&SC.C#&=&'01'&and&SC.score&&=&80&
order&by&Student.S# ,
--39、求每门课程的学生人数&
select&Course.C# ,
ame ,&count(*)&[学生人数]
from&Course ,
where&Course.C#&=&SC.C#
group&by& Course.C# ,
order&by&Course.C# ,
--40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
当最高分只有一个时select&top&1&Student.*&,
ame , SC.C# ,
SC.score&&
from&Student, SC ,
Course , Teacher
where&Student.S#&=&SC.S#&and&SC.C#&=&Course.C#&and&Course.T#&=&Teacher.T#&and&Teacher.Tname&=&N'张三'
order&by&SC.score&desc
--40.2 当最高分出现多个时select&Student.*&,
ame , SC.C# ,
SC.score&&
from&Student, SC ,
Course , Teacher
where&Student.S#&=&SC.S#&and&SC.C#&=&Course.C#&and&Course.T#&=&Teacher.T#&and&Teacher.Tname&=&N'张三'&and
SC.score&=&(select&max(SC.score)&from&SC
, Course , Teacher&where&SC.C#&=&Course.C#&and&Course.T#&=Teacher.T#&and&Teacher.Tname&=&N'张三')
--41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩&
--方法1select&m.*&from&SC
m ,(select&C# ,
score&from&SC&group&by&C#
, score&having&count(1)&&&1)
where&m.C#=&n.C#&and&m.score&=&n.score&order&by&m.C#
, m.score , m.S#
--方法2select&m.*&from&SC
m&where&exists&(select&1&from&(select&C#
, score&from&SC&group&by&C#
, score&havingcount(1)&&&1)
where&m.C#=&n.C#&and&m.score&=&n.score)&order&by&m.C#
, m.score , m.S#
--42、查询每门功成绩最好的前两名&
select&t.*&from&sc
t&where&score&in&(select&top&2&score&from&sc&where&C#&=&T.C#&order&by&score&desc)&order&byt.C#
, t.score&desc
--43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列&&
select&Course.C# ,
ame ,&count(*)&[学生人数]
from&Course ,
where&Course.C#&=&SC.C#
group&by& Course.C# ,
having&count(*)&&=&5
order&by&[学生人数]&desc&,
Course.C#&
--44、检索至少选修两门课程的学生学号&
select&student.S# ,
student.Sname&
from&student ,
where&student.S#&=&SC.S#&
group&by&student.S# ,
student.Sname&
having&count(1)&&=&2
order&by&student.S#&
--45、查询选修了全部课程的学生信息&
根据数量来完成select&student.*&from&student&where&S#&in
(select&S#&from&sc&group&by&S#&having&count(1)&=&(select&count(1)&from&course))
--方法2 使用双重否定来完成select&t.*&from&student
t&where&t.S#&not&in&
&&select&distinct&m.S#&from
&&&&select&S#
, C#&from&student ,
& ) m&where&not&exists&(select&1&from&sc
n&where&n.S#&=&m.S#&and&n.C#&=&m.C#)
--方法3 使用双重否定来完成select&t.*&from&student
t&where&not&exists(select&1&from&
&&select&distinct&m.S#&from
&&&&select&S#
, C#&from&student ,
& ) m&where&not&exists&(select&1&from&sc
n&where&n.S#&=&m.S#&and&n.C#&=&m.C#)
) k&where&k.S#&=&t.S#
--46、查询各学生的年龄
只按照年份来算select&*&,&datediff(yy
, sage ,&getdate())&[年龄]&from&student
--46.2 按照出生日期来算，当前月日 &
出生年月的月日则，年龄减一select&*&,&case&when&right(convert(varchar(10),getdate(),120),5)&&&right(convert(varchar(10),sage,120),5)&thendatediff(yy
, sage ,&getdate())&-&1&else&datediff(yy
, sage ,&getdate())&end&[年龄]&from&student
--47、查询本周过生日的学生select&*&from&student&where&datediff(week,datename(yy,getdate())&+right(convert(varchar(10),sage,120),6),getdate())&=&0
--48、查询下周过生日的学生select&*&from&student&where&datediff(week,datename(yy,getdate())&+right(convert(varchar(10),sage,120),6),getdate())&=&-1
--49、查询本月过生日的学生select&*&from&student&where&datediff(mm,datename(yy,getdate())&+right(convert(varchar(10),sage,120),6),getdate())&=&0
--50、查询下月过生日的学生select&*&from&student&where&datediff(mm,datename(yy,getdate())&+right(convert(varchar(10),sage,120),6),getdate())&=&-1
drop&table&
Student,Course,Teacher,SC
TA的最新馆藏[转]&[转]&[转]&[转]&}