IT教程: 必须知道的SQL编写技巧，多条件查询不拼字符串的写法

本邮件内容由第三方提供，如果您不想继续收到该邮件，可点此退订。

必须知道的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 Machine Learning Algorithms Study Notes(3)-- 3Learning Theory - Xuesong 阅读原文»

Machine Learning Algorithms Study Notes

高雪松

@雪松Cedro

Microsoft MVP

本系列文章是Andrew Ng 在斯坦福的机器学习课程 CS 229 的学习笔记。

Machine Learning Algorithms Study Notes 系列文章介绍

3 Learning Theory

3.1 Regularization and model selection

模型选择问题：对于一个学习问题，可以有多种模型选择。比如要拟合一组样本点，可以使用线性回归，也可以用多项式回归。那么使用哪种模型好呢（能够在偏差和方差之间达到平衡最优）？

还有一类参数选择问题：如果我们想使用带权值的回归模型，那么怎么选择权重w公式里的参数？

形式化定义：假设可选的模型集合是，比如我们想分类，那么SVM、logistic回归、神经网络等模型都包含在M中。

3.1.1 Cross validation

我们的第一个任务就是要从M中选择最好的模型。

假设训练集使用S来表示，如果我们想使用经验风险最小化来度量模型的好坏，那么我们可以这样来选择模型：

使用S来训练每一个，训练出参数后，也就可以得到假设函数。（比如，线性模型中得到后，也就得到了假设函数）
选择错误率最小的假设函数。

遗憾的是这个算法不可行，比如我们需要拟合一些样本点，使用高阶的多项式回归肯定比线性回归错误率要小，偏差小，但是方差却很大，会过度拟合。因此，我们改进算法如下：

从全部的训练数据S中随机选择70%的样例作为训练集，剩余的30%作为测试集。
在上训练每一个，得到假设函数。
在上测试每一个，得到相应的经验错误。
选择具有最小经验错误的作为最佳模型。

这种方法称为hold-out cross validation或者称为简单交叉验证。

由于测试集是和训练集中是两个世界的，因此我们可以认为这里的经验错误接近于泛化错误（generalization error）。这里测试集的比例一般占全部数据的1/4-1/3。30%是典型值。

还可以对模型作改进，当选出最佳的模型后，再在全部数据S上做一次训练，显然训练数据越多，模型参数越准确。

简单交叉验证方法的弱点在于得到的最佳模型是在70%的训练数据上选出来的，不代表在全部训练数据上是最佳的。还有当训练数据本来就很少时，再分出测试集后，训练数据就太少了。

我们对简单交叉验证方法再做一次改进，如下：

将全部训练集S分成k个不相交的子集，假设S中的训练样例个数为m，那么每一个子集有m/k个训练样例，相应的子集称作{}。
每次从模型集合M中拿出来一个，然后在训练子集中选择出k-1个{}（也就是每次只留下一个），使用这k-1个子集训练后，得到假设函数。最后使用剩下的一份作测试，得到经验错误。
由于我们每次留下一个（j从1到k），因此会得到k个经验错误，那么对于一个，它的经验错误是这k个经验错误的平均。
选出平均经验错误率最小的，然后使用全部的S再做一次训练，得到最后的。

这个方法称为k-fold cross validation（k-折叠交叉验证）。说白了，这个方法就是将简单交叉验证的测试集改为1/k，每个模型训练k次，测试k次，错误率为k次的平均。一般讲k取值为10。这样数据稀疏时基本上也能进行。显然，缺点就是训练和测试次数过多。

极端情况下，k可以取值为m，意味着每次留一个样例做测试，这个称为leave-one-out cross validation。

如果我们发明了一种新的学习模型或者算法，那么可以使用交叉验证来对模型进行评价。比如在NLP中，我们将训练集中分出一部分训练，一部分做测试。

参考文献

[1] Machine Learning Open Class by Andrew Ng in Stanford http://openclassroom.stanford.edu/MainFolder/CoursePage.php?course=MachineLearning

[2] Yu Zheng, Licia Capra, Ouri Wolfson, Hai Yang. Urban Computing: concepts, methodologies, and applications. ACM Transaction on Intelligent Systems and Technology. 5(3), 2014

[3] Jerry Lead http://www.cnblogs.com/jerrylead/

[4]《大数据-互联网大规模数据挖掘与分布式处理》 Anand Rajaraman，Jeffrey David Ullman著，王斌译

[5] UFLDL Tutorial http://deeplearning.stanford.edu/wiki/index.php/UFLDL_Tutorial

[6] Spark MLlib之朴素贝叶斯分类算法 http://selfup.cn/683.html

[7] MLlib - Dimensionality Reduction http://spark.apache.org/docs/latest/mllib-dimensionality-reduction.html

[8] 机器学习中的数学(5)-强大的矩阵奇异值分解(SVD)及其应用 http://www.cnblogs.com/LeftNotEasy/archive/2011/01/19/svd-and-applications.html