mysql - 聚合现有查询以处理多行

Question

我有一个分类帐表，现在我能够根据某人的付款历史记录查找日期或 NULL(如果有人拖欠)。我需要一个查询，让我能够找到所有拖欠的成员，而不仅仅是特定的成员。

我需要能够运行一个查询来获取任何拖欠的成员(member)并向我返回 member_id 和拖欠日期。

基本上，查找特定成员拖欠行为的原始查询的作用是，只对每个成员而不是特定成员进行操作。

我已经尝试过:

SELECT DISTINCT member_id, created_at FROM member_ledger_items WHERE 
balance > 0 and id > (
    IFNULL(
        (SELECT id from member_ledger_items WHERE balance <= 0 and member_ledger_items.deleted_at is NULL GROUP BY member_id ORDER BY created_at, id desc LIMIT 1),
        0
    )
) and `member_ledger_items`.`deleted_at` is null GROUP BY member_id order by created_at asc, id asc;

这是查找特定成员是否拖欠的查询:

select `created_at` from `member_ledger_items` where `member_id` = ? and `balance` > 0 and `id` > 
(
IFNULL(
(select `id` from `member_ledger_items` where `member_id` = ? and `balance` <= 0 and `member_ledger_items`.`deleted_at` is null order by `created_at` desc, `id` desc limit 1)
, 0)
) 
and `member_ledger_items`.`deleted_at` is null order by `created_at` asc, `id` asc limit 1;

以下是 member_ledger_items 表的创建语法:

CREATE TABLE `member_ledger_items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `member_id` int(10) unsigned NOT NULL,
  `status` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `type` enum('credit','debit') COLLATE utf8_unicode_ci NOT NULL,
  `category` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `memo` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `amount` decimal(13,3) DEFAULT NULL,
  `autopay` tinyint(1) DEFAULT NULL,
  `late` tinyint(1) DEFAULT NULL,
  `date` date NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `balance` decimal(13,3) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=53596 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

我需要包含 member_id 和开始拖欠日期的行。

这可能吗？

如有任何帮助，我们将不胜感激!

Answer 1

SELECT `member_id`, 
       (SELECT `created_at` 
        FROM   `member_ledger_items` AS MLI2 
        WHERE  `balance` > 0 
               AND MLI2.`member_id` = MLI.`member_id` 
               AND `id` > ( Ifnull((SELECT `id` 
                                    FROM   `member_ledger_items` AS MLI3 
                                    WHERE  `balance` <= 0 
                                           AND MLI3.`member_id` = 
                                               MLI2.`member_id` 
                                           AND MLI3.`deleted_at` IS NULL 
                                    ORDER  BY `created_at` DESC, 
                                              `id` DESC 
                                    LIMIT  1), 0) ) 
               AND MLI2.`deleted_at` IS NULL 
        ORDER  BY `created_at` ASC, 
                  `id` ASC 
        LIMIT  1) AS created_date 
FROM   `member_ledger_items` AS MLI 
GROUP  BY `member_id`; 

最终成为解决方案