mssql 部分语句转 mysql

一、 top —— limit

**mssql:**  select top 10 * from table
**mysql:** select * from table limit 10 ***注意：limit 是在 最后！最后！最后！比order by还后！***

二、 替换DECLARE （只是本人在项目中遇到的，可以忽略）

**mssql：** DECLARE @ff VARCHAR(MAX),@str VARCHAR(MAX) 
 SELECT @ff=(''''+REPLACE(rm.MenuID,',',''',''')+'''') FROM H rm 
 LEFT JOIN U u ON rm.RoleID=u.User_LevelID 
 WHERE u.UserID='001' 
 SET @str='select *  from M where MenuID IN ('+@ff+') ORDER BY CAST(Mark AS INT)' 
 exec (@str)

**mysql：** 
 SELECT * FROM M
 WHERE FIND_IN_SET( MenuID ,(SELECT rm.MenuID FROM M rm 
 LEFT JOIN HR_User u ON rm.RoleID=u.User_LevelID 
 WHERE u.UserID='admin') ) 
 ORDER BY CAST(Mark AS SIGNED);

FIND_IN_SET():
list是常量，则可以直接用IN， 否则要用find_in_set()函数。

也就是这两个sql是查询的效果是相同的：

SELECT id,name,list from tb_test WHERE FIND_IN_SET('daodao',list);

SELECT * from C_PURCHASINGMASTERDATA where FIND_IN_SET(EKGRP,'C54,C02,C14,C60,C06,C61,C53,C51,C12,C08,C03,C07')

SELECT * from C_PURCHASINGMASTERDATA where EKGRP in ('C54','C02','C14','C60','C06','C61','C53','C51','C12','C08','C03','C07')

三、 in 子查询用法

mssql: select * from S where ID not in (select id from S where) （在mssql中这样使用没问题但是到mysql中就需要包起来）

mysql: SELECT * FROM S WHERE ID NOT IN(SELECT S.ID FROM (SELECT * FROM S WHERE 1=1 ) AS S )

四、 临时表创建的不同

 **mssql:** 
 	SELECT * INTO #temp FROM S WHERE 1=1 
 	SELECT * FROM #temp
 	DROP TABLE #temp

**mysql:** 
CREATE TEMPORARY TABLE temp(
     SELECT * FROM S WHERE 1=1
);

SELECT * FROM temp;
DROP TABLE temp;
注意：mysql 三句一起执行的话一定要 加分号！加分号！加分号！这样就会前一句成功了才会执行下一句，否则。。。自己试一下^ . ^

五、 LEN —— LENGTH

**mssql:**  LEN(Note//字段)>10//长度

**mysql:** LENGTH(Note//字段)>10//长度

六、 Cast（）与 CONVERT（）

mssql	mysql
int	SIGNED
varchar	CHAR(N)

Cast（）与 CONVERT（） 用法不同 值类型一样 CAST(xxx AS 类型), CONVERT(xxx,类型)

七、 IDENTITY()—— max(id)

 **mssql:** select IDENTITY(INT,1,1) INTO #temp1 FROM  table 
 **mysql:** CREATE TEMPORARY TABLE temp1(SELECT MAX(id) from table )

注意： mysql 中的max(id)只限在创建临时表中或者同时只改单个表的时候用，同时多表插入请使用LAST_INSERT_ID()

八、 UNION ALL

mssql:

SELECT * INTO #temp FROM Q
UNION ALL 
SELECT * FROM QX
UNION ALL
SELECT * FROM QF 
SELECT * FROM #temp";
DROP TABLE #temp

mysql:

CREATE TEMPORARY TABLE temp AS(
SELECT * FROM
 ((SELECT * FROM Q)
  UNION ALL 
  (SELECT * FROM QX )
  UNION ALL 
  (SELECT * FROM QF))
  AS temp
);

 SELECT * ROM temp;
 DROP TABLE temp;

mysql中在 CREATE TEMPORARY TABLE 是不能直接用 UNION ALL 的，需要包含到子查询中，否则。。自己可以尝试一下 ^ . ^

九、 ISNULL —— IFNULL

**mssql：** ISNULL(e , 0) 
**mysql：** IFNULL(e , 0) 

e代表要替换的值，当e为null时就会被0代替，否则还是其本身

mysql扩展 ： 1.isnull(exper) 判断exper是否为空，是则返回1，否则返回0
2.nullif(exper1,exper2)如果expr1= expr2 成立，那么返回值为NULL，否则返回值为 expr1

十、 WITH AS 和 OVER (ORDER BY ) 转

mssql:

WITH questions AS
(
SELECT tx.ID,tx.QID, ROW_NUMBER() OVER (ORDER BY tx.ID) AS RowNumber
FROM QuestionTX tx
) 
SELECT * FROM questions ;

mysql:

CREATE TEMPORARY TABLE questions AS
(
	SELECT * FROM (
	SELECT ID, QID,RowNumber 
	FROM (
		SELECT Q.ID, Q.QID @rownum := @rownum + 1,
				IF (
				@pjan = Q.ID AND @grade = Q.QID,
				@rank :=@rank + 1 ,@rank := @rank + 1 //（我这里为了满足项目需求才写成这样的正常是@rank :=@rank + 1 ,@rank := 1）
				) AS RowNumber,
				# 定义了两个个参数 Q.ID 和 Q.QID，通过if函数判断，
				# 当这两个参数与查询出来的数据一样的时候，我们自增，不然那就从1开始重新计数
				@pjan := Q.ID,
				@grade := Q.QID
			FROM
			(
				SELECT tx.ID, tx.QID
				FROM QuestionTX tx
				ORDER BY QID,ID DESC
			) Q,
			(
			SELECT @rownum := 0, @grade := NULL ,@pjan := NULL ,@rank := 0
			) a
		) result
	) AS questions
);
SELECT * FROM questions ;
DROP TABLE questions;

**十一、**Fatal error encountered during command execution.

我中途遇到了这个Fatal error encountered during command execution.
查了以后发现是：因为在sql语句中使用了自定义参数：类似这样 @rank :=@rank + 1
所以……
需要在连接的URL内加上Allow User Variables=True，完美解决

<add key="HRconnectionstring" value="Server= .;DataBase=.;Persist Security Info=True;UID=s;PWD=123;port=3366;Allow User Variables=True"/>

在此感谢 @老醋小花生

十二、 CONCAT 字符拼接

例子： CONCAT(‘T’ ,id)

十三、 GETDATE()——NOW()

两个都是获取时间的，mssql的GETDATE（）可以直接在mysql中用NOW（）获得

十四、 FOR XML PATH(’’) ——GROUP_CONCAT（）

mssql:

SELECT  ''''+QID+''''+',' FROM Q 
ORDER BY 排序字段 FOR XML PATH('')

mysql:

SELECT  CONCAT(CONCAT('''',GROUP_CONCAT(DISTINCT QID SEPARATOR ''',''')),'''')   FROM Q
ORDER BY 排序字段

FOR XML PATH 是将查询结果集以XML形式展现
GROUP_CONCAT把多条记录的某个字段拼成一条记录的字段值