必须知道的SQL编写技巧,多条件查询不拼字符串的写法

  在做项目中,我们经常遇到复杂的查询方法,要根据用户的输入,判断某个参数是否合法,合法的话才能当作过滤条件,我们通常的做法是把查询SQL赋值给一个字符串变量,然后根据判断条件动态的拼接where条件进行查询。下面来简单说一下写SQL中遇到的问题和解决办法。

 

一、不确定字段名,而产生的SQL字符串拼接                                                                                                            

   比如,有个公司要做一个系统,要支持多语言,这个时候我们就要将语音信息存储在数据库中。然后,根据客户选择查询对应的语言字段,进行显示。下面我们来模拟这个场景,打开SQL Server,新建SysLanguage表,添加一些语言字段,如English,Chinese,French,这里French不知道是什么,就都设置为Empty。

 

建表的SQL和插入一些简单的数据,SQL如下:

CREATE TABLE SysLanguage(
    Id INT PRIMARY KEY,
    English NVARCHAR(100),
    Chinese NVARCHAR(100),
    French NVARCHAR(100)
)

INSERT INTO SysLanguage VALUES (1, Hello, 你好, Empty)
INSERT INTO SysLanguage VALUES (2, world, 世界, Empty)
INSERT INTO SysLanguage VALUES (3, Book, , Empty)
INSERT INTO SysLanguage VALUES (4, Open, 打开, Empty)
INSERT INTO SysLanguage VALUES (5, Save, 保存, Empty)
INSERT INTO SysLanguage VALUES (6, New, 新建, Empty)

 那么,考虑到系统性能,我们可能会使用存储过程,来减少网络通信量和提高响应速度。那么,在存储过程中,我们会传入一个参数,然后根据参数来查询指定的语言字段,那么SQL该怎么写呢?

这个时候,我们会想到在C#代码中,我们都是把SQL拼好,然后传给SQL Server执行,我们可以拼接字符串啊,然后有了下面的写法:

DECLARE @sql NVARCHAR(100);
DECLARE @para NVARCHAR(20);
SET @para = Chinese;
SET @sql = select ID,  + @para +  from SysLanguage
print @sql
exec(@sql)

当然这样写没有任何问题,但是当SQL很复杂的时候,很多的引号和字符转义,还有拼接字符串带来的缺少空格的问题,会令人崩溃!下面来说一种不拼接字符串的写法,代码如下:

DECLARE @para NVARCHAR(20);
SET @para = Chinese;
SELECT ID, 
CASE @para 
WHEN English THEN English
WHEN Chinese THEN Chinese 
ELSE
French
END
AS [Language]
FROM
SysLanguage

这里使用了CASE WHEN语句来进行判断,当判断条件太多时,写起来也很累,我们可以写一个Function来进行封装,当然,具体用那种方法,要看具体情况了。

这里说道了CASE WHEN,就不得不提另外一种查询了。先来看下需求:从学生表中查询出男生的人数,女生的人数和学生总人数

看到这个,我们首先想到了SUM函数,但是,然后呢?好像哪里不对?那么,到底该怎么写呢,当然还是用CASE WHEN,来看下代码吧

SELECT SUM(CASE StuSex WHEN 1 THEN 1 ELSE 0 END) AS Boys,
SUM(CASE StuSex WHEN 1 THEN 0 ELSE 1 END) AS Girls,  
SUM(StuID) AS Total
FROM Student

看到代码,感觉是不是很简单啊,就是当时没想到,呵呵!

 

二、不定查询条件产生的SQL字符串拼接                                                                                                                         

  习惯了C#代码,突然写起SQL来,感觉还是有点那么不顺手!下面来看看,当有多个查询条件,但是又不确定几个查询条件时,SQL该怎么写?我们经常遇到这种需求,页面上很多控件,要根据用户的输入进行查询,这个时候我们就要判断传入的控件的值是否符合指定条件,符合条件了才能被作为查询条件,否则就忽略这个条件。

拼接字符串的写法相信大家都经常用,这里就不写了,我们之接来看布拼接SQL怎么查询!

比如现在要查询指定条件的学生,用户可能查询所有,或查询男生,或查询指定Id的学生,或……等待,可能很多种情况。下面我们来写布拼接字符串的SQL,代码如下:

DECLARE @ID INT;
DECLARE @Sex BIT;
DECLARE @Name NVARCHAR(50);

SET @ID = -1;
SET @Sex = NULL;
SET @Name = Ja;

SELECT * FROM Student
WHERE (@ID = -1 OR StuID = @ID)
AND (@Sex IS NULL OR StuSex = @Sex)
AND (@Name IS NULL OR StuName LIKE % + @Name + %)

这里声明三个变量,当做传入的参数,传入的参数都有默认值,或者为NULL,我们在where添加里面用了OR添加判断,首先判断@ID = -1,如果条件成立了,OR就不会再判断OR后面的条件,到这里就(@ID = -1 OR StuID = @ID)就返回一个TRUE,相当于WHERE TRUE AND ……,所以就会忽略这个查询条件继续走下面的过滤判断。这样写,比拼接字符串好的多吧!

 

最后来说一下数据库中N字符的作用,说白了就是将后面的字符串内容,转换成Unicode编码,来写段SQL测试一下吧

DECLARE @a NVARCHAR(20)
DECLARE @b NVARCHAR(20)
SET @a = N中文
SET @b = 中文
IF(@a = @b)
PRINT 数值相等
ELSE
PRINT 不相等

输出结果如下:

在中文版的系统中,是相等的,在英文版的系统中,@b会显示乱码。大家可以测试一下!

 

 作者:雲霏霏

 博客地址:http://www.cnblogs.com/yunfeifei/

 声明:本博客原创文字只代表本人工作中在某一时间内总结的观点或结论,与本人所在单位没有直接利益关系。非商业,未授权,贴子请以现状保留,转载时必须保留此段声明,且在文章页面明显位置给出原文连接。

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。