一些比较难的sql问题：2(三)
then cast(CONVERT(varchar(10),end_time,120) + ' 18:00:00' AS datetime)
else end_time
end as end_time_temp
FROM tab
) t
inner join calendar c
on convert(varchar(10),t.start_time,120) <= c.r
and convert(varchar(10),t.end_time,120) >= c.r
left join holiday h
on c.r = h.h_date
--OPTION(MAXRECURSION 1000) --限制最大递归次数
)
--select * from tt
select start_time,
end_time,
--汇总秒数，同时转化为小时
cast(round(SUM(seconds) / 3600 ,1,1) as numeric(10,1)) as diff_time
from tt
group by start_time,
end_time
/*
start_time end_time diff_time
2013-09-09 10:59:59.837 2013-09-09 14:06:21.223 1.1
2013-09-13 16:28:29.833 2013-09-16 09:41:47.987 2.7
2013-09-18 17:02:40.443 2013-09-22 15:27:58.983 5.9
2013-09-18 08:21:32.037 2013-09-22 15:31:52.500 12.5
2013-09-11 17:26:02.383 2013-09-24 10:38:01.410 55.1
*/
2、统一改换查询出的字段。。这是不是想多了？
能不能这样
select A.* as A_*
from QAQuestion Q
inner join QAAnswer A ON A.QuestionID = Q.ID
简单地说，不想一个个地去给每个字段as别名
我如上去写只是打个比方。。实际运行不了的，想得到的查询结果是
A_字段1,A_字段2,A_字段3,A_字段4
有没有办法呢？
我的回复：
本质上来说，只有在sql server端，能把select a.* as a_*,也就是自动进行转换，才能支持。
因为在sql server端，你写的sql语句是各式各样的，要想实现你的A.* as A_*，实际上就是要改写查询，改为：
select a.字段1 as a_字段1，
a.字段2 as a_字段2，
a.字段3 as a_字段3，
from a
下面是通过动态语句来实现的：
[sql]
--先建个表
select * into wc_table
from sys.objects
/*
要实现
select a.* as a_*
from wc_table
的效果
*/
--动态生成语句为:
declare @sql varchar(max);
set @sql = '';
select @sql = @sql + ',' + c.name + ' as A_' + c.name
from sys.tables t
inner join sys.columns c
on t.object_id = c.object_id
where t.name = 'wc_table'
order by c.column_id
set @sql = 'select ' +
STUFF(@sql,1,1,'') +
' from wc_table A'
select @sql
/*
我把结果格式化了一下就是这样：
SELECT name AS A_name,
object_id AS A_object_id,
principal_id AS A_principal_id,
schema_id AS A_schema_id,
parent_object_id AS A_parent_object_id,
type AS A_type,
type_desc AS A_type_desc,
create_date AS A_create_date,
modify_date AS A_modify_date,
is_ms_shipped AS A_is_ms_shipped,
is_published AS A_is_published,
is_schema_published AS A_is_schema_published
FROM wc_table A
*/
exec(@sql)
3、关于日期条件出现的奇怪问题。
select top 1 ekeyid,ProbeMaterial,Result,LabTestDate from PatientLabTestResults
where ProbeMaterial='Ca'
and LabTestDate between convert(datetime,2013/1/1) and
convert(datetime,'2013/6/24') order by LabTestDate desc
这样查的出结果，变成
select top 1 ekeyid,ProbeMaterial,Result,LabTestDate from PatientLabTestResults
where ProbeMaterial='Ca'
and LabTestDate between convert(datetime,2013/1/1) and
convert(datetime,2013/6/24) order by LabTestDate desc
这样后就查不出结果了，其实只是去除了2013/6/24的'号而已，这个大家能理解是什么问题吗？
[sql]
if OBJECT_ID('t') is not null
drop table t
go
create table t(d datetime