MySQL To MSSQL

重設 auto increment 值

dbcc checkident('MEMBERS0', RESEED, 0);

LIMIT 2, 3

SELECT * FROM MEMBERS0 LIMIT 2, 3;

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY ID) RN FROM I_MARRY_V3.dbo.MEMBERS0
) T WHERE T.RN BETWEEN 3 AND 5;

GROUP BY ORDER LIMIT

SELECT GENDER, COUNT(*) AS TOTAL FROM MEMBERS0 GROUP BY GENDER ORDER BY GENDER ASC LIMIT 0, 1;

SELECT T.GENDER, T.TOTAL, T.RN FROM (
    SELECT GENDER, COUNT(*) AS TOTAL, ROW_NUMBER() OVER (ORDER BY GENDER DESC)
    RN FROM I_MARRY_V3.dbo.MEMBERS0 GROUP BY GENDER
) T WHERE T.RN BETWEEN 1 AND 1;

INSERT IGNORE INTO IGNORE

INSERT IGNORE INTO TEMP...

IF NOT EXISTS (
    SELECT * FROM TEMP WHERE = ...
)
BEGIN
    INSERT INTO TEMP (...) VALUES (...)
END

INSERT INTO... ON DUPLICATE KEY UPDATE

INSERT INTO TEMP (MEMBERS_ID, TYPE, COMMENT) VALUES (3, 1, 'None') ON DUPLICATE KEY UPDATE MEMBERS_ID = 3, TYPE = 2

UPDATE TEMP SET MEMBERS_ID = 3, TYPE = 2 WHERE MEMBERS_ID = 3 AND TYPE = 1
IF @@ROWCOUNT = 0
INSERT INTO TEMP (MEMBERS_ID, TYPE, COMMENT) values(3, 1, 'None');

仿 MySQL 的 MGR_MyISAM 功能

CREATE TRIGGER [dbo].[MEMBERS0_AIUD] ON [dbo].[MEMBERS0] AFTER UPDATE, INSERT, DELETE
AS
BEGIN
    DECLARE @ID int
    DECLARE @FULL_NAME nvarchar(20)
    DECLARE @GENDER nchar(1)
    DECLARE @DEPARTMENT tinyint
    DECLARE @CREATE_DT nchar(19)
  
    IF EXISTS (SELECT 1 FROM INSERTED) AND NOT EXISTS (SELECT 1 FROM DELETED)
        BEGIN
            SELECT
                @ID = ID, @FULL_NAME = FULL_NAME, @GENDER = GENDER,
                @DEPARTMENT = DEPARTMENT, @CREATE_DT = CREATE_DT
                FROM INSERTED
            INSERT INTO MEMBERS_MERGE (ID, FULL_NAME, GENDER, DEPARTMENT,
           CREATE_DT) VALUES (@ID, @FULL_NAME, @GENDER, @DEPARTMENT,
           @CREATE_DT)
        END
    IF EXISTS (SELECT 1 FROM INSERTED) AND EXISTS (SELECT 1 FROM DELETED)
        BEGIN
            SELECT
                @ID = ID, @FULL_NAME = FULL_NAME, @GENDER = GENDER,
                @DEPARTMENT = DEPARTMENT, @CREATE_DT = CREATE_DT
                FROM INSERTED
            UPDATE MEMBERS_MERGE SET
                ID = @ID, FULL_NAME = @FULL_NAME, GENDER = @GENDER,
                DEPARTMENT = @DEPARTMENT, CREATE_DT = @CREATE_DT
                WHERE ID = (SELECT ID FROM DELETED)
        END
    IF NOT EXISTS (SELECT 1 FROM INSERTED) AND EXISTS (SELECT 1 FROM DELETED)
        BEGIN
            DELETE FROM MEMBERS_MERGE WHERE ID = (SELECT ID FROM DELETED)
        END
END