0722 sql 语句那些事

     刚接触这个项目的时候,还记得同事拿着一堆sql拼接和嵌套的代码,来问我们。当时感觉真的好麻烦,好复杂。以前用的sql都是简单的增删该查,后来ITOO中也是封装好的,所以就懒了。但是现在又遇到这个问题啦,这几天我负责的是手机端的考勤统计部分,所以用到了大量的纯sql查询语句。又将以前的知识好好学习了一下,总结一下。

     这是用slqsever建立的两张表,下面几个例子都将以A、B两表为基础,介绍几种常用的连接。

  

 一、左连接

<span> select * from A  left join  B   on A.studentID = B.StudentID

</span>

 left join 是以A表的记录为基础的,A可以看成是左表,B可以看成是右表,left join 是以左为基准的。也就是说,左表(A) 的记录会全部显示出来,而右表B 只会显示符合搜索条件的记录。上图中A..studentID = B.studentID.B表中没有值的地方,就会显示null.

运算结果:


二、右链接

<span> select * from  A right join B on A .studentID = B .StudentID</span>

左连接和右链接正好相反,这次是以右表为基础的,A表是空的地方就会用NUll来填充。

三、内连接

<span>   select * from A inner join B on A.studentID = B.StudentID</span>

很明显,内连接中并不以谁为基准,它只显示符合条件的记录。上图中只是列出了符合要求的记录。实际上是左右链接的交集。

 四、全连接

 这是我在项目中使用了左右链接之后,仍然不能够达到想要的效果,所以就想到了全连接。但是mysql 并不支持全连接,但是可以通过合并左右连接达到类似的效果。

<span>     --左连接
     select  a.studentID ,a.studentName, B.StudentID,b.age,b.sex  from A left join B on A.studentID = B.StudentID  
      --结合
       union
       --右链接
      select  a.studentID ,a.studentName, b.StudentID,b.age,b.sex  from A right join B on A.studentID = B.StudentID</span>

全连接返回参与连接的两个数据集合中的全部数据,无论他们是否有匹配的行。在功能上等价于,对这两个数据集合分别进行左外链接和右外链接。然后在消去重复行的并操作,将上述两个结果集合并为一个结果集。

五、模糊查询语句

like像  通配符:% 任意字符 _ 代表单个字符。

例如:

<span>select * from A 
 where studentName like '振%'</span>

再如:

<span>select * from A where studentName  like '小_'  </span>

在做电子班牌,查询的时候,用到一个模糊匹配查询,语句如下。

<span> if (txtKeyword.Text != "")
               {
//  根据文件描述、状态、IP、操作者、日期查询。
 strWhere.AppendFormat("description LIKE '%{0}%'or state LIKE '%{0}%' OR IP LIKE '%{0}%' OR   operater like  '%{0}%' OR createtime  LIKE '%{0}%' ", txtKeyword.Text.Trim());
              }
</span>

六、项目实战

在统计的时候,datagriview不能够满足需要,所以同事用div拼成了一个表格。我负责数据这边,所以我这边需要按照他需要的参数,传过去相应的数据。所以下面就开始了sql嵌套查询和拼接连接之旅。

需求是这样的:(1)学生考勤,是通过人脸识别技术的。通过处理,将每天考勤的记录写入考勤表。(2) 查询缺勤人数。应该是日期是今天,不在考勤表, 不在请假表的记录。需要联合请假表、考勤表、学生信息表。(3)统计请假人数,用到的是考勤表。

最后达到的效果应该是:

                                         

 这个表格是 同事用div拼接起来的,我需要做的按照这种格式,传入级部编号、班级班号、缺勤人数、请假人数这些参数。

查询统计缺勤人数:

<span> <pre name="code" class="sql">SELECT stu.classno,cla .partno,COUNT(*) as queqin FROM(
SELECT
	s.classno,
	s.partyid
FROM
	sc_student_info s
WHERE
	  s.partyid NOT IN (
		SELECT
			partyid
		FROM
			(
				SELECT
					partyid,
					logindate,
					group_concat(IP),
					max(createtime) AS CreateTimeMax
				FROM
					sc_login_trace
				WHERE
					logindate = '2016/7/21'
				GROUP BY
					partyid
			) t
	)
AND s.partyid NOT IN (
	SELECT
		b.studentid
	FROM
		sc_absent b
	WHERE
		state = 'MA'
	AND validflag = '1'
	AND starttime <= '2016/7/21'
	AND endtime >= '2016/7/21'
)
)  stu LEFT JOIN sc_class_info  cla on cla.classno =  stu.classno GROUP BY cla.classno</span>
结果如下:

