代码之家 › 专栏 › 技术社区 › DsCpp

MySQL获取所有比平均外观更高的行

sql mysql

-2

DsCpp · 技术社区 · 7 年前

这是我的尝试,但是结果产生了相同的名称和不同的外观值,因此我认为我有一个错误

 SELECT 
   a.first_name AS fname, 
   a.last_name AS lname, 
   films_per_actor.num_films 
 FROM 
   actor as a, 
   (
     SELECT 
       AVG(num_films) AS avg_films_num 
     FROM 
       (
         SELECT 
           COUNT(film_id) AS num_films, 
           a.actor_id 
         FROM 
           film_actor as f_a, 
           actor as a 
         WHERE 
           f_a.actor_id = a.actor_id 
         GROUP BY 
           actor_id
       ) as films_per_actor1
   ) as avg_films, 
   (
     SELECT 
       COUNT(film_id) AS num_films, 
       a.actor_id 
     FROM 
       film_actor as f_a, 
       actor as a 
     WHERE 
       f_a.actor_id = a.actor_id 
     GROUP BY 
       actor_id
   ) as films_per_actor 
 WHERE 
   films_per_actor.num_films > avg_films.avg_films_num + 10 
 ORDER BY 
   fname, 
   lname

但结果是

"ADAM"  "GRANT" "40"
"ADAM"  "GRANT" "39"
"ADAM"  "GRANT" "42"
"ADAM"  "GRANT" "41"
"ADAM"  "HOPPER"    "40"
"ADAM"  "HOPPER"    "39"
"ADAM"  "HOPPER"    "42"
"ADAM"  "HOPPER"    "41"
"AL"    "GARLAND"   "40"
"AL"    "GARLAND"   "39"
"AL"    "GARLAND"   "41"
"AL"    "GARLAND"   "42"
"ALAN"  "DREYFUSS"  "39"
"ALAN"  "DREYFUSS"  "40"
"ALAN"  "DREYFUSS"  "42"
"ALAN"  "DREYFUSS"  "41"

1 回复 | 直到 7 年前

Raza Rafaideen 7 年前

正如@Eric所说,使用现代显式连接语法可能会完全避免这个问题。在actor表和两个子查询之间没有联接条件。所以你会得到每一个演员,每一个计数都超过平均值。

SELECT 
  a.first_name AS fname, 
  a.last_name AS lname, 
  fpa.num_films 
FROM 
  actor as a 
  INNER JOIN (
    SELECT 
      actor_id, 
      COUNT(film_id) AS num_films 
    FROM 
      film_actor 
    GROUP BY 
      actor_id
  ) AS fpa ON a.actor_id = fpa.actor_id 
  INNER JOIN (
    SELECT 
      COUNT(*)/ COUNT(DISTINCT actor_id) AS avg_films_num 
    FROM 
      film_actor
  ) AS avg_films ON fpa.num_films > avg_films.avg_films_num 
ORDER BY 
  fname, 
  lname

或者:

  SELECT 
  a.first_name AS fname, 
  a.last_name AS lname, 
  fpa.num_films 
FROM 
  actor as a 
  INNER JOIN (
    SELECT 
      actor_id, 
      COUNT(film_id) AS num_films 
    FROM 
      film_actor 
    GROUP BY 
      actor_id
  ) AS fpa ON a.actor_id = fpa.actor_id 
WHERE 
  fpa.num_films > (
    SELECT 
      COUNT(*)/ COUNT(DISTINCT actor_id) AS avg_films_num 
    FROM 
      film_actor
  ) 
ORDER BY 
  fname, 
  lname

推荐文章

Johnny T · 基于当前值的SQL合并表[重复]

1 年前

Shukurullox Komiljonov · 从记录中获得相互和解。使用SQL

1 年前

John D · 需要为NULL或NOT NULL的WHERE子句

1 年前

ojek · 如何对SQL结果进行分组和编号?

1 年前

senek · 如何在PL/SQL中将选择结果(列)放入数组中

1 年前

Sax · 规范化Google表格(第一步)

1 年前

BarÄ±Å UÅaklÄ± · 如何在ON冲突更新中使用CTE中的值

1 年前

Jatin · 检索卷计数的动态sql抛出错误语法错误[关闭]

1 年前

Andrus · 如何在sql中查找第二个匹配项

1 年前

Nebula Tech · 在SQL Server中搜索字符串中单词的多次出现

1 年前