sql - 如何根据WHERE条件计算值连续出现的次数？

Question

我有以下late_payments表：

ref-no  |Jan    |Feb    |Mar   |Apr    |May     |Jun
1       |       |PAID   |PAID  |       |PAID    |  
2       |       |PAID   |      |       |        |PAID
3       |PAID   |       |PAID  |PAID   |PAID    |PAID
4       |PAID   |PAID   |      |PAID   |PAID    |
5       |PAID   |       |PAID  |       |        |
6       |       |       |      |       |        |PAID 

我想为每个ID检查没有支付多少个月的时间。

例如，按列，我可以执行以下操作：

SELECT ref_no FROM late_payments WHERE Jan IS NULL OR Jan = ''

我还可以计算每列中Null字符串或PAID字符串的出现情况，如下所示：

SELECT Jan COUNT(*) FROM late_payments GROUP BY MyTable;

但是，我想做同样的事情，但要基于行而不是列。并稍作调整。例如，我要计算的是连续的空字符串的出现，并考虑了从年初到当前月份的情况。例如，如果这个月是2月，那么对于ID 1，它应计为1。如果当前月是4月，则应计为2。如果是6月，则应计为3，例如：

SELECT occurrences of '' WHERE ref_no = 1 AND current-month = Apr 

可能吗 ？！如果是这样，如何？

附注：我正在Qt上为C ++应用程序实现这些选择语句。我正在使用 QTableView在 QSqlQueryModel上显示select语句的结果。

更新

以下@ VR46回答下面，这是我所做的：

for(int i = 0; i < all_refNo_list.size(); i++) {
        qryTxt = "INSERT INTO late_payments "
                "select ref_no AS ref_no, sum(i_count) as unpaid_count from ("
                "SELECT ref_no, CASE WHEN January = 'PAID' THEN 0 ELSE 1 END AS i_count, 1 AS month_no FROM payments_" + getCurrentYear() +
                " UNION SELECT ref_no, CASE WHEN February = 'PAID' THEN 0 ELSE 1 END , 2  FROM payments_" + getCurrentYear() +
                " UNION SELECT ref_no, CASE WHEN March = 'PAID' THEN 0 ELSE 1 END , 3  FROM payments_" + getCurrentYear() +
                " UNION SELECT ref_no, CASE WHEN April = 'PAID' THEN 0 ELSE 1 END , 4  FROM payments_" + getCurrentYear() +
                " UNION SELECT ref_no, CASE WHEN May = 'PAID' THEN 0 ELSE 1 END , 5  FROM payments_" + getCurrentYear() +
                " UNION SELECT ref_no, CASE WHEN June = 'PAID' THEN 0 ELSE 1 END , 6  FROM payments_" + getCurrentYear() +
                " UNION SELECT ref_no, CASE WHEN July = 'PAID' THEN 0 ELSE 1 END , 7  FROM payments_" + getCurrentYear() +
                " UNION SELECT ref_no, CASE WHEN August = 'PAID' THEN 0 ELSE 1 END , 8  FROM payments_" + getCurrentYear() +
                " UNION SELECT ref_no, CASE WHEN September = 'PAID' THEN 0 ELSE 1 END , 9  FROM payments_" + getCurrentYear() +
                " UNION SELECT ref_no, CASE WHEN October = 'PAID' THEN 0 ELSE 1 END , 10  FROM payments_" + getCurrentYear() +
                " UNION SELECT ref_no, CASE WHEN November = 'PAID' THEN 0 ELSE 1 END , 11  FROM payments_" + getCurrentYear() +
                " UNION SELECT ref_no, CASE WHEN December = 'PAID' THEN 0 ELSE 1 END , 12  FROM payments_" + getCurrentYear() +
                ") A Where ref_no = " + all_refNo_list[i];

        query.prepare(qryTxt);
        if (!query.exec())
        {
            qDebug() << "Error" << query.lastError().text();
        }
    }

结果如下表：

ref_no   |unpaid_count
1        |3 
2        |4
3        |1
4        |2
5        |4
6        |5

这正是我所需要的。

但是现在，每当将特定的“ ref_no”检查为“ PAID”时，我就无法更新这些值。即：只要有变化，就更新表。

Answer 1

这是一种方法

Unpivot数据并使用过滤器执行count

select ID,sum(i_count) as unpaid_count
from
(
select ID ,case when Jan = 'PAID' then 0 else 1 end as i_count,1 as month_no
from payments
union all
select ID, case when Feb = 'PAID' then 0 else 1 end ,2
from payments  
union all
select ID, case when Mar = 'PAID' then 0 else 1 end ,3
from payments
union all
select ID, case when Apr = 'PAID' then 0 else 1 end ,4
from payments 
union all
..
) A 
Where month_no <= strftime('%m', date('now')) 

还可以考虑更改数据库设计，这样查询会容易得多