mysql - 为什么 mysql 中的这个查询适用于表但不适用于该表的 View ？

Question

请看图片。

以下查询在对表“participants”执行时效果很好:

SELECT 
    *
FROM
    (SELECT 
        p.*, @curRow:=@curRow + 1 AS position
    FROM
        (SELECT @curRow:=0) r, 
        participants p
    WHERE
        (p.isVolunteer = 0
            OR p.isVolunteer IS NULL)
    GROUP BY p.id
    ORDER BY p.lastRegistrationDate DESC
    LIMIT 0 , 25) AS p
        INNER JOIN
    participants_registrations pr ON p.id = pr.participantId
        INNER JOIN
    registrations r ON pr.registrationId = r.id
ORDER BY p.position ASC , r.createdOn DESC

但是，当我用 View “vparticipants”替换“participants”时，我无法得到相同的结果，这应该是相同的。

问题是结果的顺序不一样。

使用表格，我得到按 createdOn 列(日期)和 position 列排序的行，值从 1 到 25，但使用 View ' vparticipants' 我得到从 6 到 30 的列 'position'，列 createdOn 的顺序是升序排列。我需要与表格相同的结果。

View 很简单，就在这里:

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `vparticipants` AS
    SELECT 
        `participants`.`id` AS `id`,
        `participants`.`identificationNumber` AS `identificationNumber`,
        `participants`.`firstName` AS `firstName`,
        `participants`.`lastName` AS `lastName`,
        `participants`.`email` AS `email`,
        `participants`.`cellphone` AS `cellphone`,
        `participants`.`city` AS `city`,
        `participants`.`gender` AS `gender`,
        `participants`.`shirtSize` AS `shirtSize`,
        `participants`.`bloodType` AS `bloodType`,
        `participants`.`emergencyContactName` AS `emergencyContactName`,
        `participants`.`emergencyContactPhone` AS `emergencyContactPhone`,
        `participants`.`eps` AS `eps`,
        `participants`.`birthday` AS `birthday`,
        `participants`.`memberOfGroupId` AS `memberOfGroupId`,
        `participants`.`memberOfGroupName` AS `memberOfGroupName`,
        `participants`.`lastRegistrationDate` AS `lastRegistrationDate`,
        `participants`.`isVolunteer` AS `isVolunteer`,
        `agreements_signatures`.`signatureRequest` AS `signatureRequest`,
        `agreements_signatures`.`signature` AS `signature`,
        `agreements_signatures`.`manualSignature` AS `manualSignature`,
        `agreements_signatures`.`fromWhereWasSigned` AS `fromWhereWasSigned`,
        `agreements_signatures`.`responsibleName` AS `responsibleName`,
        `agreements_signatures`.`responsibleIdentificationNumber` AS `responsibleIdentificationNumber`,
        `agreements_signatures`.`expeditionPlace` AS `expeditionPlace`,
        `agreements_signatures`.`signedIn` AS `signedIn`
    FROM
        ((`participants`
        LEFT JOIN `participants_registrations` ON ((`participants_registrations`.`participantId` = `participants`.`id`)))
        LEFT JOIN `agreements_signatures` ON ((`agreements_signatures`.`id` = `participants_registrations`.`agreementSignatureId`)))

感谢任何帮助。

正确的结果图像:

错误的结果图像( View ):

Answer 1

这是因为 ORDER BY 不适用于 View ，参见 this链接，它是 SQL SERVER 在 View 中的限制。最好不使用 View 来编写此查询