mysql:错误:操作数应包含1列我用where…not in(select…)有什么问题?

4sup72z8  于 2021-07-26  发布在  Java
关注(0)|答案(4)|浏览(443)

credit:leetcode_1355. 活动参与者
问题:编写一个sql查询来查找所有活动的名称,这些活动的参与人数既不是最大的,也不是最小的。
按任意顺序返回结果表。表中的每个活动活动都由表中的任何人执行。

Friends table:
+------+--------------+---------------+
| id   | name         | activity      |
+------+--------------+---------------+
| 1    | Jonathan D.  | Eating        |
| 2    | Jade W.      | Singing       |
| 3    | Victor J.    | Singing       |
| 4    | Elvis Q.     | Eating        |
| 5    | Daniel A.    | Eating        |
| 6    | Bob B.       | Horse Riding  |
+------+--------------+---------------+

Activities table:
+------+--------------+
| id   | name         |
+------+--------------+
| 1    | Eating       |
| 2    | Singing      |
| 3    | Horse Riding |
+------+--------------+

Result table:
+--------------+
| activity     |
+--------------+
| Singing      |
+--------------+

我的代码如下:

WITH a AS(
    SELECT activity, COUNT(1) AS n 
    FROM Friends
    GROUP BY activity
    )

SELECT activity
FROM a
WHERE n NOT IN (SELECT MAX(n),MIN(n) FROM a)

我看到了使用n!=(从a)和n!=(从a)中选择max(n),但我不知道为什么我的代码出错。我猜是因为 SELECT MAX(n), MIN(n) FROM a '将生成两列,而不是两行。但我还是不知道确切的原因。
希望有人能帮我!非常感谢!

cyej8jka

cyej8jka1#

你很接近。但是 NOT IN 确实是这样的——因为子查询返回多个列。你只比较了一个值。相反,使用两个单独的比较:

SELECT activity
FROM a
WHERE n <> (SELECT MAX(n) FROM a) AND
      n <> (SELECT MIN(n) FROM a) ;
dba5bblo

dba5bblo2#

我猜是因为 SELECT MAX(n), MIN(n) FROM a 将生成两列,而不是两行。
是的,这就是重点。除了使用两个子查询(您自己已经找到了),您还可以利用这里的窗口函数(事实上您使用 with 子句表示您正在运行mysql 8.0,它支持窗口函数):

select activity
from (
    select 
        activity, 
        row_number() over(order by count(*) asc)  rn_asc,
        row_number() over(order by count(*) desc) rn_desc
    from friends
    group by activity
) t
where 1 not in (rn_asc, rn_desc)

我怀疑这比 with 子句和两个子查询。

jjjwad0x

jjjwad0x3#

而不是在中使用子查询 WHERE ,可以与子查询联接。

WITH a AS(
    SELECT activity, COUNT(1) AS n 
    FROM Friends
    GROUP BY activity
    )

SELECT activity
FROM a AS a1
JOIN (SELECT MAX(n) AS maxn, MIN(n) AS minn) AS a2
ON a1.n NOT IN (a2.maxn, a2.minn)
mlnl4t2r

mlnl4t2r4#

你可以用 MIN() 以及 MAX() 窗口功能:

WITH cte AS (
  SELECT activity, 
         COUNT(*) AS n,
         MIN(COUNT(*)) OVER () min_n,
         MAX(COUNT(*)) OVER () max_n          
  FROM Friends
  GROUP BY activity
)
SELECT activity, n
FROM cte
WHERE n NOT IN (min_n, max_n)

请看演示。
结果:

| activity | n   |
| -------- | --- |
| Singing  | 2   |

相关问题