MySQL：检查一组查询返回相同的行数：但我不知道计数是什么

We read values from a set of sensors, occasionally a reading or two is lost for a particular sensor , so now and again I run a query to see if all sensors have the same record count.

我们从一组传感器读取值，偶尔会丢失一个或两个特定传感器的读数，因此我不时地运行查询以查看所有传感器是否具有相同的记录计数。

GROUP BY sensor_id HAVING COUNT(*) != xxx;

So I run a query once to visually get a value of xxx and then run it again to see if any vary.

所以我运行一次查询以直观地获取xxx的值，然后再次运行它以查看是否有任何变化。

But is there any clever way of doing this automatically in a single query?

但是，在单个查询中是否有任何聪明的方法可以自动执行此操作？

2 个解决方案

#1

You could do:

你可以这样做：

HAVING COUNT(*) != (SELECT MAX(count) FROM (
  SELECT COUNT(*) AS count FROM my_table GROUP BY sensor_id
) t)

Or else group again by the count in each group (and ignore the first result):

或者按每组中的计数再次分组（并忽略第一个结果）：

SELECT count, GROUP_CONCAT(sensor_id) AS sensors
FROM (
  SELECT sensor_id, COUNT(*) AS count FROM my_table GROUP BY sensor_id
) t
GROUP BY count
ORDER BY count DESC
LIMIT    1, 18446744073709551615

#2

SELECT sensor_id,COUNT(*) AS count
FROM table
GROUP BY sensor_id
ORDER BY count

Will show a list of the sensor_id along with a count of all the records it has, you can then manually check to see if any vary.

将显示sensor_id的列表以及它具有的所有记录的计数，然后您可以手动检查是否有任何变化。

SELECT * FROM (
SELECT sensor_id,COUNT(*) AS count
FROM table
GROUP BY sensor_id
) AS t1
GROUP BY count

Will show all the counts that vary, but the group by will lose information about which sensor_ids have which counts.

将显示所有变化的计数，但group by将丢失有关哪个sensor_ids具有哪些计数的信息。

---EDIT---

- -编辑 - -

Taken a bit from both mine and eggyal's answer and created this, for the count that is most frequent I call the id default, and then for any values that stand out I have given them separate rows. This way you maintain the readability of a table if you have many results Multi Row, but also have a simple one row column if all counts are the same One Row. If however you are happy with the concocted strings then go with eggyal's answer. Might be a bit over the top but here goes:

从我和eggyal的答案中得到了一点并创造了这个，对于最频繁的计数我称之为默认值，然后对于任何突出的值我给它们分开的行。这样，如果您有许多结果Multi Row，则可以保持表的可读性，但如果所有计数都是相同的One Row，则还具有简单的一行列。然而，如果你对编造的琴弦感到满意，那么请使用eggyal的答案。可能有点超过顶部但是这里：

select 'default' as id,t5.c1 as count from(
select id,count(*) as c1 from your_table group by id having count(*)=
(select t4.count from
(
select max(t3.count2) as max,t3.count as count from 
(
select count(*) as count2,t2.count from
(
  SELECT id,COUNT(*) AS count
  FROM your_table
  GROUP BY id
) as t2 
  GROUP BY count
) as t3
) as t4)) as t5 group by count
union all
select t5.id as id,t5.c1 as count from(
select id,count(*) as c1 from your_table group by id having count(*)<>
(select t4.count from
(
select max(t3.count2) as max,t3.count as count from 
(
select count(*) as count2,t2.count from
(
  SELECT id,COUNT(*) AS count
  FROM your_table
  GROUP BY id
) as t2 
  GROUP BY count
) as t3
) as t4)) as t5

#1