欢迎来到Doc100.Net免费学习资源知识分享平台!
您的位置:首页 > 程序异常 >

在论坛中出现的比较难的sql有关问题:1

更新时间: 2014-01-05 02:07:22 责任编辑: Author_N1

 

在论坛中出现的比较难的sql问题:1

最近,在论坛中,遇到了不少比较难的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 tb

create table tb(id int,status int,date varchar(10),price int)

insert into tb
select 1,  1,      '2013-10-01',  218 union all
select 2,  1,      '2013-10-02',  218 union all
select 3,  0,      '2013-10-03',  218 union all
select 4,  0,      '2013-10-04',  238 union all
select 5,  0,      '2013-10-05',  238 union all
select 6,  0,      '2013-10-06',  238 union all
select 7,  0,      '2013-10-07',  258 union all
select 8,  0,      '2013-10-08',  258 union all
select 9,  0,      '2013-10-09',  218 --union all
--select 10,  0,      '2013-10-10',  218 
go



--第一个结果集
;with t
as
(
select *,
       row_number() over(partition by price order by id) as rownum,
       min(id) over(partition by price) as min_id
from tb 
),

tt
as
(
select id,
       price,
       a.date,
       rownum - (id - min_id) as interval
from t a 
)

select min(date) + '至' + max(date) as date,
       price
from tt
group by price,interval
order by 1
/*
date	                price
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
*/


--第2个结果集
;with t
as
(
select *,
       row_number() over(partition by status,price order by id) as rownum,
       min(id) over(partition by status,price) as min_id
from tb 
),

tt
as
(
select id,
       price,
       a.date,
       a.status,
       rownum - (id - min_id) as interval
from t a 
)

select status,min(date) + '至' + max(date),price
from tt
group by status,price,interval
order by 2
/*
status date	                    price
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
*/


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_splitSTR
go

create function dbo.f_splitSTR
(
	@s varchar(8000),     --要分拆的字符串
	@split varchar(10)    --分隔字符
) 
returns @re table(                      --要返回的临时表
                     col varchar(1000)  --临时表中的列 
                 )
as
begin   
  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   --返回临时表
end
go  



--> 测试数据:[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 all
select 2,'55,6,99,87,1000' union all
select 3,'7,567567,567,43,123' union ALL
SELECT 4,'2,34,45'



--------------开始查询--------------------------
DECLARE @s VARCHAR(1000)

SET @s= '2,34,45,345,867,4,984'


;with t
as
(
select t.ID,
       t.IndexArr,
       f.col,
       
       --把IndexArr按照分隔符,拆分成了多少个字符串
       COUNT(*) over(PARTITION by IndexArr) as split_str_count
from tb t
cross apply dbo.f_splitSTR(t.IndexArr,',') f       
)

select t.ID,
       t.IndexArr
from t
where charindex(col, ','+@s+',') > 0
group by t.ID,
         t.IndexArr,
         t.split_str_count
having COUNT(*) = t.split_str_count  --比如2,34,45分拆为3个字符串,
                                     --那么在经过where条件过滤后,记录数也必须是3
                                     --这样说明了indexarr中的字符串都在@s变量中出现了


 

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 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
*/

 

上一篇:上一篇
下一篇:下一篇

 

随机推荐程序问答结果

 

 

如对文章有任何疑问请提交到问题反馈,或者您对内容不满意,请您反馈给我们DOC100.NET论坛发贴求解。
DOC100.NET资源网,机器学习分类整理更新日期::2014-01-05 02:07:22
如需转载,请注明文章出处和来源网址:http://www.doc100.net/bugs/t/5858/
本文WWW.DOC100.NET DOC100.NET版权所有。