where子句的case不识别第二个case条件

ht4b089n 于 2021-06-15 发布在 Mysql

关注(0)|答案(2)|浏览(347)

下面的case子句我在一个内部连接中用作max value id上的where条件，case查询只根据第一个条件>lasteditor给出结果！“@example.com“不为空，则lasteditor！=”@example.com".
我想做的是，如果lasteditor！“@example.com“为空，则使用lasteditor=”@example.com“否则忽略@example.com 作为lasteditor并获取下一封lasteditor电子邮件

WHERE 
                    CASE WHEN 
                        lasteditor != "abc@example.com" IS NOT NULL
                            THEN
                                lasteditor != "abc@example.com"
                    WHEN
                        lasteditor != "abc@example.com" IS NULL
                            THEN 
                                lasteditor = "abc@example.com"
                    ELSE NULL END

我要做的是，在最新的行上进行内部连接，其中lasteditor不是@example.com但是，如果lasteditor不存在，则根本没有结果@example.com，然后用@example.com

INNER JOIN (SELECT MAX(id) AS maxid,dyna_id,LastEditor FROM t2
        WHERE 
                CASE WHEN t2.lasteditor != "abc@example.com" IS NOT NULL
                    THEN t2.lasteditor != "abc@example.com"
                    WHEN t2.lasteditor != "abc@example.com" IS NULL
                    THEN t2.lasteditor = "abc@example.com"
                    ELSE NULL END
    GROUP BY t2.dyna_id) AS history ON main.id = history.dyna_id

总结

WHERE t2.lasteditor = (CASE
                WHEN t2.lasteditor != 'abc@example.com' --- if rows exists --- THEN 'abc@example.com' (if rows exists after skipping rows where lasteditor is abc@example.com then use this case)
                WHEN t2.lasteditor != 'abc@example.com' --- if no rows exists --- THEN pick up the rows where 'abc@example.com' is the last editor
                END)

下面的代码做到了，我猜它做到了，这是一个巨大的表我正在工作，我希望它是显示正确的结果

WHERE
                        CASE
                            WHEN
                            lasteditor <> "abc@example.com" IS NOT NULL
                            THEN
                                lasteditor <> "abc@example.com"
                                ELSE
                                lasteditor <> "test"
                            END
                            GROUP BY
                            hist.dyna_id

下面的完整查询

