如何通过filestatus显示最后一行的总数? [英] How to display the total count of the last row by filestatus?

问题描述

我必须按filestatus显示最后一行的总数.

I have to display the total count of the last row by filestatus.

tbl_bankdata

bank_id | b_orderno| b_bankname| lead_id
     1  | 01-01    | 1         |  1
     2  | 01-02    | 2         |  1
     3  | 02-01    | 3         |  2
     4  | 03-01    | 1         |  3

tbl_fileStatus

f_id | f_bankid| f_filestatus
  1  |      1  | 1
  2  |      2  | 1
  3  |      2  | 2
  4  |      1  | 2
  5  |      1  | 3
  6  |      3  | 2 
  7  |      3  | 3  

我有两个表 tbl_bankdata 和 tbl_fileStatus .我正在 tbl_fileStatus 中将 bank_id 作为 f_bank_id 发送.

I have two tables tbl_bankdata and tbl_fileStatus. I am sending bank_id in the tbl_fileStatus as a f_bank_id.

现在，我必须显示最后一个 f_bankid 计数.

Now I have to show the last f_bankid count.

例如，我必须获取其中f_filestatus = 1的计数.因此我的输出将为 0 .为什么使用 0 ，因为 f_bankid 1和2 具有 f_filestatus = 1 ，但是 f_bankid 1和2 具有最后一行，f_filestatus 3和2 .

For example, I have to fetch the count where f_filestatus=1. so my output will be 0. Why 0 because f_bankid 1 and 2 have a f_filestatus=1 but f_bankid 1 and 2 have the last row with f_filestatus the 3 and 2.

如果我必须计数 f_filestatus = 2 ，那么我将获得输出 1 ，如果计数 f_filestatus = 3 ，则输出将为 2 .为什么2是因为 f_bank_id 1 具有 f_filestatus 3 和 f_bank_id 3 具有 f_filestatus 3

If I have to count f_filestatus=2 then I will get the output 1 and if count f_filestatus=3 then the output will be 2. why 2 because f_bank_id 1 have f_filestatus 3 and f_bank_id 3 have f_filestatus 3

这是我的查询

select (
    SELECT COUNT(f_id) 
    FROM tbl_fileStatus 
    WHERE f_filestatus=1 and f_id IN (
        SELECT MAX(f_id) FROM tbl_fileStatus GROUP BY f_bankid
    )
) as tcount

您能帮我解决这个问题吗?

Would you help me out with this issue?

在@forpas建议之后

After suggested by @forpas

SELECT (SELECT Count(DISTINCT f_bankid)
    FROM   tbl_filestatus t
    WHERE  1 = (SELECT f_filestatus
                FROM   tbl_filestatus
                WHERE  f_bankid = t.f_bankid
                ORDER  BY f_id DESC
                LIMIT  1)) AS tcount1,
   (SELECT Count(DISTINCT f_bankid)
    FROM   tbl_filestatus t
    WHERE  2 = (SELECT f_filestatus
                FROM   tbl_filestatus
                WHERE  f_bankid = t.f_bankid
                ORDER  BY f_id DESC
                LIMIT  1)) AS tcount2,
   (SELECT Count(DISTINCT f_bankid)
    FROM   tbl_filestatus t
    WHERE  3 = (SELECT f_filestatus
                FROM   tbl_filestatus
                WHERE  f_bankid = t.f_bankid
                ORDER  BY f_id DESC
                LIMIT  1)) AS tcount3  

推荐答案

使用相关子查询:

SELECT COUNT(DISTINCT f_bankid) AS tcount
FROM tbl_fileStatus t
WHERE ? = (SELECT f_filestatus FROM tbl_fileStatus WHERE f_bankid = t.f_bankid ORDER BY f_id DESC LIMIT 1)

将?替换为要搜索的 f_bankid .
请参见演示.

Replace ? with the f_bankid you search for.
See the demo.

在MySql 8.0+中，您可以使用 FIRST_VALUE()窗口函数:

In MySql 8.0+ you can use FIRST_VALUE() window function:

SELECT COUNT(*) AS tcount
FROM (
  SELECT DISTINCT f_bankid, 
         FIRST_VALUE(f_filestatus) OVER (PARTITION BY f_bankid ORDER BY f_id DESC) f_filestatus
  FROM tbl_fileStatus
) t
WHERE f_filestatus = ?

请参见演示.

如果要获得1行中所有 f_filestatus 的结果:

If you want results for all f_filestatus in 1 row:

SELECT
  SUM(f_filestatus = 1) AS tcount1,
  SUM(f_filestatus = 2) AS tcount2,
  SUM(f_filestatus = 3) AS tcount3
FROM (
  SELECT t.f_bankid, t.f_filestatus
  FROM tbl_fileStatus t
  WHERE t.f_id = (SELECT f_id FROM tbl_fileStatus WHERE f_bankid = t.f_bankid ORDER BY f_id DESC LIMIT 1)
) t

请参见演示.
结果:

See the demo.
Results:

> tcount1 | tcount2 | tcount3
> ------: | ------: | ------:
>       0 |       1 |       2

这篇关于如何通过filestatus显示最后一行的总数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！