我必须按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。我送bank_id的tbl_fileStatus作为f_bank_id。
现在，我必须显示最后一个f_bankid计数。
例如，我必须获取其中f_filestatus = 1的计数。所以我的输出将是0。为什么0因为f_bankid 1 and 2有一个，f_filestatus=1但f_bankid 1 and 2最后一行带有f_filestatus该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
这是我的查询
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
您能帮我解决这个问题吗？
在@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您搜索。
参见演示。
在MySql 8.0+中，您可以使用FIRST_VALUE()窗口功能：
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 = ?
参见演示。
如果要f_filestatus在1行中全部显示结果：
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
参见演示。
结果：
> tcount1 | tcount2 | tcount3
> ------: | ------: | ------:
> 0 | 1 | 2
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句