主页 / user-245771

Stefanie's questions

Martin Hope
Stefanie
Asked: 2022-01-15 06:46:52 +0800 CST
  • 0

我正在使用 mariadb 服务器版本 10.6.4。

这是我的表定义：

CREATE TABLE `tmp_dba` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `case_id` bigint(20) DEFAULT NULL,
  `client_id` bigint(20) NOT NULL,
  `arrival` date DEFAULT NULL,
  `departure` date DEFAULT NULL,
  PRIMARY KEY (`ID`)
)

以下是一些示例数据：

INSERT INTO `tmp_dba` VALUES
(1,10,1000,'2018-10-02','2019-04-25'),
(2,10,1000,'2019-04-26','2019-05-01'),
(3,10,1000,'2019-05-02',NULL),
(4,20,2000,'2018-11-21',NULL),
(5,20,2001,'2018-11-21',NULL),
(6,20,2002,'2018-11-21',NULL),
(7,30,3000,'2019-03-04','2022-01-01'),
(8,30,3001,'2019-03-04','2022-01-01'),
(9,30,3002,'2019-03-04','2022-01-01'),
(10,30,3003,'2019-03-04','2022-01-01'),
(11,30,3004,'2019-03-04','2022-01-01');

我想要实现的是确定每组 case_id 和 client_id 的 MIN(arrival)，如果离开不为空，则应显示 MAX(departure)，否则为空。

我想最终每个客户只有一行提供上述数据。

例如对于 case_id = 10 我想看到 1 行这样： 10;1000;2018-10-02;NULL。

对于 case_id = 20，结果应该是 4 行，因为 case_id 和 client_id 有 4 种不同的组合。

对于 case_id = 30，应该显示 5 行，因为 case_id 和 client_id 有 5 种不同的组合。

使用 group by 时我一定做错了什么。

更多信息：

  • 数据是连续的，这意味着输入的新记录将具有比旧记录更高的 ID。此外，新的记录将——相对于之前的记录——在未来总是如此。这并不意味着下一个记录总是会在最后一次出发后的第二天到达。
  • 同一客户的表中的新记录将始终设置先前记录的出发日期。在这种情况下，以前的记录离开不能为空。
  • 当没有离开并且客户仍然“进入”时，离开将始终为空。
  • 出发时间必须小于到达时间，除非出发时间为空。
  • 到达不能为空。

这是可行的吗？

解决方案

SELECT 
-- First solution, but wrong, GROUP_CONCAT(ID) avoids error below
-- ID, case_id, client_id, <-- only_full_group_by - error
GROUP_CONCAT(ID), case_id, client_id,
MIN(arrival) AS arrival,
IF(COUNT(departure) = COUNT(*), MAX(departure), NULL) AS departure
FROM tmp_dba
GROUP BY case_id, client_id
ORDER BY case_id, client_id

非常感谢您的帮助。

斯蒂菲

mariadb greatest-n-per-group
Martin Hope
Stefanie
Asked: 2022-01-07 04:09:45 +0800 CST
  • 1

我有一张桌子，里面有这样的行：

+----------+--------------+---------------------+------+
| CASE_ID  | CAT          | BIRTH               | TYPE |
+----------+--------------+---------------------+------+
| 20033738 | CASE_OWNER   | 1996-04-08 00:00:00 | NULL |
| 20033738 | WIFE         | 1995-08-22 00:00:00 | NULL |
| 20039334 | CASE_OWNER   | 1994-03-10 00:00:00 | NULL |
| 20039301 | CASE_OWNER   | 1999-07-27 00:00:00 | NULL |
| 20039301 | WIFE         | 2001-07-05 00:00:00 | NULL |
| 20039301 | CHILD        | 2018-10-22 00:00:00 | NULL |
| 20033831 | CASE_OWNER   | 1975-03-05 00:00:00 | NULL |
| 20033831 | CHILD        | 2005-03-19 00:00:00 | NULL |
| 20033831 | CHILD        | 2006-03-25 00:00:00 | NULL |
| 20033831 | CHILD        | 2010-05-20 00:00:00 | NULL |
| 20033831 | CHILD        | 2013-10-25 00:00:00 | NULL |
+----------+--------------+---------------------+------+

对于一个 CASE_ID，每个 CASE_OWNER 的组合都有或没有 WIFE 和/或 CHILD（1 个或多个）都是可能的。

对于每个 CASE_ID，我想根据在 CAT 和 BIRTH 中找到的信息设置列 TYPE：

a) 如果 CASE_ID 中只有 CASE_OWNER，则 TYPE 应为

  • a1) 匹配 CASE_ID 的列 TYPE 中的 SINGLE_PERSON，如果 CASE_OWNER 早于 21
  • a2) 与 CASE_ID 匹配的列 TYPE 中的 UNKNOWN，如果 CASE_OWNER 小于 21

b) 如果 CASE_ID 的 CASE_ID 具有 CAT CASE_OWNER AND WIFE (WITHOUT CHILD)，则在与特定 CASE_ID 匹配的每一行中，TYPE 都应为 PAIR_NO_CHILD。

c) 如果一个 CASE_ID 有 CAT CASE_OWNER AND WIFE AND 1 个或多个 CHILD(ren) 对应一个 CASE_ID，则 TYPE 应该是

  • c1) PAIR_WITH_CHILD 如果在匹配特定 CASE_ID 的每一行中有一个或多个 CHILD(ren) 低于 21。
  • c2) OTHER 如果所有 CHILD(ren) 在与特定 CASE_ID 匹配的每一行中都是 21。

d) 如果一个 CASE_ID 有 CAT CASE_OWNER 并且一个 CASE_ID 有 1 个或多个 CHILD(ren)，则 TYPE 应该是

  • d1) SINGLE_WITH_CHILD，以防匹配特定 CASE_ID 的每一行中一个或多个（不是全部）CHILD(ren) 低于 21。
  • d2) 如果所有 CHILD(ren) 都在 21 岁以上，则为 MULTIPLE。

e) 所有其他组合将是 TYPE == UNKNOWN。

我的问题是：

  • 使用 SQL 是否可行？
  • 这应该使用 SQL 还是使用编程语言来解决？
  • 如果这在 SQL 中是可行的 - 应该怎么做？

非常感谢您的反馈

mysql update