时间戳

首页 > 解决方案 > SELECT 语句返回行，但具有相同 WHERE 的 SELECT COUNT(*) 不返回 (AWS AuroraDB)

sql - SELECT 语句返回行，但具有相同 WHERE 的 SELECT COUNT(*) 不返回 (AWS AuroraDB)

问题描述

这是与 User 和 GpsPosition 相关的数据库架构：

CREATE TABLE GpsPosition 
(
    altitudeInMeters SMALLINT NOT NULL,
    dateCreated      BIGINT NOT NULL,
    dateRegistered   BIGINT NOT NULL,
    deviceId         BINARY(16) NOT NULL,
    emergencyId      BINARY(16) NULL,
    gpsFix           SMALLINT NOT NULL,
    heading          SMALLINT NOT NULL,
    horizontalUncertaintyInMeters SMALLINT NOT NULL,
    id               BINARY(16) NOT NULL,
    latestForDevice  BOOLEAN NOT NULL,
    latestForUser    BOOLEAN NOT NULL,
    latitude         DOUBLE PRECISION NOT NULL,
    longitude        DOUBLE PRECISION NOT NULL,
    numSatellites    SMALLINT NOT NULL,
    speedInKmph      SMALLINT NOT NULL,
    stale            BOOLEAN NOT NULL,
    userId           BINARY(16) NULL,
    verticalUncertaintyInMeters SMALLINT NOT NULL,

    PRIMARY KEY (id)
);

ALTER TABLE GpsPosition 
    ADD CONSTRAINT GpsPosition_deviceId_fkey 
        FOREIGN KEY (deviceId) REFERENCES Device(id) 
            ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE GpsPosition 
    ADD CONSTRAINT GpsPosition_emergencyId_fkey 
        FOREIGN KEY (emergencyId) REFERENCES Emergency(id) 
            ON UPDATE CASCADE ON DELETE SET NULL;

ALTER TABLE GpsPosition 
    ADD CONSTRAINT GpsPosition_userId_fkey 
        FOREIGN KEY (userId) REFERENCES User(id) 
            ON UPDATE CASCADE ON DELETE SET NULL;

ALTER TABLE GpsPosition 
    ADD CONSTRAINT deviceId_dateCreated_must_be_unique 
        UNIQUE (deviceId, dateCreated);

CREATE INDEX i2915035553 ON GpsPosition (deviceId);
CREATE INDEX deviceId_latestForDevice_is_non_unique ON GpsPosition (deviceId, latestForDevice);
CREATE INDEX i3210815937 ON GpsPosition (emergencyId);
CREATE INDEX i1689669068 ON GpsPosition (userId);
CREATE INDEX userId_latestForUser_is_non_unique ON GpsPosition (userId, latestForUser);

此语句返回大量行：

select *
from GpsPosition
where exists (select *
              from User
              where User.id = GpsPosition.userId and
                    User.id = UNHEX( '3f4163aab2ac46d6ad15164222aca89e' )
             );

此语句返回值为 0 的单行（结果）：

select count(*)
from GpsPosition
where exists (select *
              from User
              where User.id = GpsPosition.userId and
                    User.id = UNHEX( '3f4163aab2ac46d6ad15164222aca89e' )
             );

我不明白 SELECT * 语句如何返回许多结果，而 SELECT COUNT(*) 语句返回 0。它们都具有相同的 WHERE 语句。

标签： sqlamazon-aurora

解决方案

相关子查询用于逐行处理。每个子查询对外部查询的每一行执行一次。这个图在此处输入图像描述 很有帮助 因为外部查询结果只有一行 GpsPosition 中的行数，所以结果为 0。

我认为这应该有效：

select count(*) from (
    select *
    from GpsPosition
    where exists (select *
                  from User
                  where User.id = GpsPosition.userId and
                        User.id = UNHEX( '3f4163aab2ac46d6ad15164222aca89e' )
                 )
) view1

推荐阅读