从一个ASP SQL语句返回多个计数结果

您可以这样做:

select
  sum(case when Field1 <> '' then 1 else 0 end) as Field1Count,
  sum(case when Field2 <> '' then 1 else 0 end) as Field2Count,
  sum(case when Field3 <> '' then 1 else 0 end) as Field3Count,
  sum(case when Field4 <> '' then 1 else 0 end) as Field4Count
from TheTable

count() 将不包括空字段。具有每行函数的所有解( if , case , coalesce 相对的 术语来说,即使您认为数据库很大,它对您的数据库仍然是可以接受的-我在一家商店工作,那里数百万行的大小与我们的配置表一样:-)

然后您可以使用:

select
    count(field1) as count1,
    count(field2) as count2,
    count(field3) as count3,
    count(field4) as count4
from ...

count(distinct fieldX) 当然,对于不同的值)。

如果这是一种可行的方式,您可以通过以下方式设置您的桌子:

update tbl set field1 = NULL where field1 = '';
update tbl set field2 = NULL where field2 = '';
update tbl set field3 = NULL where field3 = '';
update tbl set field4 = NULL where field4 = '';

测量,不要猜测。 并在目标环境(或合适的副本)中测量。经常测量。数据库优化不是一个“设置即忘”操作。

下面是我将如何使用MySQL来实现它

select sum(CASE WHEN Field1 <>'' THEN 1 ELSE 0 END) as Field1Count 
     , sum(CASE WHEN Field2 <>'' THEN 1 ELSE 0 END) as Field2Count 
     , sum(CASE WHEN Field3 <>'' THEN 1 ELSE 0 END) as Field3Count 
     ...
     , sum(CASE WHEN FieldN <>'' THEN 1 ELSE 0 END as FieldNCount 
  from DataTable