本文解答了几个SQL难题，包括在线登陆时间显示格式调整、多表中外键删除、查询字段统一命名及日期条件导致的查询异常等问题，并提供了具体解决方案。
所以，觉得有必要记录下来，这样以后再次碰到这类问题，也能从中获取解答的思路。
1、如何解决用户在线登陆时间——的小时和分钟计算问题。http://bbs.youkuaiyun.com/topics/390613823
我想得到用户在线时长，格式是：08:00和08:43这种格式的在线时长结果。
我自己尝试查了sql的文档，也百度了很多。但是没有这方面的应用。
我自己也尝试写了很多但是不行。
我只能得到在线的总分钟数，或者总秒数。无法弄成想要的格式。
这是我的代码：
select ta.[user],(DATEDIFF(mi,ta.time,tb.time)) from
(select * from T1 where T1.operate='Login') as ta
inner join
(select * from T1 where T1.operate='Logout') as tb
on ta.[user]=tb.[user]
------------------------------------
或者这样写：
select ta.[user], cast(datediff(hour, ta.time, tb.time) as varchar) + ':' + cast(DATEDIFF(MINUTE, ta.time, tb.time) as varchar)
from
(select [user], [time] from T1 where [operate] = 'login') as ta
inner join
(select [user], [time] from T1 where [operate] = 'logout') as tb
on ta.[user] = tb.[user];
两种写法都无法实现想要的结果。请求大神指导下，帮忙给出一种解决方法。
我测试完，发效果图。
我的解法：
方法1：
drop table t1 create table T1 ( [user] varchar(30), operate varchar(10), time datetime ) insert into T1 select 'LiMing','Login','2010/10/24 8:03' union all select 'WangYi','Login','2010/10/24 8:14' union all select 'WangYi','Logout','2010/10/24 16:14' union all select 'LiMing','Logout','2010/10/24 16:14' select [user], cast(cast(round(interval * 1.0 / 60,0,1) as int) as varchar) + ':' + case when interval * 1.0 % 60 <> 0 then cast(cast(round(interval * 1.0 % 60,0,1) as int) as varchar) else '00' end from ( select T1.[user], DATEDIFF(MINUTE,t1.time,t2.time) as interval from T1 inner join T1 t2 on t1.[user] = t2.[user] and t1.operate = 'login' and t2.operate = 'logout' )a /* user (无列名) LiMing 8:11 WangYi 8:00 */
方法2：
--方法2. select [user], convert(varchar(5),DATEADD(MINUTE,interval,time),114) from ( select T1.[user], convert(varchar(10),t1.time,120) as time, DATEDIFF(MINUTE,t1.time,t2.time) as interval from T1 inner join T1 t2 on t1.[user] = t2.[user] and t1.operate = 'login' and t2.operate = 'logout' )a /* user (无列名) LiMing 08:11 WangYi 08:00 */
2、求助!SQLServer如何删除多表(10表以上)中的同一个外键。
http://bbs.youkuaiyun.com/topics/390633551
最近在做一个OA系统，用SQLServer2008数据库。系统里有个删除员工的功能，但是考虑到其他表中有员工表的外键，删除时应该将其他表中相关的记录全部删除，请问如何写语句？
比如员工表是OA_User，员工编号Uid，数据库中最少有10张表里有Uid外键，请问如何删除其他表中的相关记录？
create table OA_User(Uid int primary key ,uname varchar(20)) create table OA_tb ( id int identity(1,1)primary key , Uid int foreign key references OA_User(Uid) on delete cascade --级联删除 ) insert into OA_User values(1,'张三'), (2,'李四') insert into OA_tb values(1),(1),(1),(2),(1) --删除主表中udi 为2的记录，没有报错 delete from oa_user where uid = 2 --附表中的uid为2的记录，自动删除 select * from oa_tb /* id Uid 1 1 2 1 3 1 5 1 */也可以通过图形化操作来实现：
3、统一改换查询出的字段。。这是不是想多了？
http://bbs.youkuaiyun.com/topics/390610092
能不能这样
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
下面是通过动态语句来实现的：
--先建个表 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)
4、关于日期条件出现的奇怪问题。
http://bbs.youkuaiyun.com/topics/390498925
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的'号而已，这个大家能理解是什么问题吗？
if OBJECT_ID('t') is not null drop table t go create table t(d datetime) insert into t select cast('2013-05-01' as datetime) as d union all select cast('2013-05-10' as datetime) /* 1. 通过查询计划能看出，SQL Server把下面的查询转化成了： select * from t where d >= convert(datetime,2013/5/1,0) and d <= convert(datetime,'2013/6/24',0) 也就是查询条件： d >= convert(datetime,2013/5/1,0) and d <= convert(datetime,'2013/6/24',0) 进一步转化： d >= '1901-02-07 00:00:00.000' and d <= 2013-06-24 00:00:00.000 这样就能查询出结果集。 */ select * from t where d between convert(datetime,2013/5/1) and convert(datetime,'2013/6/24') /* 2. 通过查询计划能看出，SQL Server把下面的查询转化成了： select * from t where d >= convert(datetime,2013/5/1,0) and d <= convert(datetime,2013/6/24,0) 也就是查询条件： d >= convert(datetime,2013/5/1,0) and d <= convert(datetime,2013/6/24,0) 进一步转化： d >= '1901-02-07 00:00:00.000' and d <= '1900-01-14 00:00:00.000' 由于SQL Server 把2013/6/24中的斜杠，当成了除号，也就是按除法计算了， 比如：2013/6/24 就等于13，那么由于datetime默认值是默认值： 1900-01-01 00:00:00， 那么加上13后，就是1900-01-14 00:00:00.000，这样后就查不出结果了. */ select * from t where d between convert(datetime,2013/5/1) and convert(datetime,2013/6/24) 其实就是，
2013/5/1 就是一个除法运算，结果为402。
2013/6/24 做除法运算后，就是13。
由于datetime数据类型的默认值为：'1900-01-01 00:00:00'，
所以上面的convert(datetime,2013/5/1)就是'1900-01-01 00:00:00' 再加上402，
就是'1901-02-07 00:00:00.000',
而convert(datetime,2013/6/24)就是是'1900-01-01 00:00:00' 再加上 13，
就是'1900-01-14 00:00:00.000',
所以就会查不出结果来。
所以，上面的2013/5/1 要写成 '2013/5/1',一定要加上引号。
转载于:https://blog.51cto.com/yupeigu/1367962
扫一扫
1307
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