查询缺勤人数:

<span>SELECT   ty.partno , tt.classno ,tt.qingjia from (
SELECT  stud.classno, COUNT(*) as qingjia from  (
SELECT  ab.studentid FROM sc_absent ab where   starttime <= '2016-7-22' and endtime >='2016-7-22' )   absent
LEFT JOIN sc_student_info  stud on  stud.partyid = absent.studentid  GROUP BY classno
)  tt LEFT JOIN sc_class_info  ty on ty.classno = tt.classno
</span>

结果如下:


最后统计缺勤和请假人数列表:将上面的表联合起来。

<span>
	SELECT
		e.classno,
		sum(e.queqin) AS queqin,
		sum(e.qingjia) AS qingjia,
		e.partno AS jibu
	FROM
		(
			(
				SELECT
					cla.partno,
					stu.classno,
					COUNT(*) AS queqin,
					0 AS qingjia
				FROM
					(
						SELECT
							s.classno,
							s.classdesc,
							s.partyid
						FROM
							sc_student_info s
						LEFT JOIN (
							SELECT
								partyid,
								logindate,
								group_concat(IP),
								max(createtime) AS CreateTimeMax
							FROM
								sc_login_trace
							WHERE
								logindate = '2016/7/23'  
							GROUP BY
								partyid
						) AS login ON login.partyid = s.partyid
						LEFT JOIN (
							SELECT
								b.studentid
							FROM
								sc_absent b
							WHERE
								state = 'MA'
							AND validflag = '1'
							AND starttime <= '2016/7/23'
							AND endtime >= '2016/7/23'
						) AS nt ON s.partyid = nt.studentid
						WHERE
							login.partyid IS NULL
						AND nt.studentid IS NULL
  
					) stu
				LEFT JOIN sc_class_info cla ON cla.classno = stu.classno
				GROUP BY
					cla.classno
			)
			UNION
				(
					SELECT
						ty.partno,
						tt.classno,
						0 AS queqin,
						tt.qingjia
					FROM
						(
							SELECT
								stud.classno,
								COUNT(*) AS qingjia
							FROM
								(
									SELECT
										ab.studentid
									FROM
										sc_absent ab
									WHERE
										starttime <= '2016/7/23'
									AND endtime >= '2016/7/23'
								) absent
							LEFT JOIN sc_student_info stud ON stud.partyid = absent.studentid 
							GROUP BY
								classno
						) tt
					LEFT JOIN sc_class_info ty ON ty.classno = tt.classno
				)
		) e  
	GROUP BY
		e.classno,
		e.partno
	ORDER BY
		e.classno DESC
</span>

运行效果:


这是写的第二版的代码,刚开始是用全连接实现的,但是会有空值,效果十分不好。最后将缺勤表和考勤表都插入一列空行,设置默认值都为0 , 变成4列,然后通过union连接,就成为了上面的效果。具体都在上面sql语句中了。

总结:

  这几天的项目都在跟sql语句打交道了,虽然功能实现了,但是sql代码看着好不专业,而且一定还有更加简单的方法。也学习到很多sql的使用方法,继续加油!相信熟练之后,可以写的更快更加规范,回去接着写sql去。



本页内容版权归属为原作者,如有侵犯您的权益,请通知我们删除。

主流NoSQL技术详解与比较 - 2016-07-24 19:07:00

主流NoSQL分类 1键值(Key-Value)存储数据库 键值对NoSQL是比较流行的一种NoSQL的解决方案,特点就是采用键值对来存储数据,它的优势在于容易部署和简单性,但是如果查询的部分只是整个数据库的小部分,那性能并不是特别突出。 2列存储数据库 列存储NoSQL比较适合的场景是处理海量的分布式存储的数据,它的主键可能是指向多个列的,数据量增加的时候几乎不影响性能。 3文档型数据库 文档数据库是采用类似键值对的方式进行存储,更准确的说是采用的JSON的格式进行存储,可以嵌套键值对,文档数据库比键值

