SQL Server Sql query with multi return options

eqqqjvef  于 2023-04-04  发布在  其他
关注(0)|答案(2)|浏览(142)

I'm trying to build query that return 0 or more and return -1 if Id isn't exists For example:

Select Count(*)
From my table
Where id = inputId

I tried to use case like this:

Select
  CASE WHEN Count(*) >= 0 THEN Count(*) 
   ELSE -1 END
From .....
Where....

my issue is that 0 is valid result that mean Id exist with 0 columns, I need to return -1 if id isn't exists

fkaflof6

fkaflof61#

Use a caseexpression to:

  1. If there are values in that other column, return the count.
  2. If no value in that other column, return 0 if id still exists.
  3. None of the above exist, return -1.
select case when count(othercolumn) > 0 then count(id)   -- or perhaps "then count(othercolumn)"
            when count(id) > 0 then 0
            else -1
       end
From my table
Where id = inputId
xzabzqsa

xzabzqsa2#

I'm reading through several lines here, but at a pure guess what you are actually want is something like:
"I want a COUNT of the number of rows for a specific ID where another column has a specific value. If there are no rows for the ID I want to return -1 , however, if there are no rows where the other column has a specific value, bit there are rows for the ID, then 0 should be returned."

If this is the correct interpretation, then you could use a subquery and then within the subquery GROUP BY the ID, even though it will still be a single distinct group; this means that if the ID doesn't exist no rows are returned. To ensure that rows are returned regardless of the value of the other column, you'll need to use conditional aggregation. This results in something like this:

SELECT ISNULL((SELECT COUNT(CASE OtherColumn WHEN @YourValue THEN 1 END)
               FROM dbo.YourTable
               WHERE ID = @ID
               GROUP BY ID),-1);

The ISNULL then means that when no rows are returned, which would result in NULL , -1 is then returned.

相关问题