我有一张桌子叫xDays
设置如下:
╔══════════════╦═════════════════════════╦═══════╗
║ Project_Name ║ Date ║ Hours ║
╠══════════════╬═════════════════════════╬═══════╣
║ proj1 ║ 2010-03-03 00:00:00.000 ║ 0 ║
║ proj1 ║ 2010-03-04 00:00:00.000 ║ 0 ║
║ proj1 ║ 2010-03-05 00:00:00.000 ║ 0 ║
║ proj2 ║ 2010-03-03 00:00:00.000 ║ 1 ║
║ proj2 ║ 2010-03-04 00:00:00.000 ║ 0 ║
║ proj2 ║ 2010-03-05 00:00:00.000 ║ 0 ║
╚══════════════╩═════════════════════════╩═══════╝
我想做的是选择每个Project_Name
谁总共在Hours
列是0
。在上面的示例中,select 语句应返回proj1
,由于总计hours
因为它是 0,而总小时数proj2
is 1.
我得到的最接近的是:
select sum(hours) as 'total', project_name
From xDays
group by project_name
order by project_name
这给了我一个表格,显示每个项目名称的总小时数,其中确实显示了0
某些项目名称的总小时数。从这里开始,我尝试了一些不同的事情,要么得到
将 varchar 值转换为数据类型 int 时转换失败
错误或空结果。我尝试过的一些例子:
select sum(hours) as 'total', project_name
From xDays
where 'total' = convert(varchar(10), 0)
group by project_name
order by project_name`
这将返回空结果。
select sum(hours) as 'total', project_name
From xDays
where 'total' = 0
group by project_name
order by project_name
这会返回一个转换错误(无法将 varchar Total 转换为 int)。
我怎样才能让它正常工作?