由于工作原因，程序需要适配两种类型的数据，所以把一些sql语句写法对比总结一下
本篇及后续随笔都将使用一个极其简单的场景（课室，学生，1对多）来演示，请先创建表
mysql
CREATE TABLE IF NOT EXISTS `class` ( `Id` int(11) NOT NULL, `Name` varchar(50) NOT NULL DEFAULT ‘0‘, PRIMARY KEY (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `student` ( `Id` int(11) NOT NULL, `ClassId` int(11) NOT NULL, `Name` varchar(50) NOT NULL DEFAULT ‘0‘, PRIMARY KEY (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
mssql
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Class]( [Id] [int] NOT NULL, [Name] [nvarchar](10) NULL, CONSTRAINT [PK_Class] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Student]( [Id] [int] NOT NULL, [ClassId] [int] NULL, [Name] [nvarchar](10) NULL, CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
数据的插入语句mysql和mssql一样
INSERT Class (Id, Name) VALUES (1, ‘1年3班‘); INSERT Class (Id, Name) VALUES (2, ‘1年1班‘); INSERT Class (Id, Name) VALUES (3, ‘1年4班‘); INSERT Class (Id, Name) VALUES (4, ‘1年2班‘); INSERT Student (Id, ClassId, Name) VALUES (1, 3, ‘小a‘); INSERT Student (Id, ClassId, Name) VALUES (2, 1, ‘小b‘); INSERT Student (Id, ClassId, Name) VALUES (3, 2, ‘小c‘); INSERT Student (Id, ClassId, Name) VALUES (4, 1, ‘小d‘); INSERT Student (Id, ClassId, Name) VALUES (5, 2, ‘小e‘); INSERT Student (Id, ClassId, Name) VALUES (6, 3, ‘小f‘); INSERT Student (Id, ClassId, Name) VALUES (7, 2, ‘小h‘); INSERT Student (Id, ClassId, Name) VALUES (8, 3, ‘小i‘); INSERT Student (Id, ClassId, Name) VALUES (9, 4, ‘小j‘); INSERT Student (Id, ClassId, Name) VALUES (10, 3, ‘小k‘); INSERT Student (Id, ClassId, Name) VALUES (11, 3, ‘小l‘); INSERT Student (Id, ClassId, Name) VALUES (12, 3, ‘小m‘); INSERT Student (Id, ClassId, Name) VALUES (13, 4, ‘小n‘); INSERT Student (Id, ClassId, Name) VALUES (14, 1, ‘小o‘); INSERT Student (Id, ClassId, Name) VALUES (15, 4, ‘小p‘); INSERT Student (Id, ClassId, Name) VALUES (16, 2, ‘小q‘);
p.s. mysql和mssql的表名不区分大小写，但是避免混淆，演示时mssql会首字母大写
一、分页写法
假设一页5条数据，取第1页
1.1 主流写法
mysql
select * from student limit 0, 5
mssql（分页前必须要order by 一下，否则报错，mysql没有这限制）
select * from Student order by Id offset(5 * 0) rows fetch next 5 rows only
1.2 用行号来分页
mysql：mysql通过自定义变量使用行号
SELECT * from (SELECT @row:=@row+1 AS row, student.* FROM student, (select @row := 0) r ORDER BY student.Id) student WHERE row > (0 * 5) AND row <= ((0+1) * 5)
mssql：通过系统函数来使用行号
select * from (select ROW_NUMBER() over(order by Id) as row, * from Student) as Student where row > (0 * 5) and row <= ((0+1) * 5)
好像还有between and 的写法也行
二、top
取第一条数据
mysql没有top的写法，只能用limit 0, 1
select * from student limit 0, 1
mssql则有top
select top 1 * from Student
p.s. 鉴于mssql有top可以用，所以mssql可以改上面的行号分页方式，去掉row <= ((0+1) * 5)条件，使用top 5
三、插入数据时如果有条件，且数据源不来自旧表，mysql需要加上from dual而mssql则不用
给1年1班新增一个学生，但不要重复新增
mysql
INSERT INTO student(Id, ClassId, Name) SELECT 17, 1, ‘小r‘ FROM dual WHERE NOT EXISTS(SELECT 1 FROM student WHERE ClassId = 1 AND NAME = ‘小r‘)
mssql
INSERT INTO student(Id, ClassId, Name) SELECT 17, 1, ‘小r‘ WHERE NOT EXISTS(SELECT 1 FROM student WHERE ClassId = 1 AND NAME = ‘小r‘)
四、判断结果是否为null，若为null则用别的代替
mysql：IFNULL
mssql：ISNULL
五、mysql不支持in查询里面分页，但是mssql支持
SELECT * FROM student WHERE Id IN (SELECT Id FROM class LIMIT 0, 1)
mysql会报错“This version of MariaDB doesn‘t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery‘”
六、mysql和mssql都有distinct和group by来去重，但是mssql的order by 后面的字段必须是distinct或group by中出现的字段，而mysql无此限制
原因可能是mysql是先order by再distinct，而mssql则是先distinct再order by
SELECT DISTINCT class.* FROM class JOIN student ON class.Id = student.ClassId ORDER BY student.Name
SELECT class.* FROM class JOIN student ON class.Id = student.ClassId GROUP BY class.Id, class.Name ORDER BY student.Name
如上语句mysql正确执行，而mssql会报错“ORDER BY 子句中的列 "student.Name" 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。”
mssql可以用以下语句实现同等效果
SELECT class.* FROM class JOIN student ON class.Id = student.ClassId GROUP BY class.Id, class.Name ORDER BY (select top 1 Name from Student where ClassId = class.Id)
注意，用distinct去重时（如下语句），连这样改排序条件都不行，会报错“如果指定了 SELECT DISTINCT，那么 ORDER BY 子句中的项就必须出现在选择列表中。”[mssql真坑]
SELECT DISTINCT class.* FROM class JOIN student ON class.Id = student.ClassId ORDER BY (select top 1 Name from Student where ClassId = class.Id)
这里总结为：多表查询只查主表，排序依据用子表的问题，mysql可以直接写，而mssql就必须转换一下，且用distinct还无法实现需求
*（代码场景）通常排序依据都是外部传入的，查询方法本身不能知道排序依据会不会有子表的，有多少个，这里若用mssql作为数据仓储就需要多这一步额外的判断，程序相对就复杂了。[mssql真坑]
七、多表查询，主表分页
查询学生名字排序，班级及班级所有学生的分页，假设分页大小是2，取第2页
查询出来的主表的数据应该是2条，但由于join了
mysql
SELECT class.*, student.* FROM ( SELECT DISTINCT class.* FROM class JOIN student ON class.Id = student.ClassId ORDER BY student.Name LIMIT 2, 2 ) AS class JOIN student ON class.Id = student.ClassId
mssql
select Class.*, Student.* from ( SELECT Class.* FROM Class JOIN Student ON Class.Id = Student.ClassId GROUP BY Class.Id, Class.Name ORDER BY (select top 1 Name from Student where ClassId = Class.Id) offset(1 * 2) rows fetch next 2 rows only ) as Class join Student on Class.Id = Student.ClassId ORDER BY (select top 1 Name from Student where ClassId = Class.Id)
结果为
注意mssql外面那次不排序，结果就是这样
这是默认排序的问题，建议是都给加上外面那层排序
八、多表更新
mysql的多表更新要把查询用到的表写在set前面，更新内容可以是任意一个表的字段
UPDATE class JOIN student ON class.Id = student.ClassId SET student.NAME = ‘小a‘, class.Name = ‘1年4班‘ WHERE student.name = ‘小a‘
mssql的多表更新则是只能更新其中一个表，查询用到的表写在set后面，还要有from
UPDATE Student SET NAME = ‘小a‘ from Class JOIN Student ON Class.Id = Student.ClassId WHERE Student.Name = ‘小a‘
总结：mysql在更新方面比mssql要灵活
p.s. 此随笔只要笔者踩到新坑就会更新
原文：https://www.cnblogs.com/ogurayui/p/12486699.html
如果您也喜欢它,动动您的小指点个赞吧
602392714
清零编程群