mysql-按selects中selects的结果分组

zazmityj 于 2021-06-23 发布在 Mysql

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

我当前正在执行一个查询，其中包含select中的两个select。我想对返回的结果进行分组，以便进行计数（进而创建饼图）
我试图查询的系统包含一个图像表。每个图像可能有零个或更多的元数据位。可用的元数据因图像而异，因此由单独的表提供，而不是列作为图像表的一部分。
所以表结构是：

Image {
   image_id (PK)
}

ImageMetaDataKey {
   metadata_key_id (PK),
   key
}

ImageMetaDataValue {
   metadata_value_id (PK),
   value
}

ImageMetaData {
   image_id (FK - Image.image_id),
   metadata_key_id (FK - ImageMetaDataKey.metadata_key_id ),
   metadata_value_id (FK - ImageMetaDataValue.metadata_value_id )
}

我当前的sql语句是：

SELECT i_o.image_id, (SELECT imdv.value
    FROM Image i, ImageMetaDataValue imdv, ImageMetaDataKey imdk, ImageMetaData imd
    WHERE imdk.metadata_key_id = imd.metadata_key_id 
    AND imd.metadata_value_id =imdv.metadata_value_id  
    AND (imdk.key='Camera Model')
    AND i.image_id=imd.image_id
) as Camera, (SELECT imdv.value
    FROM Image i, ImageMetaDataValue imdv, ImageMetaDataKey imdk, ImageMetaData imd
    WHERE imdk.metadata_key_id = imd.metadata_key_id 
    AND imd.metadata_value_id =imdv.metadata_value_id  
    AND (imdk.key='Lens Model')
    AND i.image_id=imd.image_id
) as Lens
FROM Image i_o
GROUP BY i_o.image_id;

+----------+-----------------------+-------------------------------------+
| image_id | Camera                | Lens                                |
+----------+-----------------------+-------------------------------------+
|       11 | Canon EOS 450D        | EF-S17-55mm f/2.8 IS USM            |
|       15 | Canon EOS 450D        | EF-S17-55mm f/2.8 IS USM            |
|       24 | Canon EOS 450D        | EF-S17-55mm f/2.8 IS USM            |
|       28 | Canon EOS 450D        | EF16-35mm f/2.8L USM                |
|       29 | Canon EOS 450D        | EF16-35mm f/2.8L USM                |
|       34 | Canon EOS 450D        | EF-S18-55mm f/3.5-5.6 IS            |
|       35 | Canon EOS 450D        | EF-S18-55mm f/3.5-5.6 IS            |
|       37 | Canon EOS 450D        | EF-S17-55mm f/2.8 IS USM            |
|       43 | Canon EOS 7D          | EF-S17-55mm f/2.8 IS USM            |
|       48 | Canon EOS 450D        | EF-S17-55mm f/2.8 IS USM            |
|       49 | Canon EOS 450D        | EF70-200mm f/2.8L USM               |
|       50 | Canon EOS 450D        | EF70-200mm f/2.8L USM               |
+----------+-----------------------+-------------------------------------+

理想情况下，我想运行类似于：

SELECT COUNT(i_o.image_id) as 'Count   ', (SELECT imdv.value
    FROM Image i, ImageMetaDataValue imdv, ImageMetaDataKey imdk, ImageMetaData imd
    WHERE imdk.metadata_key_id = imd.metadata_key_id 
    AND imd.metadata_value_id =imdv.metadata_value_id  
    AND (imdk.key='Camera Model')
    AND i.image_id=imd.image_id
) as Camera, (SELECT imdv.value
    FROM Image i, ImageMetaDataValue imdv, ImageMetaDataKey imdk, ImageMetaData imd
    WHERE imdk.metadata_key_id = imd.metadata_key_id 
    AND imd.metadata_value_id =imdv.metadata_value_id  
    AND (imdk.key='Lens Model')
    AND i.image_id=imd.image_id
) as Lens
FROM Image i_o
GROUP BY i_o.image_id, Camera, Lens;

它将返回：

+----------+-----------------------+-------------------------------------+
    | Count    | Camera                | Lens                                |
    +----------+-----------------------+-------------------------------------+
    |        5 | Canon EOS 450D        | EF-S17-55mm f/2.8 IS USM            |
    |        2 | Canon EOS 450D        | EF16-35mm f/2.8L USM                |
    |        2 | Canon EOS 450D        | EF-S18-55mm f/3.5-5.6 IS            |
    |        1 | Canon EOS 7D          | EF-S17-55mm f/2.8 IS USM            |
    |        2 | Canon EOS 450D        | EF70-200mm f/2.8L USM               |
    +----------+-----------------------+-------------------------------------+

sql mysql

来源：https://stackoverflow.com/questions/51804990/mysql-grouping-by-results-from-selects-in-selects

2条答案

按热度按时间

fsi0uk1n1#

我将使用两级聚合。首先得到两个模型：

SELECT imd.image_id,
       MAX(CASE WHEN imdk.key = 'Camera Model' THEN imdv.value END) as camera_model,
       MAX(CASE WHEN imdk.key = 'LENS Model' THEN imdv.value END) as lens_model