SELECT
               "Total",
               COUNT(IF(main.case_status = "OPEN", 1, NULL)) AS "open_cases",
               COUNT( 
               CASE
                  WHEN
                     owner.attr_role = "TM" 
                     AND main.tm_perception > "0 Star" 
                     AND main.tm_perception < "3 Star" 
                  THEN
                     main.tm_perception 
                  WHEN
                     owner.attr_role = "TL" 
                     AND main.tl_perception > "0 Star" 
                     AND main.tl_perception < "3 Star" 
                  THEN
                     main.tl_perception 
                  WHEN
                     owner.attr_role = "SE" 
                     AND main.owner_perception > "0 Star" 
                     AND main.owner_perception < "3 Star" 
                  THEN
                     main.owner_perception 
                  WHEN
                     owner.attr_role = "SE2" 
                     AND main.owner_perception > "0 Star" 
                     AND main.owner_perception < "3 Star" 
                  THEN
                     main.owner_perception 
                  ELSE
                     NULL 
               END
            ) AS "bb", COUNT( 
               CASE
                  WHEN
                     owner.attr_role = "TM" 
                     AND main.tm_perception > "2 Star" 
                     AND main.tm_perception < "5 Star" 
                  THEN
                     main.tm_perception 
                  WHEN
                     owner.attr_role = "TL" 
                     AND main.tl_perception > "2 Star" 
                     AND main.tl_perception < "5 Star" 
                  THEN
                     main.tl_perception 
                  WHEN
                     owner.attr_role = "SE" 
                     AND main.owner_perception > "2 Star" 
                     AND main.owner_perception < "5 Star" 
                  THEN
                     main.owner_perception 
                  WHEN
                     owner.attr_role = "SE2" 
                     AND main.owner_perception > "2 Star" 
                     AND main.owner_perception < "5 Star" 
                  THEN
                     main.owner_perception 
                  ELSE
                     NULL 
               END
            ) AS "mb", COUNT( 
               CASE
                  WHEN
                     owner.attr_role = "TM" 
                     AND main.tm_perception > "4 Star" 
                     AND main.tm_perception < "6 Star" 
                  THEN
                     main.tm_perception 
                  WHEN
                     owner.attr_role = "TL" 
                     AND main.tl_perception > "4 Star" 
                     AND main.tl_perception < "6 Star" 
                  THEN
                     main.tl_perception 
                  WHEN
                     owner.attr_role = "SE" 
                     AND main.owner_perception > "4 Star" 
                     AND main.owner_perception < "6 Star" 
                  THEN
                     main.owner_perception 
                  WHEN
                     owner.attr_role = "SE2" 
                     AND main.owner_perception > "4 Star" 
                     AND main.owner_perception < "6 Star" 
                  THEN
                     main.owner_perception 
                  ELSE
                     NULL 
               END
            ) AS "tb", COUNT( 
               CASE
                  WHEN
                     owner.attr_role = "na" 
                  THEN
                     main.id 
                  ELSE
                     NULL 
               END
            ) AS "na" 
            FROM
               gt AS main 
               LEFT JOIN
                  frontend_forms_users AS caseowner 
                  ON main.se_v_9 = caseowner.alias 
               INNER JOIN
                  (
                     SELECT
                        MAX(id) AS maxid,
                        dyna_id AS dn 
                     FROM
                        history_gt hist 
                     WHERE
                        CASE
                           WHEN
                              lasteditor <> "abc@example.com" IS NOT NULL 
                           THEN
                              lasteditor <> "abc@example.com" 
                           ELSE
                              lasteditor <> "test" 
                        END
                     GROUP BY
                        hist.dyna_id 
                  )
                  AS history 
                  ON main.id = dn 
               LEFT JOIN
                  history_gt AS owner2 
                  ON maxid = owner2.id 
               LEFT JOIN
                  frontend_forms_users AS owner 
                  ON owner2.LastEditor = owner.primary_email 
            UNION ALL
            SELECT
               caseowner.attr_lob AS lob,
               COUNT(IF(main.case_status = "OPEN", 1, NULL)) AS "open_cases",
               COUNT( 
               CASE
                  WHEN
                     owner.attr_role = "TM" 
                     AND main.tm_perception > "0 Star" 
                     AND main.tm_perception < "3 Star" 
                  THEN
                     main.tm_perception 
                  WHEN
                     owner.attr_role = "TL" 
                     AND main.tl_perception > "0 Star" 
                     AND main.tl_perception < "3 Star" 
                  THEN
                     main.tl_perception 
                  WHEN
                     owner.attr_role = "SE" 
                     AND main.owner_perception > "0 Star" 
                     AND main.owner_perception < "3 Star" 
                  THEN
                     main.owner_perception 
                  WHEN
                     owner.attr_role = "SE2" 
                     AND main.owner_perception > "0 Star" 
                     AND main.owner_perception < "3 Star" 
                  THEN
                     main.owner_perception 
                  ELSE
                     NULL 
               END
            ) AS "bb", COUNT( 
               CASE
                  WHEN
                     owner.attr_role = "TM" 
                     AND main.tm_perception > "2 Star" 
                     AND main.tm_perception < "5 Star" 
                  THEN
                     main.tm_perception 
                  WHEN
                     owner.attr_role = "TL" 
                     AND main.tl_perception > "2 Star" 
                     AND main.tl_perception < "5 Star" 
                  THEN
                     main.tl_perception 
                  WHEN
                     owner.attr_role = "SE" 
                     AND main.owner_perception > "2 Star" 
                     AND main.owner_perception < "5 Star" 
                  THEN
                     main.owner_perception 
                  WHEN
                     owner.attr_role = "SE2" 
                     AND main.owner_perception > "2 Star" 
                     AND main.owner_perception < "5 Star" 
                  THEN
                     main.owner_perception 
                  ELSE
                     NULL 
               END
            ) AS "mb", COUNT( 
               CASE
                  WHEN
                     owner.attr_role = "TM" 
                     AND main.tm_perception > "4 Star" 
                     AND main.tm_perception < "6 Star" 
                  THEN
                     main.tm_perception 
                  WHEN
                     owner.attr_role = "TL" 
                     AND main.tl_perception > "4 Star" 
                     AND main.tl_perception < "6 Star" 
                  THEN
                     main.tl_perception 
                  WHEN
                     owner.attr_role = "SE" 
                     AND main.owner_perception > "4 Star" 
                     AND main.owner_perception < "6 Star" 
                  THEN
                     main.owner_perception 
                  WHEN
                     owner.attr_role = "SE2" 
                     AND main.owner_perception > "4 Star" 
                     AND main.owner_perception < "6 Star" 
                  THEN
                     main.owner_perception 
                  ELSE
                     NULL 
               END
            ) AS "tb", COUNT( 
               CASE
                  WHEN
                     owner.attr_role = "na" 
                  THEN
                     main.id 
                  ELSE
                     NULL 
               END
            ) AS "na" 
            FROM
               gt AS main 
               LEFT JOIN
                  frontend_forms_users AS caseowner 
                  ON main.se_v_9 = caseowner.alias 
               INNER JOIN
                  (
                     SELECT
                        MAX(id) AS maxid,
                        dyna_id AS dn 
                     FROM
                        history_gt hist 
                     WHERE
                        CASE
                           WHEN
                              lasteditor <> "abc@example.com" IS NOT NULL 
                           THEN
                              lasteditor <> "abc@example.com" 
                           ELSE
                              lasteditor <> "test" 
                        END
                     GROUP BY
                        hist.dyna_id 
                  )
                  AS history 
                  ON main.id = dn 
               LEFT JOIN
                  history_gt AS owner2 
                  ON maxid = owner2.id 
               LEFT JOIN
                  frontend_forms_users AS owner 
                  ON owner2.LastEditor = owner.primary_email 
            GROUP BY
               lob 
            ORDER BY
               2 DESC

