查询有些麻烦。我正在尝试从deposit_type_language_id = 3的表中创建选择，或者如果按deposit_type_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;
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句