FROM ImageMetaData imd JOIN
     ImageMetaDataKey imdk
     ON imdk.metadata_key_id = imd.metadata_key_id JOIN
     ImageMetaDataValue imdv
     ON imd.metadata_value_id = imdv.metadata_value_id  
GROUP BY imd.image_id;

然后将其用作子查询以获取计数：

SELECT camera_model, lens_model, COUNT(*)
FROM (SELECT imd.image_id,
             MAX(CASE WHEN imdk.key = 'Camera Model' THEN imdv.value END) as camera_model,
             MAX(CASE WHEN imdk.key = 'LENS Model' THEN imdv.value END) as lens_model
      FROM ImageMetaData imd JOIN
           ImageMetaDataKey imdk
           ON imdk.metadata_key_id = imd.metadata_key_id JOIN
           ImageMetaDataValue imdv
           ON imd.metadata_value_id = imdv.metadata_value_id  
      GROUP BY imd.image_id
     ) cl
GROUP BY camera_model, lens_model;

非常重要的一点是，使用适当的、明确的、标准的 JOIN 语法。

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

hlswsv352#

因为我发现这个问题很有趣，所以通过对第一个查询的结果进行反向工程，我创建了一个sqlfiddle。然后我将查询改写为：

SELECT i.image_id,
       imdata1.value AS Camera,
       imdata2.value AS Lens
FROM Image i
JOIN (SELECT imd1.image_id, imdv1.value FROM ImageMetaData imd1
      JOIN ImageMetaDataKey imdk1 
        ON imdk1.metadata_key_id = imd1.metadata_key_id AND
           imdk1.key = 'Camera Model'
      JOIN ImageMetaDataValue imdv1
        ON imdv1.metadata_value_id = imd1.metadata_value_id
      ) AS imdata1 ON imdata1.image_id = i.image_id
JOIN (SELECT imd2.image_id, imdv2.value FROM ImageMetaData imd2
      JOIN ImageMetaDataKey imdk2
        ON imdk2.metadata_key_id = imd2.metadata_key_id AND
           imdk2.key = 'Lens Model'
      JOIN ImageMetaDataValue imdv2
        ON imdv2.metadata_value_id = imd2.metadata_value_id
      ) AS imdata2 ON imdata2.image_id = i.image_id

输出：

image_id    Camera              Lens
11          Canon EOS 450D      EF-S17-55mm f/2.8 IS USM
15          Canon EOS 450D      EF-S17-55mm f/2.8 IS USM
24          Canon EOS 450D      EF-S17-55mm f/2.8 IS USM
28          Canon EOS 450D      EF16-35mm f/2.8L USM
29          Canon EOS 450D      EF16-35mm f/2.8L USM
34          Canon EOS 450D      EF-S18-55mm f/3.5-5.6 IS
35          Canon EOS 450D      EF-S18-55mm f/3.5-5.6 IS
37          Canon EOS 450D      EF-S17-55mm f/2.8 IS USM
43          Canon EOS 7D        EF-S17-55mm f/2.8 IS USM
48          Canon EOS 450D      EF-S17-55mm f/2.8 IS USM
49          Canon EOS 450D      EF70-200mm f/2.8L USM
50          Canon EOS 450D      EF70-200mm f/2.8L USM

我重写它的原因是把它放在一个更适合分组的形式：

SELECT COUNT(i.image_id) AS `Count`,
       imdata1.value AS Camera,
       imdata2.value AS Lens
FROM Image i
JOIN (SELECT imd1.image_id, imdv1.value FROM ImageMetaData imd1
      JOIN ImageMetaDataKey imdk1 
        ON imdk1.metadata_key_id = imd1.metadata_key_id AND
           imdk1.key = 'Camera Model'
      JOIN ImageMetaDataValue imdv1
        ON imdv1.metadata_value_id = imd1.metadata_value_id
      ) AS imdata1 ON imdata1.image_id = i.image_id
JOIN (SELECT imd2.image_id, imdv2.value FROM ImageMetaData imd2
      JOIN ImageMetaDataKey imdk2
        ON imdk2.metadata_key_id = imd2.metadata_key_id AND
           imdk2.key = 'Lens Model'
      JOIN ImageMetaDataValue imdv2
        ON imdv2.metadata_value_id = imd2.metadata_value_id
      ) AS imdata2 ON imdata2.image_id = i.image_id
GROUP BY Camera, Lens

输出：

Count   Camera              Lens
5       Canon EOS 450D      EF-S17-55mm f/2.8 IS USM
2       Canon EOS 450D      EF-S18-55mm f/3.5-5.6 IS
2       Canon EOS 450D      EF16-35mm f/2.8L USM
2       Canon EOS 450D      EF70-200mm f/2.8L USM
1       Canon EOS 7D        EF-S17-55mm f/2.8 IS USM

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

我来回答

mysql-按selects中selects的结果分组

2条答案

相关问题

热门标签

最新问答