mysql

来源：https://stackoverflow.com/questions/54067176/where-clause-with-case-not-recognizing-the-second-case-condition

2条答案

按热度按时间

egmofgnx1#

你的问题，示例连接，子查询没有意义。。。话虽如此，让我试着这样重新问你的问题。如果我是对的，很好，如果不是，你的问题需要一些严肃的澄清。
这张table是“t2”。在它里面，对于任何给定的“dyna\u id”，您都有多个记录。它可以有一个或多个记录，并且每个“id”都是自动递增的。因此，对于给定的“dyna\u id”，您需要该键的最新记录。一旦您知道每个“dyna\u id”示例的最新“id”，您就想知道最后一个编辑记录的人是谁。对我来说，这意味着所有的记录都会有一个编辑文章的人，除非第一个记录是创建记录，并且有一个“addeditor”值，并且没有提供“lasteditor”值（但不要认为是这样）。
所以，要得到这个，它是一个由两部分组成的查询。首先，获取给定dyna\u id的最后一个id，然后获取与给定“id”匹配的最后一个编辑器的名称。最后，把这个答案和你的另一个加入main。。。

INNER JOIN 
   -- getting the Details for that last edited per Dyna_ID
   (select
         T2.Dyna_id,
         T2.ID,
         coalesce( T2.LastEditor, 'abc@example.com' ) as LastEditorPerDynaID
      from
         T2
            -- getting the last ID for any given Dyna_ID
            JOIN ( select
                         tmp1.dyna_id,
                         max(tmp1.id) as MaxIdPerDynaID
                      from
                         t2 tmp1
                      group by 
                         tmp1.dyna_id ) PQ
               on T2.Dyna_ID = PQ.Dyna_ID
              AND T2.ID = PQ.MaxIdPerDynaID ) history 
   ON main.id = history.dyna_id