Spark简要学习 - 2016-07-24 19:07:20

什么是Spark? 在Apache的网站上,有非常简单的一句话,'Spark is a fast and general engine',就是说Spark是一个统一的计算引擎,而且突出fast。那么具体是做什么的?是做large-scale的processing,即大数据处理。 Spark is a fast and general engine for large-scale processing. 这句话非常简单,但是它突出了Spark的一些特点:第一个特点就是Spark是一个并行式的、内存的、计算密

CentOS上面安装Oracle 11GR2 - 2016-07-24 17:07:10

正常图形化界面安装 安装X Window yum groupinstall "X Window System" yum install unzip .x 86_64 vim java- 1.8 .0 -openjdk .x 86_64 java- 1.8 .0 -openjdk-devel .x 86_64 安装依赖软件包 yum install binutils compat -libstdc ++- 33 elfutils -libelf elfutils -libelf -devel gcc gcc

pl/sql知识点总结 - 2016-07-24 14:07:00

概念 pl/sql(procedural language/sql)是oracle专有的在sql上扩展的一门语言,不仅可以写sql语言,还可以定义变量和常量。 pl/sql编写的存储过程,函数等可以提高效率,省去了传统程序与数据库交互时的编译时间。(例如:传统java程序在访问数据库带着sql语句访问,sql语句需要编译后才能被数据库识别) 编写 存储过程 SQL create or replace procedure sp_pro1 is 2 begin 3 insert into test value
实际项目实践过程中,经常会遇到一些形如“ORA-XXXX”的Oracle错误提示,此时,我们便会打开百度、Google,搜索相关技术解决方案。本系列博文的目的就在于,对常见的Oracle错误提示进行总结,不断完善。 ORA-12560 ORA-12560: TNS: protocol adapter error(SQLPLUS) ORA-27101: shared memory realm does not exist(PL/SQL Developer) (1)原因分析 在path环境变量中Oracle_
调试经验: 1. 编译包时,如果出现编译错误(PL/SQL developer中包名出现红叉),可通过以下语句查看: SELECT   name,line, POSITION, text     FROM user_errors    WHERE NAME = 'INITTEST' ORDER BY SEQUENCE; 2. 上述语句中,引号中的包名(如,INITTEST)一定要大写,否则查不到 另外,PL/SQL中的包分为包规范(包的定义)和包体(包的实现)两部分,是PL/SQL编程的基本单元。 虽然名

DB2数据库的学习及总结 - 2016-07-23 19:07:12

                                                                                                                                                   db2命令 给db2用赋权的命令 db2 grant dbadm on database to user dsp #导出数据库结构会生成一个faldb20160224.sql文件  db2look -d udep -
对于PL/SQL这种基本上面向过程的语言来讲,子程序(过程和函数)就是其核心所在了。 整个解决方案可通过组织、调用由过程和函数构成的模块来实现。 在“Divide and conquer”(分而治之)的编程世界中,子程序就是程序员手中的兵器,用的越熟练越好。 --代码 13.1 创建过程示例CREATE OR REPLACE PROCEDURE newdept ( p_deptno dept.deptno%TYPE, --部门编号 p_dname dept.dname%TYPE, --部门名称 p_loc
调试nocopy功能时,为了看到实际效果,设置了一个比较大的循环次数,结果悲剧了: 运行了近1个小时没出结果,电脑死机(任务管理器都打不开);无奈只能强行关机,开机时间又特别长,一度让我以为系统崩溃。 看来,PL/SQL developer下调试这种暴力计算的程序风险很高啊,我在Eclipse下调试Java程序时从来没碰到过这种造成电脑死机的情况。 不过,这章的内容实用性很强,死机也值了(有点心疼电脑了 ) -代码13.11 使用%Type定义形式参数CREATE OR REPLACE PROCEDURE
Less - 54 Challenge-1 Test Sourse Code Solution Less - 55 Challenge-2 Test Sourse Code Solution Less - 56 Challenge-3 Test Sourse Code Solution Less - 57 Challenge-4 Test Sourse Code Solution Less - 58 Challenge-5 Test Sourse Code Solution Less - 59 Chall