SQL Server查询带有多个何种情况的情况

发布于 01-20 04:24 字数 401 浏览 3 评论 0原文

我正在尝试编写一个SQL选择,

SELECT CASE WHEN table1.col1 IS NOT NULL 
AND  table2.col1='someValue' THEN 'YES'
WHEN main.col2 IS NOT NULL 
AND table2.col1 NOT LIKE '%Rejected%' THEN 'YES'
        ELSE NULL END AS eligible,table1.col13,table2.col14 
        FROM table1 
        INNER JOIN table2 ON table1.col5=table2.col6     
        WHERE eligible='YES'

但要获得无效的列名称“合格”,请建议

I am trying to write a sql select like below

SELECT CASE WHEN table1.col1 IS NOT NULL 
AND  table2.col1='someValue' THEN 'YES'
WHEN main.col2 IS NOT NULL 
AND table2.col1 NOT LIKE '%Rejected%' THEN 'YES'
        ELSE NULL END AS eligible,table1.col13,table2.col14 
        FROM table1 
        INNER JOIN table2 ON table1.col5=table2.col6     
        WHERE eligible='YES'

but getting Invalid column name 'eligible', please advice

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

烟酒忠诚2025-01-27 04:24:25

您可能要寻找的不是符合条件的地方。相反,它是符合条件的:

SELECT CASE WHEN table1.col1 IS NOT NULL AND  table2.col1='someValue'  
                THEN 'YES'
            WHEN main.col2 IS NOT NULL AND table2.col1 NOT LIKE '%Rejected%' 
                THEN 'YES'
           ELSE NULL END AS eligible,
      table1.col13,
      table2.col14 
FROM table1 
INNER JOIN table2 ON table1.col5=table2.col6     
HAVING eligible='YES';  /*here*/

由于列合格实际上是您case> case表达式的别名,并且不是来自任何表格t在其中中使用它。

如果您更熟悉使用where,则首先将基本查询作为子查询来解决:

SELECT * 
  FROM
( SELECT CASE WHEN table1.col1 IS NOT NULL AND  table2.col1='someValue'  
                THEN 'YES'
            WHEN main.col2 IS NOT NULL AND table2.col1 NOT LIKE '%Rejected%' 
                THEN 'YES'
           ELSE NULL END AS eligible,
      table1.col13,
      table2.col14 
FROM table1 
INNER JOIN table2 ON table1.col5=table2.col6 ) AS subquery
WHERE eligible='YES';

What you probably looking for is not WHERE eligible. Instead it's HAVING eligible:

SELECT CASE WHEN table1.col1 IS NOT NULL AND  table2.col1='someValue'  
                THEN 'YES'
            WHEN main.col2 IS NOT NULL AND table2.col1 NOT LIKE '%Rejected%' 
                THEN 'YES'
           ELSE NULL END AS eligible,
      table1.col13,
      table2.col14 
FROM table1 
INNER JOIN table2 ON table1.col5=table2.col6     
HAVING eligible='YES';  /*here*/

Since the column eligible is actually an alias of your CASE expression and is not from any of your tables then you can't use it in WHERE.

If you're more familiar with using WHERE, here's a workaround by making the base query as subquery first:

SELECT * 
  FROM
( SELECT CASE WHEN table1.col1 IS NOT NULL AND  table2.col1='someValue'  
                THEN 'YES'
            WHEN main.col2 IS NOT NULL AND table2.col1 NOT LIKE '%Rejected%' 
                THEN 'YES'
           ELSE NULL END AS eligible,
      table1.col13,
      table2.col14 
FROM table1 
INNER JOIN table2 ON table1.col5=table2.col6 ) AS subquery
WHERE eligible='YES';
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文