mssql 部分语句转 mysql

一、 top —— limit
**mssql:**  select top 10 * from table
**mysql:** select * from table limit 10 ***注意：limit 是在 最后！最后！最后！比order by还后！***
1
2
二、 替换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);
1
2
3
4
5
6
7
8
9
10
11
12
13
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')
1
2
3
4
5
三、 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
);
1
2
3
4
5
6
7
8
9
SELECT * FROM temp;
DROP TABLE temp;
注意：mysql 三句一起执行的话一定要 加分号！加分号！加分号！这样就会前一句成功了才会执行下一句，否则。。。自己试一下^ . ^

五、 LEN —— LENGTH
**mssql:**  LEN(Note//字段)>10//长度

**mysql:** LENGTH(Note//字段)>10//长度
1
2
3
六、 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 )
1
2
注意： 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
1
2
3
4
5
6
7
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;
1
2
3
4
5
6
7
8
9
10
11
12
mysql中在 CREATE TEMPORARY TABLE 是不能直接用 UNION ALL 的，需要包含到子查询中，否则。。自己可以尝试一下 ^ . ^

九、 ISNULL —— IFNULL
**mssql：** ISNULL(e , 0) 
**mysql：** IFNULL(e , 0) 
1
2
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 ;
1
2
3
4
5
6
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;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
**十一、**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"/>
1
在此感谢 @老醋小花生

十二、 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('')
1
2

mysql:

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

FOR XML PATH 是将查询结果集以XML形式展现
GROUP_CONCAT把多条记录的某个字段拼成一条记录的字段值
————————————————
版权声明：本文为CSDN博主「小俊的碍着你了」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_47779182/article/details/121349393