最近一直在忙,也没有太多时间停留下来写博客。晚上遇到一个觉得很有趣的sql题,可能对初学者和我这种菜鸟会有帮助,所以决定分享给大家。
由于笔者天生笨拙,且思维不严谨,也实在不擅长写sql语句,高手请勿见笑,就请直接跳过本文吧。
背景就不多介绍了,先建表,插入测试数据吧。字段那些都有注释
--医生表
CREATE TABLE doctor
(
id INT IDENTITY(1, 1) , --ID 自增长
docNumber NVARCHAR(50) NOT NULL , --医生编码
NAME NVARCHAR(50) NOT NULL --医生姓名
)
go
--插入测试数据
INSERT INTO doctor
VALUES ( '007', 'Tom' )
INSERT INTO doctor
VALUES ( '008', 'John' )
INSERT INTO doctor
VALUES ( '009', 'Jim' )
--号源表(挂号表)
CREATE TABLE Nosource
(
id INT IDENTITY(1, 1) ,
docNumber NVARCHAR(50) NOT NULL , --和医生表中的医生编码对应
workTime DATETIME NOT NULL
)
go
--插入测试数据
INSERT INTO Nosource
VALUES ( '007', '20120819' )
INSERT INTO Nosource
VALUES ( '007', '20120820' )
INSERT INTO Nosource
VALUES ( '007', '20120821' )
INSERT INTO Nosource
VALUES ( '008', '20120821' )
表建好之后,测试数据也OK。下面开始说需求啦。
1.查出每位医生的相关信息,以及该医生所拥有的号源数量。
这简直太简单了,可能连刚学会helloWorld和一点点数据库基础的朋友都会严重真心BS。不过代码还是写出来。
--简单的分组查询即可搞定
SELECT COUNT(nos.id) AS PersonNumSounceCOUNT , --总数
dct.ID AS docID ,
dct.NAME ,
dct.docNumber ,
nos.workTime
FROM doctor AS dct
LEFT JOIN Nosource AS nos ON dct.docNumber = nos.docNumber
GROUP BY dct.ID ,
dct.NAME ,
dct.docNumber ,
nos.workTime
确实简单啊。一个小小的分组就能搞定的。还卖什么关子呢。
那现在需求改变,需要按条件去匹配:要求号源表的workTime大于当前日期才算有效的,否则就不匹配。
如果workTime条件不匹配的医生,对应的PersonNumSounceCOUNT字段的值应为0 ;例如:Jim医生没有匹配和符合条件的号源,其PersonNumSounceCOUNT字段值应为0。抬头仰望天空40度,想想能够用where关键字过滤,然后一次性查询出来吗?试试吧。
SELECT COUNT(nos.id) AS PersonNumSounceCOUNT , --总数
dct.ID ,
dct.NAME ,
dct.docNumber ,
nos.workTime
FROM doctor AS dct
LEFT JOIN Nosource AS nos ON dct.docNumber = nos.docNumber
WHERE DATEDIFF(day, GETDATE(), nos.workTime) > 0
GROUP BY dct.ID ,
dct.NAME ,
dct.docNumber ,
nos.workTime
相信有人会写出上面的代码来。可是执行查询后,发现完全不符合要求啊。连Jim医生的基本信息和表记录也都被过滤掉了,不见了。咋回事啊?
原因很简单嘛。在连接查询的后面使用"where"关键字,会过滤连接查询的结果集中的数据。由于右表(号源表)的条件不匹配,也会导致左表(医生表)的数据被过滤掉。
所以,会出现以上的现象(Jim医生的信息和记录都不见了)。要想一次性查出来可能吗?到底该如何去实现呢?
其实,正确的写法应该是这样的:
SELECT COUNT(nos.id) AS PersonNumSounceCOUNT , --总数
dct.ID ,
dct.NAME ,
dct.docNumber ,
nos.workTime
FROM doctor AS dct
LEFT JOIN ( SELECT *
FROM Nosource
WHERE DATEDIFF(day, GETDATE(), workTime) > 0
) AS nos ON dct.docNumber = nos.docNumber
GROUP BY dct.ID ,
dct.NAME ,
dct.docNumber ,
nos.workTime
再执行一下,果然OK,是满足要求的结果。思路就是:只需要过滤右表,就将(使用子查询)过滤后的结果集作为连接查询的右表,然后再去连接,分组......
其实编写简洁而高性能的sql语句,是需要很强的逻辑思维能力(和数学分不开)和经验的。还有种更简单的写法:
SELECT sum(case when nos.workTime>getdate then 1 else 0 end) AS PersonNumSounceCOUNT , --总数
dct.ID AS docID ,
dct.NAME ,
dct.docNumber
FROM doctor AS dct
LEFT JOIN Nosource AS nos ON dct.docNumber = nos.docNumber
GROUP BY dct.ID ,
dct.NAME ,
dct.docNumber
这样去解释,不知道大家是否能够理解,反正大致意思就是这样的。笔者的表达能力和水平确实有限,难免有偏差,望读者谅解!
本文出自http://blog.csdn.net/dinglang_2009,转载请注明出处。
分享到:
相关推荐
最近遇到一个觉得很有趣的sql题,可能对初学者和我这种菜鸟会有帮助,所以小编决定分享给大家
查询数据指从数据库中获取所需要的数据。查询数据是数据库操作中最常用,也是最重要的操作。用户可以根据自己对数据的需求,使用不同的查询方式。通过不同的查询方式,可以获得不同的数据。MySQL中是使用SELECT语句...
子查询是一个嵌套在select、insert、update或delete语句或其他子查询中的查询。任何允许使用表达式的地方都可以使用子查询。子查询也称为内部查询或内部选择,而包含子查询的语句也成为外部查询或外部选择。 # ...
《精通SQ:结构化查询语言详解》全面讲解SQL语言,提供317个典型应用,读者可以随查随用,针对SQL Server和Oracle进行讲解,很有代表性。 全书共包括大小实例317个,突出了速学速查的特色。《精通SQ:结构化查询语言...
讲解SQL语言的应用,提供了近500个曲型应用,读者可以随查随用,深入讲解SQL语言的各种查询语句,详细介绍数据库设计及管理,详细讲解存储过程、解发器和游标等知识,讲解了SQL语言在高级语言中的具体应用。...
10.1.1 在多表查询中使用子查询 187 10.1.2 在子查询中使用聚合函数 188 10.2 创建和使用返回多行的子查询 190 10.2.1 in子查询 190 10.2.2 in子查询实现集合交和集合差运算 191 10.2.3 exists子查询 192 ...
要高效地处理查询和数据库设计,你必须对SQL连接声明有较好的了解。在基本的逻辑句法之外,还有一些关于连接和简便数据参考方面的概念,例如别名,自连接和ON子句。 别名不只是做连接 别名是一个SQL功能,他允许你在...
注释:用于制定某个集合使用 in 关键字,也可以使用 or 连接符; 答案: 法1:select * from sc where 成绩=85 or 成绩=86or 成绩=88; 法2:select *from sc where 成绩 in(85,86,88); 3.查询'95031'班的学生人数。 注释:...
sql="select * from 数据表 where 字段名 like \'%字段值%\' order by 字段名 [desc]" sql="select top 10 * from 数据表 where 字段名 order by 字段名 [desc]" sql="select * from 数据表 where 字段名 in (\'值...
经典SQL语句大全 下列语句部分是Mssql语句,不可以在access中使用。... Set 对象变量名=连接对象.Execute("SQL 查询语言") Execute方法调用后,会自动创建记录集对象,并将查询结果存储在该记录...
SQL Server 中 EXEC 与 SP_EXECUTESQL 的区别 MSSQL为我们提供了两种动态执行SQL语句的命令,分别是 EXEC 和 SP_EXECUTESQL ,我们先来看一下两种方式的用法。 先建立一个表,并添加一些数据来进行演示: 复制代码...
【IT教程网】16-等值连接查询的操作 【IT教程网】17-左外连接查询的操作 【IT教程网】18-右外连接查询的操作 【IT教程网】19-完全外连接查询的操作 【IT教程网】2-创建表-2 【IT教程网】20-where子查询的操作 ...
结构化查询语言(Structured Query Language)简称SQL,是一种关系数据库查询语言,用于存取数据以及查询、更新和管理关系数据库系统。 1.2 语句结构 1.2.1 数据查询语言(DQL) 对数据库进行的信息查询,select。 ...
以下示例显示了语义等价的查询并说明了使用 EXISTS 关键字和 IN 关键字的区别。两个都是有效子查询示例,用于检索产品型号为长袖标志运动衫且 ProductModelID 编号在 Product 和 ProductModel 两个表中相匹配的每种...
SQL由多个关键字构成。 SQL语句由子句构成,有些子句是必须的,有些是可选的。 在处理SQL语句时,其中所有的空格都被忽略(空格只用来分开单词,连续多个空格当一个用)。 SQL语句可以在一行上写出,建议多行写出,...
explain关键字的使用方法很简单,就是把它放在select查询语句的前面。 mysql查看是否使用索引,简单的看type类型就可以。如果它是all,那说明这条查询语句遍历了所有的行,并没有使用到索引。 比如:explain ...
一、SQL 结构化查询语言 包括DDL(数据定义语言)、DCL(数据控制语言)、 DQL(数据查询语言)、DML(数据操纵语言) 二、SQL的特点 SQL 语句不区分大小写 SQL 语句能输入一行或多行 关键字不能整行缩写或...
在进行开发和实际应用中,用户不应该只用root用户进行连接数据库,虽然使用root用户进行测试时很方便,但会给系统带来重大安全隐患,也不利于管理技术的提高。我们给一个应用中使用的用户赋予最恰当的数据库权限。如...