最近，在论坛中，遇到了不少比较难的sql问题，虽然自己都能解决，但发现过几天后，就记不起来了，也忘记解决的方法了。
所以，觉得有必要记录下来，这样以后再次碰到这类问题，也能从中获取解答的思路。
1、分组查询问题
http://bbs.csdn.net/topics/390619682?page=1#post-395835328
例子表结构数据如下：id status date price
1 1 2013-10-01 218
2 1 2013-10-02 218
3 0 2013-10-03 218
4 0 2013-10-04 238
5 0 2013-10-05 238
6 0 2013-10-06 238
7 0 2013-10-07 258
8 0 2013-10-08 258
9 0 2013-10-09 218
想获取的结果集一：
2013-10-01至2013-10-03 218
2013-10-04至2013-10-06 238
2013-10-07至2013-10-08 258
2013-10-09至2013-10-09 218
想获取的结果集二：
1 2013-10-01至2013-10-02 218
0 2013-10-03至2013-10-03 218
0 2013-10-04至2013-10-06 238
0 2013-10-07至2013-10-08 258
0 2013-10-09至2013-10-09 218
我的解法：
--drop table tbcreate table tb(id int,status int,date varchar(10),price int)insert into tbselect 1, 1, '2013-10-01', 218 union allselect 2, 1, '2013-10-02', 218 union allselect 3, 0, '2013-10-03', 218 union allselect 4, 0, '2013-10-04', 238 union allselect 5, 0, '2013-10-05', 238 union allselect 6, 0, '2013-10-06', 238 union allselect 7, 0, '2013-10-07', 258 union allselect 8, 0, '2013-10-08', 258 union allselect 9, 0, '2013-10-09', 218 --union all--select 10, 0, '2013-10-10', 218 go--第一个结果集;with tas(select *, row_number() over(partition by price order by id) as rownum, min(id) over(partition by price) as min_idfrom tb ),ttas(select id, price, a.date, rownum - (id - min_id) as intervalfrom t a )select min(date) + '至' + max(date) as date, pricefrom ttgroup by price,intervalorder by 1/*date price2013-10-01至2013-10-03 2182013-10-04至2013-10-06 2382013-10-07至2013-10-08 2582013-10-09至2013-10-09 218*/--第2个结果集;with tas(select *, row_number() over(partition by status,price order by id) as rownum, min(id) over(partition by status,price) as min_idfrom tb ),ttas(select id, price, a.date, a.status, rownum - (id - min_id) as intervalfrom t a )select status,min(date) + '至' + max(date),pricefrom ttgroup by status,price,intervalorder by 2/*status date price1 2013-10-01至2013-10-02 2180 2013-10-03至2013-10-03 2180 2013-10-04至2013-10-06 2380 2013-10-07至2013-10-08 2580 2013-10-09至2013-10-09 218*/
2、字符串检索问题：http://bbs.csdn.net/topics/390608926
这是 http://bbs.csdn.net/topics/390530288 问题的 一个变种
表
ID IndexArr
1 1,2,3,4,5
2 55,6,99,87,1000
3 7,567567,567,43,123
IndexArr 是","分割的数字
现在有字符串 '2,34,45,345,867,4,984'
现在要检索的是 IndexArr 中每一个数字都在 字符串中出现过的 结果集。
我的解法：
--1.函数if exists(select * from sys.objects where name = 'f_splitSTR' and type = 'tf') drop function dbo.f_splitSTRgocreate function dbo.f_splitSTR( @s varchar(8000), --要分拆的字符串 @split varchar(10) --分隔字符) returns @re table( --要返回的临时表 col varchar(1000) --临时表中的列 )asbegin declare @len int set @len = LEN(@split) --分隔符不一定就是一个字符，可能是2个字符 while CHARINDEX(@split,@s) >0 begin insert into @re values(left(@s,charindex(@split,@s) - 1)) set @s = STUFF(@s,1,charindex(@split,@s) - 1 + @len ,'') --覆盖：字符串以及分隔符 end insert into @re values(@s) return --返回临时表endgo --> 测试数据：[tb]if object_id('[tb]') is not null drop table [tb]go create table [tb]([ID] int,[IndexArr] varchar(19))insert [tb]select 1,'1,2,3,4,5' union allselect 2,'55,6,99,87,1000' union allselect 3,'7,567567,567,43,123' union ALLSELECT 4,'2,34,45'--------------开始查询--------------------------DECLARE @s VARCHAR(1000)SET @s= '2,34,45,345,867,4,984';with tas(select t.ID, t.IndexArr, f.col, --把IndexArr按照分隔符,拆分成了多少个字符串 COUNT(*) over(PARTITION by IndexArr) as split_str_countfrom tb tcross apply dbo.f_splitSTR(t.IndexArr,',') f )select t.ID, t.IndexArrfrom twhere charindex(col, ','+@s+',') > 0group by t.ID, t.IndexArr, t.split_str_counthaving COUNT(*) = t.split_str_count --比如2,34,45分拆为3个字符串， --那么在经过where条件过滤后，记录数也必须是3 [email protected]?3、如何解决用户在线登陆时间——的小时和分钟计算问题。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 t1create table T1([user] varchar(30),operate varchar(10),time datetime)insert into T1select 'LiMing','Login','2010/10/24 8:03' union allselect 'WangYi','Login','2010/10/24 8:14' union allselect 'WangYi','Logout','2010/10/24 16:14' union allselect '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' endfrom(select T1.[user], DATEDIFF(MINUTE,t1.time,t2.time) as interval from T1inner join T1 t2 on t1.[user] = t2.[user] and t1.operate = 'login' and t2.operate = 'logout')a/*user (无列名)LiMing 8:11WangYi 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 T1inner join T1 t2 on t1.[user] = t2.[user] and t1.operate = 'login' and t2.operate = 'logout')a/*user (无列名)LiMing 08:11WangYi 08:00*/