澄清@example.com
您的要求@example.com. 下面是“t2”表的一些示例数据。

ID   Dyna_ID   LastEditor
 1    A         ME
 2    B         abc@example.com
 3    A         HER
 4    C         ME
 5    B         HIM 
 6    A         abc@example.com
 7    A         HIM
 8    C         HER
 9    B         abc@example.com
10    D         abc@example.com

从上面的数据中，我得到了唯一的dyna\u id值a，b和c，d。因此，来自原始查询的max（id）和最终对应的最后一个编辑器将导致

Dyna_ID  HighestID  LastEditor
A        7          HIM
B        9          abc@example.com
C        8          HER
D        10         abc@example.com

因此，如果您只查找最后一个编辑器不是@example.com，则希望“b”dynaid拉取“him”的id=5。但对于“d”王朝，他们唯一的记录是@lexample.com“，他们没有其他的，所以保留那个是可以的。
如果是这样，那么我的内部pq（预查询）将分别基于“”条目的任一条件获得最大id。因为我们总是会有至少一个条目，但要有或没有各自的最大id。。。

INNER JOIN 
   -- getting the Details for that last edited per Dyna_ID
   (select
         T2.Dyna_id,
         T2.ID,
         coalesce( T2.LastEditor, 'abc@example.com' ) as LastEditorPerDynaID
      from
         T2
            -- getting the last ID for any given Dyna_ID
            JOIN 
            ( select
                    PQ1.Dyna_ID,
                    case when PQ1.MaxIdNotAbcEditor > 0
                         then PQ1.MaxIdNotAbcEditor
                         else PQ1.MaxIdAsAbcEditor end as FinalJoinID
                 from
                    ( select
                            tmp1.dyna_id,
                            -- get max ID if NOT the 'abc' editor
                            max( case when NOT tmp1.LastEditor = 'abc@example.com' then tmp1.id else 0 end ) as MaxIdNotAbcEditor,
                            -- get max ID if IT IS the 'abc' editor
                            max( case when tmp1.LastEditor = 'abc@example.com' then tmp1.id else 0 end ) as MaxIdAsAbcEditor
                         from
                            t2 tmp1
                         group by 
                            tmp1.dyna_id ) PQ1 ) PQ
               on T2.Dyna_ID = PQ.Dyna_ID
              AND T2.ID = PQ.FinalJoinID ) history 
   ON main.id = history.dyna_id

基于第二个查询，它由三部分组成。最内部的“pq1”预查询1，对于任何给定的dyna\u id，我都将根据各自的部分获取最后一个id

Dyna_ID  MaxIdNotAbcEditor   MaxIdAsAbcEditor
A        7                   6
B        5                   9
C        8                   0
D        0                  10

可以看到，dynaid“c”没有“@”记录，所以它的最大id为 editor=0。。。与dynaid“d”相反。它只有一个“@”，所以非编辑器为0，编辑器为10。。跟我走这么远？这是正确的假设吗？
好啊。所以现在，我们对任何一个dyna id都有一个最大可能的分类。因此，现在，在创建仅由dyna id和max id组成的外部“pq”结果时，这将与case/一起结束。如果“i'm not the editor”列有一个值，请使用该值作为id。否则，获取唯一的其他选项。。。编辑器的条目。结果如下：

Dyna_ID   FinalJoinID
A         7
B         5
C         8
D         10

展开查看全部

