如何使用“IN”运算符获取计数

I am trying to use the IN operator to get the count of certain fields in the table.

我正在尝试使用IN操作符来获取表中某些字段的计数。

This is my query:

这是我的查询:

SELECT order_id, COUNT(*)
FROM remake_error_type
WHERE order_id IN (1, 2, 100)
GROUP BY order_id;

My current output:

我目前的输出:

| order_id | COUNT(*) |
+----------+----------+
|    1     |    8     |
|    2     |    8     |

My expected output:

我预期的输出:

| order_id | COUNT(*) |
+----------+----------+
|    1     |    8     |
|    2     |    8     |
|    100   |    0     |

3 个解决方案

#1

You can write your query this way:

您可以这样编写查询:

SELECT t.id, COUNT(remake_error_type.order_id)
FROM
  (SELECT 1 AS id UNION ALL SELECT 2 UNION ALL SELECT 100) as t
  LEFT JOIN remake_error_type
  ON t.id = remake_error_type.order_id
GROUP BY
  t.id

a LEFT JOIN will return all rows from the subquery on the left, and the COUNT(remake_error_type.order_id) will count all values where the join succeeds.

左连接将返回左侧子查询中的所有行，COUNT(remake_error_type.order_id)将计算连接成功的所有值。

#2

You can create a temporary table, insert as many order_ids as required, and perform the left join to remake_error_type. At a small number of orders the other answers are sufficient, but if you were doing this for a lot of orders, UNION ALL and sub-queries are inefficient, both to type it up and to execute on the server.

您可以创建一个临时表，根据需要插入多个order_id，并将左连接执行remake_error_type。对于少量的订单，其他的答案就足够了，但是如果您对很多订单都这样做，那么UNION ALL和子查询都是低效的，无论是在服务器上输入还是执行。

Additionally, this is a very dynamic approach, because you can control easily the values in your temp table by modifying the insert statement.

此外，这是一种非常动态的方法，因为您可以通过修改insert语句轻松地控制temp表中的值。

However, this will only work if the database user has sufficient privileges: at least select, create temporary and drop table.

但是，只有当数据库用户拥有足够的特权时，这才会起作用:至少可以选择、创建临时表和删除表。

DROP TABLE IF EXISTS myTempOrders;
CREATE TEMPORARY TABLE myTempOrders (order_id INTEGER, PRIMARY KEY(order_id));
INSERT INTO myTempOrders (order_id) VALUES (1), (2), (100);

SELECT temp.order_id, count(*)

FROM myTempOrders temp
LEFT JOIN remake_error_type ON temp.order_id = remake_error_type.order_id
GROUP BY 1

If the order_id values exist in some table, then it is possible to extract the desired result without creating a temporary table and inserting values into it.

如果order_id值存在于某个表中，那么可以提取所需的结果，而无需创建临时表并将值插入其中。

To qualify, the table must

要符合条件，表必须

have an auto increment primary key with # rows greater than the maximum sought order_id value
是否有一个自动递增主键，其中#行大于最大查找的order_id值
have a starting increment value less than the minimum sought order_id value
初始增量值是否小于最小查找的order_id值
have no missing values in the primary key (i.e. no records have been deleted)
主键中没有缺失值(即没有删除记录)

if a qualified table exists, then you can run the following query, where you have to replace surrogate with the qualified table name and surrogate_id with the auto-incrementing primary key of the qualified table name

如果存在一个限定表，那么可以运行以下查询，其中必须使用限定表名和surrogate_id替换代理，并使用限定表名的自动递增主键

SELECT surrogate.surrogate_id, count(*)

FROM my_qualified_table surrogate
LEFT JOIN remake_error_type ON surrogate.surrogate_id = remake_error_type.order_id
WHERE surrogate.surrogate_id IN (1, 2, 100)
GROUP BY 1

#3

You could use a union for this. No, this does not use the IN operator, but it is an alternative that will give you your expected results. One option is to hardcode the order_id and use conditional aggregation to get the SUM() of rows with that id:

你可以为此建立一个联盟。不，这并没有使用IN操作符，但是它是另一种可以给您预期结果的方法。一种方法是硬编码order_id，并使用条件聚合来获取具有该id的行的和():

SELECT 1 AS order_id, SUM(order_id = 1) AS numOrders FROM myTable
UNION ALL
SELECT 2 AS order_id, SUM(order_id = 2) AS numOrders FROM myTable
UNION ALL
SELECT 100 AS order_id, SUM(order_id = 100) AS numOrders FROM myTable;

Here is an SQL Fiddle example.

下面是一个SQL Fiddle示例。

#1