学步园

所以，觉得有必要记录下来，这样以后再次碰到这类问题，也能从中获取解答的思路。

1、如何解决用户在线登陆时间——的小时和分钟计算问题。http://bbs.csdn.net/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.csdn.net/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、统一改换查询出的字段。。这是不是想多了？

--先建个表
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) 

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)