数据库为MS SQL Server 2012,所用数据为产品帐套中的测试数据,示例字段的含义如下
XSDD | 销售订单 | CGDD1 | 采购订单 | DJLX | 单据类型 |
XSDDMX | 销售订单明细 | CGDD2 | 采购订单明细 | YWRQ | 业务日期 |
DDLS | 订单流水 | LSBH | 流水编号 | FKKHMC | 付款客户名称 |
联查和左右连接
一、交叉连接
SELECT * FROM XSDD,XSDDMX WHERE XSDD_DDLS = '51'
XSDDMX一共4738行,XSDD一共2803行。上面查询的结果是4738行,实际上是DDLS为51的那一列同XSDDMX各列的乘积。
这种我们常用的连接方式称之为隐式交叉连接,另一种将两表间的逗号改为cross on的称之为显式,结果不变,表过不谈。
二、内连接
SELECT * FROM XSDD,XSDDMX WHERE XSDD_DDLS = XSDDMX_DDLS
同样的,也有显式的查询方法,这两种方法结果相同,都返回连接表中符合连接条件和查询条件的数据行:
SELECT * FROM XSDD inner join XSDDMX on XSDD_DDLS = XSDDMX_DDLS
三、外连接
左外连接:
SELECT XSDD_DDLS,CGDD1_LSBH FROM XSDD LEFT OUTER JOIN CGDD1 ON XSDD_DDLS = CGDD1_LSBH
XSDD共2803行,CGDD1共1587行,查询结果为2803行,可以看到查询结果包含了XSDD的所有流水编号,对于CGDD1中没有与之对应的部分,CGDD1的那一列则显示为NULL,结果如左图所示
右外连接:
SELECT XSDD_DDLS,CGDD1_LSBH FROM XSDD RIGHT OUTER JOIN CGDD1 ON XSDD_DDLS = CGDD1_LSBH
查询结果为1587行,查询结果为右图,其实这里可以简单的总结为,LEFT的右边有NULL,RIGHT的左边有NULL
关键字:Group by
这是个很常用的关键字,但是一开始由于对这个功能的不熟练导致了一些影响效率的问题发生。
它的应用其实还是集中在统计这样的功能上:
INSERT INTO XSFXHZ
SELECT XSFXMX_DJLX,'KH',XSFXMX_FKKH,XSFXMX_FKKHMC,'93',ISNULL(sum(XSFXMX_HSJE),0) ,
XSFXMX_ZGBH FROM XSFXMX
WHERE XSFXMX_YWRQ BETWEEN CONVERT(varchar(8),@year)+@s3sta AND
CONVERT(varchar(8),@year)+@s3end
GROUP BY XSFXMX_DJLX,XSFXMX_ZGBH,XSFXMX_FKKH,XSFXMX_FKKHMC
这里要查询的字段必须是一个固定的值,结合分组功能,我们可以总结出这些字段实际上可以分为三种类型,
- 第一种就是现成的字符串,比如这里的 ' KH ', ' 93 '
- 第二种是要分组查询的字段,例如 XSFXMX_DJLX
- 第三种就是聚合函数,它计算出这一分组中对应可计算列的计算结果值(很多情况下是sum或者max等等)
对于Group By的字段,它们用逗号连接起来,在查询结果中,这些字段中如果有一个不同,就会独立成一个分组,具体有几个不同的分组,这其实是一个排列组合的问题。
关键字:union 和union all
作用是合并两个查询的结果集,结果字段的名称以第一句查询的结果为准
SELECT CGDD1_LSBH FROM CGDD1
UNION
SELECT XSDD_DDLS FROM XSDD
这两者的区别就是,union all不剔除重复项
SELECT CGDD1_LSBH FROM CGDD1
UNION ALL
SELECT XSDD_DDLS FROM XSDD
函数
函数的内容,执行后是保存在数据库中的,所以执行以后在语句或者存储过程中直接调用就可以了。
在SQL中,函数分为下面三类:
- 内置函数:由系统提供,不能修改
- 标量函数:用户自定义的函数返回单个值
- 表值函数:返回表值
这次在项目中使用的是表值函数,是这么写的
if exists(select * from sys.objects where name = 'GetDwqxFun') drop function GetDwqxFun;
go
CREATE FUNCTION GetDwqxFun(@zgid nvarchar(30))
RETURNS @TempDwlb TABLE
(
id nvarchar(30)
)
AS
BEGIN
--
INSERT INTO @TempDwlb
SELECT @zgid,ZWZGZD_ZGMC FROM ZWZGZD
END
注意具体格式,在函数值记得给返回表赋值就可以啦。
其他常用内置函数
GetDate()
该函数的作用是获取当前的系统日期,使用效果如下
SELECT GETDATE() now
Convert()
该函数的作用是把日期转换为新数据类型的通用函数。可以和上面那个一起用,效果如下:
SELECT CONVERT(varchar(100), GETDATE(), 112)
经常被当做工具人来使用的SQL,单是用来实现工作中的业务功能时,操作完全是简单方便的,这或许也是我们在用不到它的时候就会快速遗忘的原因。