Mysql:具有最小值的行并根据大小写优先考虑条件

Question

我有两个表，第一个表是:文档和另一张 table :doc_val

以 doc_id 作为表 docs 的外键

我需要从 doc_val 中获取 docs 列表(包括 val、type 和 criteria ) 匹配某些条件，例如:doc_val.criteria = 'L' and docs.rev = 1

在获取此文档列表时，我还需要确保给定 doc_id 的 doc_val.val 是最小值。并且还要确保 doc_val.type = 'D'，假设存在 doc_val.type = 'D' 否则我们应该简单地获取 doc_val 对于给定的 doc_id，它具有最小的 doc_val.val。

CREATE TABLE IF NOT EXISTS `docs` (
  `id` int(6) unsigned NOT NULL,
  `rev` int(3) unsigned NOT NULL,
  `content` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `doc_val` (
  `id` int(6) unsigned NOT NULL,
  `doc_id` int(6) unsigned NOT NULL,
  `val` int(3) unsigned NOT NULL,
  `type` varchar(2) NOT NULL,
  `criteria` varchar(2) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `docs` (`id`, `rev`, `content`) VALUES
  ('1', '1', 'The earth is flat'),
  ('2', '1', 'One hundred angels can dance on the head of a pin'),
  ('3', '1', 'The earth is flat and rests on a bull\'s horn'),
  ('4', '4', 'The earth is like a ball.');

INSERT INTO `doc_val` (`id`, `doc_id`, `val`, `type`, `criteria`) VALUES
  ('1', '1', 100, 'D', 'L'),
  ('2', '1', 101, 'D', 'L'),
  ('3', '1', 80, 'H', 'L'),
  ('4', '2', 10, 'H', 'S'),
  ('5', '2', 90, 'H', 'L'),
  ('6', '3', 100, 'D', 'L'),
  ('7', '3', 100, 'D', 'L');

对于此查询，如果我将 b.type = 'D' 简单地作为 where 条件的一部分，我将丢失所有类型不为 D 的文档。

SELECT a.id, a.rev, a.content, b.val, b.type, b.criteria
FROM `docs` a
  JOIN `doc_val` b ON b.doc_id = a.id
  WHERE a.`rev` = 1 and b.type = 'D' and b.criteria = 'L'      
GROUP BY `a`.`id`
HAVING min(b.`val`)

如果我们根本不将 type=D 视为条件，则此条件的输出有点有效，但是，

SELECT a.id, a.rev, a.content, b.val, b.type, b.criteria
FROM `docs` a
  JOIN `doc_val` b ON b.doc_id = a.id
  WHERE a.`rev` = 1 and b.criteria = 'L'      
GROUP BY `a`.`id`
HAVING min(b.`val`)

最终预期输出:

但从技术上讲，如果没有 type=D 作为条件，我应该会收到 doc.id = 1 的输出:

1. 所以我可能在使用 HAVING 时做错了任何方向都会有帮助。

2. 是否可以将 doc_val.type 设置为 doc_val.type = D 的优先级，这样当 type = D 的行> 它优先，如果它不存在，只取一个最小值而不考虑type?

Answer 1

你可以试试下面-

DEMO

SELECT 
    *
FROM
    (SELECT 
        a.id, a.rev, a.content, MIN(b.val) val, b.type, b.criteria
    FROM
        `docs` a
    JOIN `doc_val` b ON b.doc_id = a.id
    WHERE
        a.`rev` = 1 AND b.criteria = 'L'
    GROUP BY a.id , a.rev , a.content , b.type , b.criteria) A
WHERE
    val IN (SELECT 
            MAX(val)
        FROM
            (SELECT 
                a.id, a.rev, a.content, MIN(b.val) val, b.type, b.criteria
            FROM
                `docs` a
            JOIN `doc_val` b ON b.doc_id = a.id
            WHERE
                a.`rev` = 1 AND b.criteria = 'L'
            GROUP BY a.id , a.rev , a.content , b.type , b.criteria) B
        WHERE
            A.content = B.content)

输出:

id  rev content                                            val   type  criteria                  
1   1   The earth is flat                                   100  D     L
2   1   One hundred angels can dance on the head of a pin   90   H     L
3   1   The earth is flat and rests on a bull's horn        100  D     L