赞(0）回复(0）举报 2021-06-16

csbfibhn2#

下面是一个可能有用的场景。主表是与作者的文章和对任何文章可以由作者或任何其他人评论。我只想显示不是作者的任何人的最新评论，除非只有作者发表了评论。
编辑
显示非作者的任何人的最新评论-除非只有作者评论，然后获取最新的作者评论。

CREATE TABLE article(
   id     INTEGER  NOT NULL PRIMARY KEY 
  ,author VARCHAR(100) NOT NULL
  ,title  VARCHAR(100) NOT NULL
);

INSERT INTO article(id,author,title)
VALUES 
  (1,'abc@example.com','a first article')
, (2,'def@example.com','beware of between')
, (3,'ghi@example.com','to be or not whatever');

CREATE TABLE comments(
   id         INTEGER  NOT NULL PRIMARY KEY 
  ,article_id INTEGER  NOT NULL
  ,author     VARCHAR(100) NOT NULL
  ,body VARCHAR(200) NOT NULL
);

INSERT INTO comments(id,article_id,author,body) 
VALUES
  (1,1,'abc@example.com','first comment')
, (2,1,'def@example.com','second comment')
, (3,1,'ghi@example.com','third comment')
, (4,1,'abc@example.com','2nd author comment')
, (5,3,'ghi@example.com','only comment')
;

相关查询方法（任何mysql版本）通过 order by case when c.author <> t.author then 1 else 2 end, id DESC 以及 limit ```
select
a.*
, c.*
from (
select
t.*
, (select id
from comments as c
where c.article_id = t.id
order by
case when c.author <> t.author then 1 else 2 end
, id DESC
limit 1) AS max_comment_id
from article as t
) a
left join comments as c on a.max_comment_id = c.id
;

id | author | title | max_comment_id | id | article_id | author | body
-: | :-------------- | :-------------------- | -------------: | ---: | ---------: | :-------------- | :------------
1 | abc@example.com | a first article | 3 | 3 | 1 | ghi@example.com | third comment
2 | def@example.com | beware of between | null | null | null | null | null
3 | ghi@example.com | to be or not whatever | 5 | 5 | 3 | ghi@example.com | only comment

分组子查询方法（任何mysql版本）

select
a.*
, c.*
from article a
left join (
select
c.article_id
, max(coalesce((case when c.author <> a.author then c.id end), (case when c.author = a.author then c.id end))) max_comment_id
from comments as c
inner join article a on c.article_id = a.id
group by
c.article_id
) d on a.id = d.article_id
left join comments c on d.max_comment_id = c.id
;

id | author | title | id | article_id | author | body
-: | :-------------- | :-------------------- | ---: | ---------: | :-------------- | :-----------------
1 | abc@example.com | a first article | 4 | 1 | abc@example.com | 2nd author comment
2 | def@example.com | beware of between | null | null | null | null
3 | ghi@example.com | to be or not whatever | 5 | 3 | ghi@example.com | only comment

行数（）方法的派生表（mysql 8以后）

select
a.*
, c.*
from article a
left join (
select
c.*
, row_number() over(partition by article_id
order by case when c.author <> a.author then 1 else 2 end, id DESC
) as rn
from comments as c
inner join article a on c.article_id = a.id
) c on a.id = c.article_id and c.rn = 1

id | author | title | id | article_id | author | body | rn
-: | :-------------- | :-------------------- | ---: | ---------: | :-------------- | :------------ | ---:
1 | abc@example.com | a first article | 3 | 1 | ghi@example.com | third comment | 1
2 | def@example.com | beware of between | null | null | null | null | null
3 | ghi@example.com | to be or not whatever | 5 | 3 | ghi@example.com | only comment | 1

公共表表达式和行数（）方法（mysql 8以后）

with max_comments as (
select
c.*
, row_number() over(partition by article_id
order by case when c.author <> a.author then 1 else 2 end, id DESC
) as rn
from comments as c
inner join article a on c.article_id = a.id
)
select
a.*
, c.*
from article a
left join max_comments c on a.id = c.article_id and c.rn = 1

db<>在这里摆弄

展开查看全部

赞(0）回复(0）举报 2021-06-16

我来回答

where子句的case不识别第二个case条件

2条答案

相关问题

热门标签

最新问答