时间戳

首页 > 解决方案 > mysql限制不能针对1个id工作多个记录

mysql - mysql限制不能针对1个id工作多个记录

问题描述

Mysql 过程针对 1 个 id 给出三个记录。我试图仅针对 1 个 id 获取 1 条记录。对 1 个 ID 仅访问 1 条记录的限制在哪里

创建 DEFINER= xxxx@ xxxx.%PROCEDURE xxxx(

    IN pCompanyId BIGINT(20)
        )
    BEGIN
        SELECT 
         `zp`.`PlaceId`,
         `zp`.`PlaceName`,
         `zp`.`PlaceCategoryCode`,
         `zp`.`Description`,
         `zp`.`CompanyId_FK`,
         `zp`.`OwnerCompanyId_FK`,
         `zp`.`IsDeleted`,
         `zp`.`IsArchived`,
         `zp`.`CreatedDate`,
         `zp`.`ModifiedDate`,
         `zp`.`CreatedBy_FK`,
         `zp`.`ModifiedBy_FK`,
         (SELECT COUNT(`ApplianceId`) FROM `ZThinQ_Appliance` WHERE `ApplianceActionCode` = 1 AND `PlaceId_FK` = `PlaceId`)  AS activeDevices ,
         (SELECT COUNT(`ApplianceId`) FROM `ZThinQ_Appliance` where  `PlaceId_FK` = `PlaceId` ) AS totalDevices

        FROM `ZThinQ_Place` AS `zp` 
        JOIN `ZThinQ_Appliance` as `za`

        #ON `ZThinQ_Appliance`
        #ON `PlaceId` = `PlaceId_FK` 

        Where `zp`.`CompanyId_FK`=pCompanyId;


    END

标签： mysqldatabasestored-procedureslimit

解决方案

如果你想要一行，你可以在 yoru 查询中添加限制 1

 SELECT 
     `zp`.`PlaceId`,
     `zp`.`PlaceName`,
     `zp`.`PlaceCategoryCode`,
     `zp`.`Description`,
     `zp`.`CompanyId_FK`,
     `zp`.`OwnerCompanyId_FK`,
     `zp`.`IsDeleted`,
     `zp`.`IsArchived`,
     `zp`.`CreatedDate`,
     `zp`.`ModifiedDate`,
     `zp`.`CreatedBy_FK`,
     `zp`.`ModifiedBy_FK`,
     (SELECT COUNT(`ApplianceId`) FROM `ZThinQ_Appliance` WHERE `ApplianceActionCode` = 1 AND `PlaceId_FK` = `PlaceId`)  AS activeDevices ,
     (SELECT COUNT(`ApplianceId`) FROM `ZThinQ_Appliance` where  `PlaceId_FK` = `PlaceId` ) AS totalDevices

    FROM `ZThinQ_Place` AS `zp` 
    JOIN `ZThinQ_Appliance` as `za`

    #ON `ZThinQ_Appliance`
    #ON `PlaceId` = `PlaceId_FK` 

    Where `zp`.`CompanyId_FK`=pCompanyId
    LIMIT 1

推荐阅读