如何获取mysql数据库的行数

发布于 2024-12-03 05:14:20 字数 1069 浏览 0 评论 0原文

+-------------------+---------------------+------+-----+---------+----------------+
| RowId             | MSGID| Received| UID | Default |                |
+-------------------+---------------------+------+-----+---------+----------------+
| 1                 | 1    | NO      | 1   | NULL    |                | 
| 2                 | 1    | YES     | 3   | NULL    |                | 
| 3                 | 1    | YES     | 4   | NULL    |                | 
| 4                 | 1    | YES     | 5   | NULL    |                | 
| 5                 | 5    | YES     | 2   | NULL    |                | 
| 6                 | 2    | YES     | 8   | NULL    |                | 
| 7                 | 1    | YES     | 9   | NULL    |                | 
+-------------------+---------------------+------+-----+---------+----------------+

这是我在 MySQL 中的表。

如何获取 MSGID = 1 的消息计数以及已收到 MSGID = 1 消息的用户数量以及未收到消息的用户数量消息 MSGID = 1

我想在循环中执行此操作,以便可以获得 [5,4,1] 等三个值。因此,每次页面加载时,查询都会检查数据库并更新集合。请帮忙

+-------------------+---------------------+------+-----+---------+----------------+
| RowId             | MSGID| Received| UID | Default |                |
+-------------------+---------------------+------+-----+---------+----------------+
| 1                 | 1    | NO      | 1   | NULL    |                | 
| 2                 | 1    | YES     | 3   | NULL    |                | 
| 3                 | 1    | YES     | 4   | NULL    |                | 
| 4                 | 1    | YES     | 5   | NULL    |                | 
| 5                 | 5    | YES     | 2   | NULL    |                | 
| 6                 | 2    | YES     | 8   | NULL    |                | 
| 7                 | 1    | YES     | 9   | NULL    |                | 
+-------------------+---------------------+------+-----+---------+----------------+

This is my table in MySQL.

How can I get count of messages where MSGID = 1 and count of no of users who have received message where MSGID = 1 and count of no of users who haven't received message where MSGID = 1?

I want to do it in a loop so that I can Get three values like [5,4,1]. So each time the page load the query will check the database and update the sets. pls help

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

嘿咻 2024-12-10 05:14:20

首先获取消息数量计数,其中 MSGID = 1

SELECT COUNT(RowID) FROM tableName WHERE MSGID = 1

获取已收到消息的用户数量计数,其中 MSGID = 1

SELECT COUNT(DISTINCT UID) FROM tableName WHERE MSGID = 1

获取未收到消息的用户数量计数,其中 MSGID = 1

SELECT COUNT(DISTINCT UID) FROM tableName WHERE MSGID =1 AND Received = 'NO'

First to get a count for the number of messages where MSGID = 1

SELECT COUNT(RowID) FROM tableName WHERE MSGID = 1

To get a count of the number of users who have received a msg where MSGID = 1

SELECT COUNT(DISTINCT UID) FROM tableName WHERE MSGID = 1

To get a count of the number of users who haven't received a msg where MSGID = 1

SELECT COUNT(DISTINCT UID) FROM tableName WHERE MSGID =1 AND Received = 'NO'
话少心凉 2024-12-10 05:14:20

您可以在一个查询中完成此操作。我没有看到循环的好处?

select count(*)
     , count(distinct uid)
     , count(distinct case when received = 'NO' then uid else null end case)
  from table_name
 where msgid = 1

这实际上应该是多个表,

1)用户,在 uid 上不同

2)消息,在 msgid 上不同,收到的是/否。

You can do this in one query. I don't see the benefit of the loop?

select count(*)
     , count(distinct uid)
     , count(distinct case when received = 'NO' then uid else null end case)
  from table_name
 where msgid = 1

This should really be multiple tables,

1) users, distinct on uid

2) messages, distinct on msgid, with received yes / no.

故事与诗 2024-12-10 05:14:20

要使用一个查询来获取它,您可以像下面这样进行联合:

SELECT COUNT(RowID) AS 'Col1', '' AS 'Col2', '' AS 'Col3' 
FROM tableName WHERE MSGID = 1

UNION ALL

SELECT '' AS 'Col1', COUNT(DISTINCT UID) AS 'Col2', '' AS 'Col3' 
FROM tableName WHERE MSGID = 1

UNION ALL

SELECT '' AS 'Col1', '' AS 'Col2' , COUNT(DISTINCT UID) AS 'Col3' 
FROM tableName WHERE MSGID =1 AND Received = 'NO'

希望这对您有用。

To get it using one query you can do a union all like the following:

SELECT COUNT(RowID) AS 'Col1', '' AS 'Col2', '' AS 'Col3' 
FROM tableName WHERE MSGID = 1

UNION ALL

SELECT '' AS 'Col1', COUNT(DISTINCT UID) AS 'Col2', '' AS 'Col3' 
FROM tableName WHERE MSGID = 1

UNION ALL

SELECT '' AS 'Col1', '' AS 'Col2' , COUNT(DISTINCT UID) AS 'Col3' 
FROM tableName WHERE MSGID =1 AND Received = 'NO'

Hope this works out for you.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文