时间戳

首页 > 解决方案 > 带有子查询的 SQL 案例

sql - 带有子查询的 SQL 案例

问题描述

我是 SQL 新手，并且已经超出了我的深度。这（下）工作“有点”，但不是真的。因为子查询有效并且案例有效并且它不返回错误消息但我想在子查询之外制作案例语句。我需要每年显示 3 列“是或否”、“年份”以及“是”和“否”的“平均”数量。当我尝试将 case 语句作为子查询之外的列移动时，它无法识别“标题”或“正文”字段。我还需要做什么？我已经尝试将案例语句作为子查询并加入它，我已经阅读了尽可能多的类似问题并尝试了一些事情，但它们似乎都比我的问题更复杂。

SELECT year, ROUND(avg(view_count),2) AS Average_Number
  from
   (
      select extract(YEAR from creation_date) as year, count
           , CASE
             WHEN upper (title) like'%AGREE%' or upper (body)like '%AGREE%'
             THEN'YES'
             ELSE 'NO'
             END AS Statement
        from posts_questions
       where upper (title) like'%AGREE%'
          or upper (body)like '%AGREE%'
          or upper (title) like'%DISAGREE%' or upper (body)like '%DISAGREE%'
       group by count, creation_date, title, body
       order by year desc
   ) x
 group by year, Statement
 order by year
;

标签： sql

解决方案

由于 'AGREE' 是 'DISAGREE' 的子字符串，我稍微调整了您的逻辑。

在注意到'DISAGREE' LIKE '%AGREE%'它是LIKE '%DISAGREE%'.

我还表明，根据您当前的要求，这可以在没有子查询的情况下完成。

注意：某些数据库可能无法直接在GROUP BY子句中使用派生列名。所以你可能需要调整它。使用您正在使用的特定数据库标记您的问题。

只是用于测试的子查询：

select extract(YEAR from creation_date) as year, count
     , CASE
       WHEN upper (title) like '%DISAGREE%'
         or upper (body)  like '%DISAGREE%'
       THEN 'NO'
       ELSE 'YES'
       END AS Statement
  from posts_questions
 where upper (title) like '%AGREE%'
    or upper (body)  like '%AGREE%'
    or upper (title) like '%DISAGREE%'
    or upper (body)  like '%DISAGREE%'
 group by count, creation_date, title, body
 order by year desc
;

仅子查询的结果（调整后）：

+------+-------+-----------+
| year | count | Statement |
+------+-------+-----------+
| 2021 |     2 | NO        |
| 2021 |     4 | YES       |
| 2021 |     3 | YES       |
| 2021 |     3 | NO        |
| 2020 |     2 | NO        |
+------+-------+-----------+

没有子查询的解决方案：

select extract(YEAR from creation_date) as year
     , AVG(count) AS avg_count
     , CASE
       WHEN upper (title) like '%DISAGREE%'
         or upper (body)  like '%DISAGREE%'
       THEN 'NO'
       ELSE 'YES'
       END AS Statement
  from posts_questions
 where upper (title) like '%AGREE%'
    or upper (body)  like '%AGREE%'
    or upper (title) like '%DISAGREE%'
    or upper (body)  like '%DISAGREE%'
 group by year, Statement
 order by year desc
;

结果：

+------+-----------+-----------+
| year | avg_count | Statement |
+------+-----------+-----------+
| 2021 |    2.5000 | NO        |
| 2021 |    3.5000 | YES       |
| 2020 |    2.0000 | NO        |
+------+-----------+-----------+

子查询的解决方案。注意：GROUP BY如果表中的多个条目具有相同的值元组，则子查询中的 可能（逻辑上）有问题：(count, creation_date, title, body)

SELECT year
     , AVG(count) AS avg_count
     , CASE
       WHEN upper (title) like '%DISAGREE%'
         or upper (body)  like '%DISAGREE%'
       THEN 'NO'
       ELSE 'YES'
       END AS Statement
  FROM (
        select extract(YEAR from creation_date) as year, count
             , title
             , body
          from posts_questions
         where upper (title) like '%AGREE%'
            or upper (body)  like '%AGREE%'
            or upper (title) like '%DISAGREE%'
            or upper (body)  like '%DISAGREE%'
         group by count, creation_date, title, body
       ) x
 GROUP BY year, Statement
 ORDER BY year desc
;

结果：

+------+-----------+-----------+
| year | avg_count | Statement |
+------+-----------+-----------+
| 2021 |    2.5000 | NO        |
| 2021 |    3.5000 | YES       |
| 2020 |    2.0000 | NO        |
+------+-----------+-----------+

推荐阅读