mysql - 过滤所有行的特定条件，并为具有相同值的每一列仅返回一行

Question

我有 房屋租赁和 房屋租赁条款 (请参阅下面的表格模式)。 A 房屋租赁可以有多个 house_lease_terms 但是一次只能有一个“事件”术语。

表定义:

CREATE TABLE `house_leases` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `house_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `house_lease_terms` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `house_lease_id` int(10) unsigned NOT NULL,
  `date_start` datetime NOT NULL,
  `date_end` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `house_lease_terms_house_lease_id_foreign` (`house_lease_id`),
  CONSTRAINT `house_lease_terms_house_lease_id_foreign` FOREIGN KEY (`house_lease_id`) REFERENCES `house_leases` (`id`) ON DELETE CASCADE
);

如您所见 house_lease_terms.house_lease_id 对应于特定的 房屋租赁 ，但是可以有多行具有相同的 house_lease_id .

确定“事件”术语的规则是:

date_start <= NOW() AND date_end > NOW() OR date_end IS NULL

如果没有返回行，那么“事件”条款必须在 future ，因此规则更改为:

date_start > 现在()

我们通过 订购date_start DESC 如果条款不在 future ，因为可能返回多行，我们需要最新的 date_start 在结果的顶部。否则我们按 排序date_start ASC 因为我们想要最接近的 date_start 到现在为止。

然后我将其限制为 1 以仅获得一个结果，并且该行被视为“事件”术语。如果没有返回结果，则没有“事件”术语。

我有一个 SQL 语句，它具有获取特定 的逻辑house_lease_id .看起来像这样:

SELECT * FROM house_lease_terms
WHERE 
CASE 
    WHEN 
        (SELECT COUNT(*) FROM house_lease_terms WHERE date_start <= NOW() AND (date_end > NOW() OR date_end IS NULL) AND house_lease_id = 1)
    THEN 
        date_start <= NOW() AND (date_end > NOW() OR date_end IS NULL)
    ELSE
        date_start > NOW()
END
AND house_lease_id = 1
ORDER BY
    IF(
        (SELECT COUNT(*) FROM house_lease_terms WHERE date_start <= NOW() AND (date_end > NOW() OR date_end IS NULL) AND house_lease_id = 1), 
        unix_timestamp(date_start), 
        -unix_timestamp(date_start)
    ) DESC
LIMIT 1;

此语句有效，但我希望有一种更好的方法(更有效)来获取特定 的“事件”术语house_lease_id (如果您知道更好的解决方案，请分享)。

现在我需要一个查询来获取所有不同 的“事件”术语。 house_lease_id 的。

我不希望任何类型的自定义 MySQL 函数或存储过程来执行此操作。我不知道从哪里开始创建这个查询。我想我可以在某些子选择或连接中使用上面的查询，但我不确定我将如何这样做。

任何帮助将不胜感激!

SQLFiddle 数据: http://sqlfiddle.com/#!9/cab159/2/0

Answer 1

寻找:

SELECT *
FROM house_lease_terms
WHERE house_lease_id = 1
  AND (   (   (    date_start <= NOW() 
               AND date_end > NOW()
              ) 
           OR date_end IS NULL
          )
       OR (date_start > NOW()
          )
      )
ORDER BY CASE WHEN ((date_start <= NOW() AND date_end > NOW()) OR date_end IS NULL)
              THEN DATEDIFF(NOW(), date_start)
              ELSE DATEDIFF(date_start, NOW()) + 1000000
              END

SELECT id,
       CASE WHEN @var2=house_lease_id 
            THEN @var1:=house_lease_id 
            ELSE 1
            END row_number_in_house_lease_id,
       @var2:=house_lease_id house_lease_id,
       date_start,
       date_end,
       created_at,
       updated_at,
       deleted_at
FROM house_lease_terms, (SELECT @var1:=0, @var2:=0) variables
WHERE house_lease_id = 1
  AND (   (   (    date_start <= NOW() 
               AND date_end > NOW()
              ) 
           OR date_end IS NULL
          )
       OR (date_start > NOW()
          )
      )
ORDER BY house_lease_id,
         CASE WHEN ((date_start <= NOW() AND date_end > NOW()) OR date_end IS NULL)
              THEN DATEDIFF(NOW(), date_start)
              ELSE DATEDIFF(date_start, NOW()) + 1000000
              END

下一代。

SELECT id, house_lease_id, date_start, date_end
FROM (
SELECT id,
       CASE WHEN @var2=house_lease_id 
            THEN @var1:=@var1+1 
            ELSE @var1:=1
            END row_number_in_house_lease_id,
       @var2:=house_lease_id house_lease_id,
       date_start,
       date_end
FROM house_lease_terms, (SELECT @var1:=0, @var2:=0) variables 
WHERE (   (   (    date_start <= NOW() 
               AND date_end > NOW()
              ) 
           OR date_end IS NULL
          )
       OR (date_start > NOW()
          )
      )
ORDER BY house_lease_id,
         CASE WHEN ((date_start <= NOW() AND date_end > NOW()) OR date_end IS NULL)
              THEN DATEDIFF(NOW(), date_start)
              ELSE DATEDIFF(date_start, NOW()) + 1000000
              END
) AS cte
WHERE row_number_in_house_lease_id = 1;

fiddle