MySQL - 列出多个属性

Question

我的数据库中有两个表:AppropriateFor 和 AquariumModel，如下所示。它们共享三个键 - AquariumType、Salinity 和 Volume。我试图找到所有被认为适合不止一种物种的水族馆模型。尝试正确计算 AppropriateFor 中的主元组数量，然后将其与 AquariumModel 表连接，我感到震惊。

AppropriateFor 表有四列:物种名称、水族馆类型、盐度、体积:

RED-BELLIED PACU                AGGRESSIVE  FRESHWATER  300
GOLDEN MORAY EEL            AGGRESSIVE  MARINE      25000
NURSE SHARK                 AGGRESSIVE  MARINE      25000
COMMON TOADFISH             COMMUNITY   BRACKISH    150
DRAGON GOBY                 COMMUNITY   BRACKISH        150
BRONZE CORYDORAS            COMMUNITY   FRESHWATER  250
COMMON DISCUS               COMMUNITY   FRESHWATER  250
SPOTTED RAPHAEL             COMMUNITY   FRESHWATER  250
UPSIDE-DOWN CATFISH         COMMUNITY   FRESHWATER  250
BLUESPOTTED ANGELFISH       COMMUNITY   MARINE      50000
CHALK BASS                  COMMUNITY   MARINE      50000
FLAME ANGELFISH             COMMUNITY   MARINE      50000
ORIENTAL SWEETLIPS          COMMUNITY   MARINE      50000
ROUND STINGRAY              COMMUNITY   MARINE      50000
ROYAL ANGELFISH             COMMUNITY   MARINE      50000
TEIRA BATFISH               COMMUNITY   MARINE      50000
BARTLETTS ANTHIAS           REEF        MARINE      75000
BELLUS ANGELFISH            REEF        MARINE      75000
CHALK BASS                  REEF        MARINE      75000
COPPERBANDED BUTTERFLYFISH  REEF        MARINE      75000
MANDARINFISH                    REEF        MARINE      75000
COMMON TOADFISH             SPECIES     BRACKISH    100
GREEN SPOTTED PUFFER        SPECIES     BRACKISH    100
REEDFISH                    SPECIES     BRACKISH        100
ELECTRIC EEL                    SPECIES     FRESHWATER  500
ORNATE BICHIR               SPECIES     FRESHWATER  500
REEDFISH                    SPECIES     FRESHWATER  500
BRAZILIAN SEAHORSE          SPECIES     MARINE      10000

AquariumModel 也有四列:aquariumType、salinity、volume、price:

AGGRESSIVE  BRACKISH    125    200
AGGRESSIVE  FRESHWATER  300    500
AGGRESSIVE  MARINE      25000  4500
COMMUNITY   BRACKISH    150    250
COMMUNITY   FRESHWATER  250    400
COMMUNITY   MARINE      50000  7000
REEF        MARINE      75000  10000
SPECIES     BRACKISH    100    150
SPECIES     FRESHWATER  500    800
SPECIES     MARINE      10000  3000

Answer 1

我认为您可以首先根据您需要的值将两个表连接在一起，这样开始:

SELECT * 
FROM aquariummodel am 
JOIN appropriatefor af ON am.aquariumtype = af.aquariumtype AND am.salinity = af.salinity AND am.volume = af.volume;

然后，您想要按水族馆类型中的所有值进行分组，并使用计数大于 1 的条件。因此我会将查询更改为:

SELECT am.*, COUNT(*) AS numFishAppropriateFor
FROM aquariummodel am
JOIN appropriatefor af ON am.aquariumtype = af.aquariumtype AND am.salinity = af.salinity AND am.volume = af.volume
GROUP BY am.aquariumtype, am.salinity, am.volume
HAVING COUNT(*) > 1;

编辑这是一个 SQL Fiddle以证明其有效。

如果可以的话，我想对您的数据库设计发表评论。

在适当的表中，您有几个从 aquariummodel 表中重复的列。我会在您的 aquariummodel 表中添加一个主键(如 auto_increment id)并在适当的表中使用该 id。这样，aquariummodel 将有 5 列，但 appropriatefor 可以减少为两列:speciesname 和 aquariummodel_id。不过只是一个建议。