php - Mysql 从重复项中获取 1 个值

Question

我做了一个查询，从表 X

中获取重复值

id         | name    |     created         | modified 
­­­­­­­­­­­­-----------------------------------------------------------
 2         | Film1   | 2016-01-29 06:00:09 | 2016-01-29 06:00:10
 5         | Film1   | 2016-01-27 06:00:09 | 2016-01-27 06:00:10
 7         | Film1   | 2016-01-23 06:00:09 | 2016-01-23 06:00:10
 1         | Film2   | 2016-01-26 06:00:09 | 2016-01-26 06:00:10
 8         | Film2   | 2016-01-28 06:00:09 | 2016-01-28 06:00:10
 9         | Film2   | 2016-01-29 06:00:09 | 2016-01-29 06:00:10

我有一个表Y，我在其中获取每个重复值的照片数量:

id         | name    |     num_photos      | 
­­­­­­­­­­­­--------------------------------------------
 2         | Film1   |        20           | 
 5         | Film1   |        10           | 
 7         | Film1   |        12           | 
 1         | Film2   |        20           | 
 8         | Film2   |        50           | 
 9         | Film2   |        12           | 

如何获得照片数量更多的副本？在 php 数组中？

结果:

id         | name    |     num_photos | 
­­­­­­­­­­­­---------------------------------------
 2         | Film1   |        20      |  
 8         | Film2   |        50      | 

Answer 1

创建表/插入数据

CREATE TABLE Y
    (`id` INT, `name` VARCHAR(5), `num_photos` INT)
;

INSERT INTO Y
    (`id`, `name`, `num_photos`)
VALUES
    (2, 'Film1', 20),
    (5, 'Film1', 10),
    (7, 'Film1', 12),
    (1, 'Film2', 20),
    (8, 'Film2', 50),
    (9, 'Film2', 12)
;

有两种方法可以做到这一点。

使用用户变量。

查询

SELECT 
   y.id
 , y.name
 , y.num_photos
FROM ( SELECT @name := '') AS init_user_var
CROSS JOIN (
 SELECT
  *
  , (y.name != @name) AS firstInGroup
  , @name := y.name
 FROM 
  Y
 ORDER BY 
    Y.name ASC
  , Y.num_photos DESC 
)
 AS
  Y   
WHERE
 y.firstInGroup = 1
ORDER BY 
 Y.id ASC 

结果

    id  name    num_photos  
------  ------  ------------
     2  Film1             20
     8  Film2             50

带有带最大值和分组依据以及连接的交付表。

查询

SELECT 
 y.*
FROM (
 SELECT 
   y.name 
  , MAX(y.num_photos) max_num_photos
 FROM 
  Y
 GROUP BY
  y.name 
) AS
 y_max_num_photos
INNER JOIN
  Y
ON
 Y.name = y_max_num_photos.name
 AND
 Y.num_photos = y_max_num_photos.max_num_photos
 ORDER BY 
  y.id ASC

结果

    id  name    num_photos  
------  ------  ------------
     2  Film1             20
     8  Film2             50