仅当总行数等于具有特殊 ID 的行数时才选择计数值

发布于 2024-12-09 12:19:10 字数 490 浏览 0 评论 0原文

我需要选择具有特定 ID 的总行数 IF 它等于表中的总最大行数。

我在此示例中使用变量，但我想仅使用一个 select 语句来完成此

DECLARE @a int, @b int

--total with special field (redcar = yes)
SELECT @a = (SELECT COUNT(*) FROM dbo.car WHERE redcar = 'yes') 

-- max total of table
SELECT @b = (SELECT COUNT(*) FROM dbo.car) 

IF(@a = @b)
BEGIN
SELECT @a
END

操作 dbo.car

id  redcar
1    yes
2    
3    yes
4
5
6

输出应该为 0，因为红色汽车不等于表的总行数

原文

I need to select the the total number of rows with a certain ID IF it equals the total max rows in table.

I am using variables in this example, but I would like to do it with only one select statement

DECLARE @a int, @b int

--total with special field (redcar = yes)
SELECT @a = (SELECT COUNT(*) FROM dbo.car WHERE redcar = 'yes') 

-- max total of table
SELECT @b = (SELECT COUNT(*) FROM dbo.car) 

IF(@a = @b)
BEGIN
SELECT @a
END

EXAMPLE
dbo.car

id  redcar
1    yes
2    
3    yes
4
5
6

The output should be 0 because red car doesn't equal total row count of table

分享到QQ

分享到微博

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

发布评论

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

花海 2024-12-16 12:19:10

我能想到的最快的方法：

SELECT COUNT(*)
FROM dbo.car
WHERE NOT EXISTS (SELECT 1 FROM dbo.CAR where COALESCE(redcar, '') <> 'yes')

既然你想返回所有行，这只是检查是否有任何行不满足你的条件，如果是的话，它应该返回 0 或什么都不返回。

The fastest way I can think of:

SELECT COUNT(*)
FROM dbo.car
WHERE NOT EXISTS (SELECT 1 FROM dbo.CAR where COALESCE(redcar, '') <> 'yes')

Since you want to return all rows, this just checks if any rows don't meet your condition, and if so it should return 0 or nothing.

回复收藏 0 原文

薄凉少年不暖心 2024-12-16 12:19:10

这应该可以解决问题：

SELECT ISNULL(b.numrows, 0) as numrows
FROM (
    SELECT COUNT(*) AS numrows FROM car
) a
LEFT JOIN (
    SELECT COUNT(*) AS numrows FROM car WHERE redcar = 'yes'
) b ON b.numrows = a.numrows

This should do the trick:

SELECT ISNULL(b.numrows, 0) as numrows
FROM (
    SELECT COUNT(*) AS numrows FROM car
) a
LEFT JOIN (
    SELECT COUNT(*) AS numrows FROM car WHERE redcar = 'yes'
) b ON b.numrows = a.numrows

回复收藏 0 原文

温柔女人霸气范 2024-12-16 12:19:10

我认为你可以做这样的事情：

SELECT count(*)
  FROM dbo.car
HAVING count(*) = sum (Case When redcar = 'yes' Then 1 Else 0 End)

或者这样：

SELECT allcars
  FROM (SELECT COUNT(*) redcars FROM dbo.car WHERE redcar = 'yes') as redcars
  cross join (SELECT COUNT(*) allcars FROM dbo.car) as allcars
 WHERE redcars = allcars

但是你为什么想要一个查询？使用像代码这样的变量更容易阅读。

I think you could do something like this:

SELECT count(*)
  FROM dbo.car
HAVING count(*) = sum (Case When redcar = 'yes' Then 1 Else 0 End)

or this:

SELECT allcars
  FROM (SELECT COUNT(*) redcars FROM dbo.car WHERE redcar = 'yes') as redcars
  cross join (SELECT COUNT(*) allcars FROM dbo.car) as allcars
 WHERE redcars = allcars

But why do you want one query? Doing with variables like your code is much more easy to read.

回复收藏 0 原文

池予 2024-12-16 12:19:10

如果匹配的行数与总行数不同，您可以使用 case 返回 0：

SELECT  case when count(*) = (select count(*) from dbo.car) then count(*)
        else 0
        end
FROM    dbo.car 
WHERE   redcar = 'yes'

这将返回包含 0 的一行如果不满足条件。 if 语句根本不会返回任何行集。您可能需要调整客户端代码来解决此问题。

You could use a case to return 0 if the matched row count is different from the total number of rows:

SELECT  case when count(*) = (select count(*) from dbo.car) then count(*)
        else 0
        end
FROM    dbo.car 
WHERE   redcar = 'yes'

This will return one row with 0 if the condition is not met. The if statement would return no rowset at all. You might have to adjust the client code to account for this.

回复收藏 0 原文

~没有更多了~