mysql - MySQL从具有回退的组中选择

查询有问题。我试图从SturiType Type语言3的表中创建SELECT，或者如果SaveTyType ID ID分组，则不存在行（1）（如回退）。

 +-----------------+--------------------------+-------------------+
    | deposit_type_id | deposit_type_language_id | deposit_type_name |
    +-----------------+--------------------------+-------------------+
    |               1 |                        1 | jewellery         |
    |               1 |                        2 | bijuterii         |
    |               1 |                        3 | šperky            |
    |               2 |                        1 | equipment         |
    |               2 |                        2 | tehnica           |
    +-----------------+--------------------------+-------------------+

我正在尝试以下查询：

SELECT t1.* from deposit_type AS t1
       INNER JOIN
           ( SELECT deposit_type_id, min(deposit_type_language_id) from
                    deposit_type group by deposit_type_id ) AS t2
       ON t1.deposit_type_id=t2.deposit_type_id group by deposit_type_id;

输出为：

+-----------------+--------------------------+-------------------+
| deposit_type_id | deposit_type_language_id | deposit_type_name |
+-----------------+--------------------------+-------------------+
|               1 |                        1 | jewellery         |
|               2 |                        1 | equipment         |
+-----------------+--------------------------+-------------------+

但我正在尝试实现如下目标：

+-----------------+--------------------------+-------------------+
| deposit_type_id | deposit_type_language_id | deposit_type_name |
+-----------------+--------------------------+-------------------+
|               1 |                        3 | šperky            |
|               2 |                        1 | equipment         |
+-----------------+--------------------------+-------------------+

我的错误在哪里？
表格结构：

deposit_type | CREATE TABLE `deposit_type` (
  `deposit_type_id` smallint(5) unsigned NOT NULL,
  `deposit_type_language_id` smallint(5) unsigned NOT NULL,
  `deposit_type_name` varchar(96) NOT NULL,
  PRIMARY KEY (`deposit_type_id`,`deposit_type_language_id`),
  KEY `deposit_type_language_id` (`deposit_type_language_id`),
  CONSTRAINT `deposit_type_ibfk_1` FOREIGN KEY (`deposit_type_language_id`) REFERENCES `language` (`language_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

最佳答案

憎恨者会憎恨。。。

SELECT x.deposit_type_id
     , COALESCE(y.deposit_type_language_id,x.deposit_type_language_id) deposit_type_language_id
     , COALESCE(y.deposit_type_name,x.deposit_type_name) deposit_type_name
  FROM deposit_type x
  LEFT
  JOIN deposit_type y
    ON y.deposit_type_id = x.deposit_type_id
   AND y.deposit_type_language_id = 3
 WHERE x.deposit_type_language_id = 1;

关于mysql - MySQL从具有回退的组中选择，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/33040772/