未使用EXISTS嵌套case语句引入子查询时，只能在选择列表中指定一个表达式 [英] Only one expression can be specified in the select list when the subquery is not introduced with EXISTS nested case statements

问题描述

我正在尝试创建一个查询，该查询获取事件在下面打开的小时数，这是我的查询.我使用的是case语句，因为它只需要考虑平日的时间.这是该过程的一个步骤，我的总体目标是实际获得那几天的时间.因此，例如，如果天数多于那几天，则乘以8..如果小于1，则执行datediff小时，然后获取该天的小时数.任何帮助将不胜感激！

I am trying to create a query that gets the number of hours an event was open below is my query. I am using case statements because it needs to take into account to only count weekdays. This is a step in the process my overall goal is to actually get the hours for those days. So for example if the the days is more than one count all those days and multiply by 8.. if its less than one do a datediff hours and just get the hours for that day.. Any help would be greatly appreciated!

但出现以下错误:

当未使用EXISTS引入子查询时，只能在选择列表中指定一个表达式.

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

DECLARE @workdays int
SELECT creationDateTime, 
       closedDateTime,DATEDIFF(dd, creationDateTime, closedDateTime)+1,
       CASE WHEN (DATEDIFF(dd, creationDateTime, closedDateTime)+1 > 1)
            THEN (
                  SELECT creationDateTime ,closedDateTime, 
                    ((DATEDIFF(dd, creationDateTime, closedDateTime)+1)
                      -(DATEDIFF(wk, creationDateTime, closedDateTime) * 2)
                      -(CASE WHEN DATENAME(dw, creationDateTime)
                        = 'Sunday' THEN 1 ELSE 0 END)
                      -(CASE WHEN DATENAME(dw, closedDateTime) 
                        = 'Saturday' THEN 1 ELSE 0 END)
                  )*8 AS workdayhours
                  FROM table.ofevents where closedDateTime IS NOT NULL) END
FROM table.ofevents where closedDateTime IS NOT NULL

推荐答案

正如错误所述，您不能在一个值中选择多个列.在执行选择语句时:

As the error said, you cannot select more than one column into a value. When doing a select statement:

SELECT A, B, C ...

每个表达式A，B，C表示要返回的数据的列.该列由具有integer或double或varchar数据类型的值组成.

Each expression A, B, C, represents a column of data to be returned. That column is composed of values that have a data type like integer or double or varchar.

因此，当您这样做时:

SELECT A, B, (SELECT C, D FROM ...) ...

您说的是我想要一列As，一列Bs和一列..."，糟糕.您刚刚破坏了SQL，因为它不知道如何在单个单元格中放置多个值. (好吧，有些SQL变体确实知道如何做到这一点，但这并不总是那么简单.)

You are saying "I want a column of As, a column of Bs, and a column of ..." Oops. You just broke SQL because it doesn't know how to put multiple values in a single cell like that. (Well, some SQL variants do know how to do that, but it is not always so simple.)

您可能想要的是:

SELECT A, B, (SELECT C FROM ...), (SELECT D FROM ...) etc.

所以，这意味着像这样的东西:

So, that means something like this:

declare @workdays int


 SELECT creationDateTime

      , closedDateTime

      , DATEDIFF(dd, creationDateTime, closedDateTime)+1

      , CASE WHEN (DATEDIFF(dd, creationDateTime, closedDateTime)+1 > 1)
          THEN 
           (SELECT creationDateTime
              as workdayhours
              from table.ofevents where closedDateTime IS NOT NULL)
          END

      , CASE WHEN (DATEDIFF(dd, creationDateTime, closedDateTime)+1 > 1)
          THEN 
           (SELECT closedDateTime
              as workdayhours
              from table.ofevents where closedDateTime IS NOT NULL)
          END

      , CASE WHEN (DATEDIFF(dd, creationDateTime, closedDateTime)+1 > 1)
          THEN ( (DATEDIFF(dd, creationDateTime, closedDateTime)+1 )
                -(DATEDIFF(wk, creationDateTime, closedDateTime) * 2)
                -(CASE WHEN DATENAME(dw, creationDateTime) = 'Sunday' THEN 1 ELSE 0 END)
                -(CASE WHEN DATENAME(dw, closedDateTime) = 'Saturday' THEN 1 ELSE 0 END))*8
              as workdayhours
              from table.ofevents where closedDateTime IS NOT NULL)
          END

   FROM table.ofevents where closedDateTime IS NOT NULL

看看每个子查询如何只选择一个单个值?

See how each subquery only selects a single value?

但是，我仍然不明白为什么需要选择"creationDateTime"和"closeDateTime"，因此您可以删除这些列.

But, I still do not see why you need to select the "creationDateTime" and "closeDateTime", so you can probably remove those columns.

最后一条建议，因为我经常在人们的SQL相关问题中看到这一点.问问自己，真的很深刻，真的很彻底， 为什么 ，您想让SQL做些什么.您为什么输入:

One last piece of advice, because I see this often in people's SQL related questions. Ask yourself, really deeply, really thoroughly, why you want SQL to do something. Why did you type:

   (SELECT creationDateTime ,closedDateTime, 
     ( (DATEDIFF(dd, creationDateTime, closedDateTime)+1 )

为什么creationDateTime就在closeDateTime之前，是什么意思? SQL是一种声明性语言，您可以写下您的意图，数据库的工作就是生成与您的意图相匹配的数据.这与大多数强制性编程语言(例如C)不同.在C中，您写下了如何做某事.对于大多数了解命令性语言的人来说，这使理解SQL变得非常困难.因此，问问自己，为什么创建该子查询，您的目标是什么?

Why is that creationDateTime there right before closeDateTime, what does it mean? SQL is a declarative language, you write down your intent and it is the job of the database to produce the data that matches your intent. This is unlike most programming languages which are imperative, like C. In C, you write down how to do something. This makes understanding SQL very difficult for people who mostly understand imperative languages. So, ask yourself, why did you create that subquery, what is your goal?

如果我的答案不够用，请编辑您的问题，以澄清您想要结果查询外观的内容.那很重要如果您不告诉我们查询将返回什么，您如何希望告诉数据库，或者希望我们告诉您如何告诉数据库?

If my answer does not suffice, please edit your question to clarify what you want your result query to look like. That's very important. If you don't tell us what you expect the query to return, how can you expect to tell the database that, or expect us to tell you how to tell the database that?

这篇关于未使用EXISTS嵌套case语句引入子查询时，只能在选择列表中指定一个表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！