mysql限制无法针对1个id使用多条记录

rseugnpd 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(365)

mysql过程根据1个id给出了3条记录。我正试图根据1个id只获取1条记录。在哪里设置一个针对1个id只访问1条记录的限制
创建定义者= x @ x.% 程序 x (

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

mysql Database stored-procedures Limit

来源：https://stackoverflow.com/questions/52148904/mysql-limit-not-working-multiple-record-against-1-id

2条答案

按热度按时间

r8uurelv1#

如果您想要一行，您可以添加限制1到yoru查询

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

赞(0）回复(0）举报 2021-06-20

laawzig22#

我有一个id的多个记录。所以我在我的过程中使用select distinct。就是这样

SELECT distinct
 `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

赞(0）回复(0）举报 2021-06-20

我来回答

mysql限制无法针对1个id使用多条记录

2条答案

相关问题

热门标签

最新问答