如何重新编写case表达式以避免此特定错误消息？

kmb7vmvb 于 2021-07-24 发布在 Java

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

我正在使用sql server 2014。我对一个表（t1）运行了下面的t-sql查询。表t1摘录如下：

ID   N1   N2   N3   N4   N5   N6
1    2    10   12   25   29   30
2    10   13   23   24   35   39
3    1    20   23   26   32   40
4    5    9    11   12   28   35
...

预期产量：
我希望查询的输出能够找到当前[id]的值[n1]到[n6]是否存在于上面2个[id]级别的任何值中。
为了简化，查询需要找出[id]=4处的值（[n1]到[n6]）是否存在于[id]-2处的值中；等于[id]=2

ID   N1   N2   N3   N4   N5   N6   N1_ID-2   N2_ID-2   N3_ID-2   N4_ID-2   N5_ID-2   N6_ID-2
1    2    10   12   25   29   30     0         0         0         0         0         0
2    10   13   23   24   35   39     0         0         0         0         0         0
3    1    20   23   26   30   40     0         0         0         0         1         0
4    5    9    11   13   28   35     0         0         0         1         0         1
...

目前我的问题如下：

USE MyDatabase

SELECT *,        
    (CASE WHEN [N1] IN (SELECT [N1], [N2], [N3], [N4], [N5], [N6] FROM [T1] WHERE [ID] = [ID] -2) THEN 1 ELSE 0 END) AS [N1_ID-2],
    (CASE WHEN [N2] IN (SELECT [N1], [N2], [N3], [N4], [N5], [N6] FROM [T1] WHERE [ID] = [ID] -2) THEN 1 ELSE 0 END) AS [N2_ID-2],
    (CASE WHEN [N3] IN (SELECT [N1], [N2], [N3], [N4], [N5], [N6] FROM [T1] WHERE [ID] = [ID] -2) THEN 1 ELSE 0 END) AS [N3_ID-2],
    (CASE WHEN [N4] IN (SELECT [N1], [N2], [N3], [N4], [N5], [N6] FROM [T1] WHERE [ID] = [ID] -2) THEN 1 ELSE 0 END) AS [N4_ID-2],
    (CASE WHEN [N5] IN (SELECT [N1], [N2], [N3], [N4], [N5], [N6] FROM [T1] WHERE [ID] = [ID] -2) THEN 1 ELSE 0 END) AS [N5_ID-2],
    (CASE WHEN [N6] IN (SELECT [N1], [N2], [N3], [N4], [N5], [N6] FROM [T1] WHERE [ID] = [ID] -2) THEN 1 ELSE 0 END) AS [N6_ID-2]        
FROM [T1]

运行上述代码集时会显示以下错误消息：
当子查询没有引入exists时，只能在选择列表中指定一个表达式。
如何更正代码以避免此错误？

sql sql-server tsql case

来源：https://stackoverflow.com/questions/62980273/how-to-re-write-my-case-expression-to-avoid-this-specific-error-message

2条答案

按热度按时间

szqfcxe21#

可以使用self-join和 IN 这样地：

select t.*,
       (case when t.n1 in (tprev.n1, tprev.n2, tprev.n3, tprev.n4, tprev.n5, tprev.n6) then 1 else 0 end) n1_comp,
       (case when t.n2 in (tprev.n1, tprev.n2, tprev.n3, tprev.n4, tprev.n5, tprev.n6) then 1 else 0 end) as n2_comp,
       . . . 
from t left join
     t tprev
     on tprev.id = t.id - 2

赞(0）回复(0）举报 2021-07-24

798qvoo82#

select
  t1.*,
  case when t1.N1 in (t1_old.N1, t1_old.N2, t1_old.N3, t1_old.N4, t1_old.N5, t1_old.N6) then 1 else 0 end as [N1_ID-2],
  case when t1.N2 in (t1_old.N1, t1_old.N2, t1_old.N3, t1_old.N4, t1_old.N5, t1_old.N6) then 1 else 0 end as [N2_ID-2],
  ...
from
  t1
  left join t1 as t1_old on t1_old.id = t1.id - 2

赞(0）回复(0）举报 2021-07-24

我来回答

如何重新编写case表达式以避免此特定错误消息？

2条答案

相关问题

热门标签

最新问答