MySQL与MsSQL应用中SQL的对比

MySQL: insert into SystemSet (id,num) SELECT 10063, 0 FROM dual where not EXISTS (SELECT ID from SystemSet where id=10063)
MsSQL: if (select id from SystemSet where ID=10063) is null insert SystemSet (id,num) values(10063,0)

存在更新，不存在插入，注意更新语句用ON DUPLICATE KEY连接，且后面不能有Where
MySQL: insert into SystemSet(ID,Num) SELECT 9999, 1 FROM dual where not EXISTS (SELECT ID from SystemSet where id=9999) 
ON DUPLICATE KEY UPDATE num=0
MsSQL: if not EXISTS (SELECT id FROM SystemSet WHERE id=9999) insert SystemSet(ID,Num) values(9999,1) 
else update [SystemSet] set num=1 where ID=9999

MySQL: CREATE TABLE IF not EXISTS `TempSpace`(`ControlServerNumber` int(11) NOT NULL AUTO_INCREMENT,
`ControlServerName` varchar(250) DEFAULT NULL,`NodeID` tinyint(4) NOT NULL,`PointID` tinyint(4) NOT NULL,
`Name` varchar(50) DEFAULT NULL,`X` float DEFAULT 0.5, `Y` float DEFAULT 0.5,`TypeID` int(11) DEFAULT NULL,
`ParkName` varchar(250) DEFAULT NULL,`ParkID` int(11) DEFAULT 1,`Angle` int(11) DEFAULT 0,
PRIMARY KEY (`ControlServerNumber`,`NodeID`,`PointID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8
MSSQL: IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TempSpace]') AND type in (N'U')) 
BEGIN 
CREATE TABLE [dbo].[TempSpace]([ControlServerNumber] [int] NOT NULL,
[ControlServerName] [nvarchar](250) NULL,[NodeID] [tinyint] NOT NULL,[PointID] [tinyint] NOT NULL,
[Name] [nvarchar](50) NULL,[X] [float] NULL,[Y] [float] NULL,[TypeID] [int] NULL,
[ParkName] [nvarchar](250) NULL,[ParkID] [int] NULL,
CONSTRAINT [PK_TempSpace] PRIMARY KEY CLUSTERED([ControlServerNumber] ASC,[NodeID] ASC,
[PointID] ASC) ON [PRIMARY]) ON [PRIMARY] 
END

MySQL: delete from SpaceDownloadParam where SpaceID in (select * from (select s.id from SpaceDownloadParam as ls, 
Space as s where s.UsedFlag=1 and s.ID=ls.SpaceID and s.ControlServerNumber='33') as tmp)
MsSQL: delete from SpaceDownloadParam where SpaceID in (select s.id from SpaceDownloadParam ls, 
Space as s where s.UsedFlag=1 and s.ID=ls.SpaceID and s.ControlServerNumber='33')

concat用来连接字符串

MySQL: select TypeID,ControlServerNumber,concat(Memo,'  ( ', ControlServerNumber,' )') as name 
from ControlServer where usedflag=1 and TypeID in(10,11) order by ControlServerNumber;
MsSQL: select TypeID,ControlServerNumber,Memo+'  ( '+CAST(ControlServerNumber as nvarchar)+' ) as name 
from ControlServer where usedflag=1 and TypeID in(10,11) order by ControlServerNumber

MySQL: 二进制，同带binary前缀的效果 : BINARY    
字符型，可带参数 : CHAR()     
日期 : DATE     
时间: TIME     
日期时间型 : DATETIME     
浮点数 : DECIMAL      
整数 : SIGNED     
无符号整数 : UNSIGNED 
select cast( 1.5*s.RateSet+100 as SIGNED )  as RateSet ,s.x,s.y,s.ParkID,s.ID,
case c.TypeID when 10 then concat(s.NodeID,'-',s.PointID) 
else concat(s.NodeID ,'-', (s.PointID)/2 , case(s.PointID+1) %2 when 0 then 'A'else 'B' end) end as  Dizhi,s.ControlServerNumber,s.Name,s.UsedFlag,
case s.UsedFlag when 0 then '停用' when 1 then '启用' end as state, p.name as pname ,
case s.TypeID when 0 then '临时' when 1 then '固定' when 2 then '预约' end as type 
from space s  
left join park p on p.ID=s.ParkID 
left join ControlServer c on c.ControlServerNumber=s.ControlServerNumber  
where s.UsedFlag in (0,1) order by pname,Name 
MsSQL: select cast( 1.5*s.RateSet+100 as int )  as RateSet ,s.x,s.y,s.ParkID,s.ID,
case c.TypeID when 10 then cast(s.NodeID as nvarchar)+'-'+cast((s.PointID) as nvarchar) 
else cast(s.NodeID as nvarchar)+'-'+ cast((s.PointID+1)/2 as nvarchar)+
case(s.PointID+1) % 2 when 0 then 'A'else 'B' end end as Dizhi,
cast(s.ControlServerNumber as nvarchar) as ControlServerNumber,s.Name,s.UsedFlag,
case s.UsedFlag when 0 then '停用' when 1 then '启用' end as state, p.name as pname ,
case s.TypeID when 0 then '临时' when 1 then '固定' when 2 then '预约' end as type 
from space s