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

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

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

 

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

 

最近,在论坛中,遇到了不少比较难的sql问题,虽然自己都能解决,但发现过几天后,就记不起来了,也忘记解决的方法了。

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

 

1、时间间隔计算.

http://bbs.csdn.net/topics/390608930 

这个问题非常复杂。

start_time                  end_time
2013-09-11 17:26:02.382      2013-09-24 10:38:01.41
2013-09-18 17:02:40.444      2013-09-22 15:27:58.984
2013-09-18 08:21:32.036      2013-09-22 15:31:52.499
2013-09-13 16:28:29.832      2013-09-16 09:41:47.988
2013-09-09 10:59:59.835      2013-09-10 14:06:21.223

要求计算这两个列的时间差 但是要去除9月份的正常休假并且只计算正常工作时间(上午8:30--12:00 下午14:00--18:00)

计算结果如下:

start_time                  end_time                        diff_time(小时)
2013-09-11 17:26:02.382      2013-09-24 10:38:01.41         55.1
2013-09-18 17:02:40.444      2013-09-22 15:27:58.984        5.9
2013-09-18 08:21:32.036      2013-09-22 15:31:52.499        12.5
2013-09-13 16:28:29.832      2013-09-16 09:41:47.988        2.7
2013-09-09 10:59:59.835      2013-09-10 14:06:21.223        1.1

请各位大大帮忙看看这个时间差应该怎么计算  谢谢了

 

我的解法:

 

if object_id('tab') is not null 
drop table tab

if object_id('holiday') is not null
drop table holiday

go
create table tab(start_time datetime,end_time datetime)

insert into tab
select '2013-09-11 17:26:02.382','2013-09-24 10:38:01.41'  union
select '2013-09-18 17:02:40.444','2013-09-22 15:27:58.984' union
select '2013-09-18 08:21:32.036','2013-09-22 15:31:52.499' union
select '2013-09-13 16:28:29.832','2013-09-16 09:41:47.988' union
select '2013-09-09 10:59:59.835','2013-09-09 14:06:21.223'
 
create table holiday(h_date datetime)

insert into holiday
select '2013-09-01' union 
select '2013-09-07'  union 
select '2013-09-08' union 
select '2013-09-14'union 
select '2013-09-15'union 
select '2013-09-19'union 
select '2013-09-20'union 
select '2013-09-21'union 
select '2013-09-29'
go



WITH calendar    --产生日历
AS
(
SELECT CAST('2013-09-01' as varchar(10)) AS r  --月份的开始日期

UNION ALL

SELECT convert(VARCHAR(10),dateadd(day,1,r),120)
FROM calendar
WHERE r < '2013-09-30'    --月份的结束日期
),

tt   --计算时间间隔,单位为秒
as
(
SELECT t.start_time,
       t.end_time,
       
       c.r,
       h.h_date,
       
       /*
       通过tab表和calendar表的关联,就能把开始时间到结束时间,多对应的多天,
       都给关联出来,
       比如开始时间 2013-09-18 08:21:32.037	结束时间 2013-09-22 15:31:52.500,
       其实就是,18、19、20、21、22这一共5天,会由原来的1条记录,现在变为5条记录。
       
       如果h_date为null,说明这一天不是假日,
       就需要计算时间间隔,有几种可能性:
       1.开始时间和结束时间,在同一天的
       2.当前日期和开始日期相同
       3.当前日期和结束日期相同
       4.当前日期是在开始日期和结束日期之间的某天
       
       如果h_date是null,那么返回0,说明是节假日,就不用计算时间间隔了 
       */
       case when h_date IS null and
                 convert(varchar(10),t.start_time_temp,120) = c.r and
                 CONVERT(varchar(10),t.end_time_temp,120) = c.r
                 then case when convert(varchar(5),t.start_time_temp,114) between '08:30' and '12:00'
                                and not (convert(varchar(5),t.end_time_temp,114) between '08:30' and '12:00')
                                then DATEDIFF(second,t.start_time_temp,c.r +' 12:00:00')
                           else 0
                      end +
                      case when convert(varchar(5),t.end_time_temp,114) between '14:00' and '18:00'
                                and not (convert(varchar(5),t.start_time_temp,114) between '14:00' and '18:00')
                                then DATEDIFF(second,c.r+' 14:00:00',t.end_time_temp)
                           else 0
                      end +
                      case when (convert(varchar(5),t.start_time_temp,114) between '08:30' and '12:00'
                                 and convert(varchar(5),t.end_time_temp,114) between '08:30' and '12:00')
                                or
                                (convert(varchar(5),t.end_time_temp,114) between '14:00' and '18:00'
                                 and convert(varchar(5),t.start_time_temp,114) between '14:00' and '18:00')
                                then DATEDIFF(SECOND,t.start_time_temp,t.end_time_temp)
                           else 0
                      end     
            
            /*
            注意下面的计算逻辑是,如果这天不是假日,同时与开始日期相同
            那么就要计算时间间隔,如果时间是在上午的工作时间范围内,
            那么用当前日期的12点,减去开始日期,就是时间间隔,但还必须要加上下午的工作时间,
            也就是4个小时,转化为秒数,就是4*3600
            */                    
            when h_date IS null and
                 convert(varchar(10),t.start_time_temp,120) = c.r 
                 then case when convert(varchar(5),t.start_time_temp,114) between '08:30' and '12:00'
                                then DATEDIFF(second,t.start_time_temp,c.r +' 12:00:00') + 4 * 3600
                           else 0
                      end +
                      case when convert(varchar(5),t.start_time_temp,114) between '14:00' and '18:00'
                                then DATEDIFF(second,t.start_time_temp,c.r +' 18:00:00')
                           else 0
                      end                      
                 
            when h_date IS null and
                 CONVERT(varchar(10),t.end_time_temp,120) = c.r
                 then case when convert(varchar(5),t.end_time_temp,114) between '08:30' and '12:00'
                                then DATEDIFF(second,c.r +' 08:30:00',t.end_time_temp) 
                           else 0
                      end +
                      case when convert(varchar(5),t.end_time_temp,114) between '14:00' and '18:00'
                                then DATEDIFF(second,c.r +' 14:00:00',t.end_time_temp) + 3.5 * 3600
                           else 0
                      end
            
            when h_date is null and
                 convert(varchar(10),t.start_time_temp,120) < c.r and
                 CONVERT(varchar(10),t.end_time_temp,120) > c.r     
                 then  7.5 * 3600
                 
            when h_date IS null
                 then 0
       end   as seconds 
FROM 
(
/*
这里之所以要转换,是由于有些时间比如 start_time为2013-09-18 08:21:32.037,
不在正常工作时间(上午8:30--12:00 下午14:00--18:00)内,
所以要先转化为正常工作时间,否则后面的case when的逻辑判断就太复杂了。
*/
SELECT start_time,
       end_time,
       
       case when CONVERT(varchar(5),start_time,114) < '08:30'
                 then cast(CONVERT(varchar(10),start_time,120) + ' 08:30:00' AS datetime)
                 
            when CONVERT(varchar(5),start_time,114) between '12:00' and '14:00'
                 then cast(CONVERT(varchar(10),start_time,120) + ' 12:00:00' AS datetime)
            
            else start_time
       end as start_time_temp,

       case when CONVERT(varchar(5),end_time,114) between '12:00' and '14:00'
                 then cast(CONVERT(varchar(10),end_time,120) + ' 12:00:00' AS datetime)
            
            when CONVERT(varchar(5),end_time,114) > '18:00'
                 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、统一改换查询出的字段。。这是不是想多了?

http://bbs.csdn.net/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) 


 

3、求一SQL语句。

http://bbs.csdn.net/topics/390496661

create table #tab 
(
col1 char(10),
col2 char(10),
item char(10),
num int,
[Date] varchar(10))
insert #tab values('AAA','BBB','A',50,'2013-06-10')
insert #tab values('ABB','BGG','B',30,'2013-06-10')
insert #tab values('AAA','BBB','C',80,'2013-06-13')

我的解法:

create table tab 
(
col1 char(10),
col2 char(10),
item char(10),
num int,
[Date] varchar(10)
)

insert tab values('AAA','BBB','A',50,'2013-06-10')
insert tab values('ABB','BGG','B',30,'2013-06-10')
insert tab values('AAA','BBB','C',80,'2013-06-13')




--动态生成sql语句
declare @start_date varchar(10) = '2013-06-01',
        @end_date   varchar(10) = '2013-06-30';

declare @date  varchar(10),
        @sql   varchar(max) = '',
        @sql1  varchar(8000),
        @sql2  varchar(8000);

set @date = @start_date;

set @sql1 = 'select case when rownum = 1 then col1 else '''' end as col1,
                    case when rownum = 1 then col2 else '''' end as col2,
                    item'

set @sql2 = 'select col1,col2,item,row_number() over(partition by col1,col2 
                                                         order by item) as rownum'
        

while @date <= @end_date
begin
    set @sql1 = @sql1 + ',v_' + REPLACE( right(@date,5),'-','') + 
                        ' as ''' + CAST(DATEPART(month,@date) as varchar) + '/' +
                                 CAST(DATEPART(day,@date) as varchar) +'''';                                  
	set @sql2 = @sql2 + ',SUM(case when date =''' + @date + 
	                   ''' then num else 0 end) as v_' + 
	                   REPLACE( right(@date,5),'-','')
	
	set @date = CONVERT(varchar(10),dateadd(day,1,@date),120)
end


set @sql = @sql1 + ' from (' +
                       @sql2 + ' from tab 
                                 group by col1,col2,item' +
                   ') v'
 
--生产的动态sql语句                  
select @sql


exec(@sql)


上面由于是动态生成语句,所以不能用局部的临时表,所以建了一个表。

下面是动态生成的sql语句,经过了格式化:

select case when rownum = 1 then col1 else '' end as col1,
       case when rownum = 1 then col2 else '' end as col2,                     
       item,
       
       v_0601 as '6/1',v_0602 as '6/2',v_0603 as '6/3',
       v_0604 as '6/4',v_0605 as '6/5',
       v_0606 as '6/6',v_0607 as '6/7',
       v_0608 as '6/8',v_0609 as '6/9',
       v_0610 as '6/10',v_0611 as '6/11',
       v_0612 as '6/12',v_0613 as '6/13',
       v_0614 as '6/14',v_0615 as '6/15',
       v_0616 as '6/16',v_0617 as '6/17',
       v_0618 as '6/18',v_0619 as '6/19',
       v_0620 as '6/20',v_0621 as '6/21',
       v_0622 as '6/22',v_0623 as '6/23',
       v_0624 as '6/24',v_0625 as '6/25',
       v_0626 as '6/26',v_0627 as '6/27',
       v_0628 as '6/28',v_0629 as '6/29',
       v_0630 as '6/30' 
from 
(
	select col1,col2,item,
	
		   row_number() over(partition by col1,col2  order by item) as rownum,
		   
		   SUM(case when date ='2013-06-01' then num else 0 end) as v_0601,
		   SUM(case when date ='2013-06-02' then num else 0 end) as v_0602,
		   SUM(case when date ='2013-06-03' then num else 0 end) as v_0603,
		   SUM(case when date ='2013-06-04' then num else 0 end) as v_0604,
		   SUM(case when date ='2013-06-05' then num else 0 end) as v_0605,
		   SUM(case when date ='2013-06-06' then num else 0 end) as v_0606,
		   SUM(case when date ='2013-06-07' then num else 0 end) as v_0607,
		   SUM(case when date ='2013-06-08' then num else 0 end) as v_0608,
		   SUM(case when date ='2013-06-09' then num else 0 end) as v_0609,
		   SUM(case when date ='2013-06-10' then num else 0 end) as v_0610,
		   SUM(case when date ='2013-06-11' then num else 0 end) as v_0611,
		   SUM(case when date ='2013-06-12' then num else 0 end) as v_0612,
		   SUM(case when date ='2013-06-13' then num else 0 end) as v_0613,
		   SUM(case when date ='2013-06-14' then num else 0 end) as v_0614,
		   SUM(case when date ='2013-06-15' then num else 0 end) as v_0615,
		   SUM(case when date ='2013-06-16' then num else 0 end) as v_0616,
		   SUM(case when date ='2013-06-17' then num else 0 end) as v_0617,
		   SUM(case when date ='2013-06-18' then num else 0 end) as v_0618,
		   SUM(case when date ='2013-06-19' then num else 0 end) as v_0619,
		   SUM(case when date ='2013-06-20' then num else 0 end) as v_0620,
		   SUM(case when date ='2013-06-21' then num else 0 end) as v_0621,
		   SUM(case when date ='2013-06-22' then num else 0 end) as v_0622,
		   SUM(case when date ='2013-06-23' then num else 0 end) as v_0623,
		   SUM(case when date ='2013-06-24' then num else 0 end) as v_0624,
		   SUM(case when date ='2013-06-25' then num else 0 end) as v_0625,
		   SUM(case when date ='2013-06-26' then num else 0 end) as v_0626,
		   SUM(case when date ='2013-06-27' then num else 0 end) as v_0627,
		   SUM(case when date ='2013-06-28' then num else 0 end) as v_0628,
		   SUM(case when date ='2013-06-29' then num else 0 end) as v_0629,
		   SUM(case when date ='2013-06-30' then num else 0 end) as v_0630 
	from tab                                    
	group by col1,col2,item
) v


4、这个语句怎么写?

http://bbs.csdn.net/topics/390490832?page=1

我有一张表:CarRule
有下面这些列和数据
ID    Keywords
1     时速50%、 不到100%
2     违反禁令标志
3     违反规定停放、拒绝立即驶离、妨碍其他车辆

我要查询这个CarRule表,根据关键字获取ID
例如:机动车行驶超过规定时速50%以上不到100%的  就能获取到  ID=1
      机动车违反禁令标志的                     就能获取到  ID=2
      违反规定停放、临时停车且驾驶人不在现场或驾驶人虽在现场拒绝立即驶离,妨碍其他车辆、行人通行的        
就能获取到  ID=3

这个查询我怎么写。

 

我的解法:

--1.先建立一个函数,通过分隔符来拆分keywords成多个关键字
create function dbo.fn_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  



--2.建表
DECLARE @CarRule TABLE(id INT,Keywords VARCHAR(100))


INSERT INTO @carrule
VALUES(1,'时速50%、不到100%'),
      (2,'违反禁令标志'),
      (3,'违反规定停放、拒绝立即驶离、妨碍其他车辆')




;WITH split  --拆分关键字
as
(
SELECT  c.id,
        c.keywords,
        f.col  
FROM @carrule c
CROSS apply dbo.fn_splitSTR(c.keywords,'、') f


)


--3.第1个查询
SELECT s.id,
       s.keywords

FROM split s
INNER JOIN 
		(
			SELECT s.id,
			       s.keywords,
			       count(col) AS split_str_count   --拆分成了几个关键字
			FROM split s
			GROUP BY s.id,
			         s.keywords
		
		) ss
        ON s.id = ss.id

WHERE charindex(s.col,'机动车行驶超过规定时速50%以上不到100%的') > 0

GROUP BY s.id,
         s.keywords
HAVING count(*) = max(ss.split_str_count)  --比如第一条记录拆分成了2个关键词,那么在匹配时要2个都匹配上了,才算为匹配


第2个查询:

DECLARE @CarRule TABLE(id INT,Keywords VARCHAR(100))


INSERT INTO @carrule
VALUES(1,'时速50%、不到100%'),
      (2,'违反禁令标志'),
      (3,'违反规定停放、拒绝立即驶离、妨碍其他车辆')




;WITH split  --拆分关键字
as
(
SELECT  c.id,
        c.keywords,
        f.col  
FROM @carrule c
CROSS apply dbo.fn_splitSTR(c.keywords,'、') f


)


--3.
SELECT s.id,
       s.keywords

FROM split s
INNER JOIN 
		(
			SELECT s.id,
			       s.keywords,
			       count(col) AS split_str_count   --拆分成了几个关键字
			FROM split s
			GROUP BY s.id,
			         s.keywords
		
		) ss
        ON s.id = ss.id

WHERE charindex(s.col,'机动车违反禁令标志的') > 0

GROUP BY s.id,
         s.keywords
HAVING count(*) = max(ss.split_str_count)  --比如第一条记录拆分成了2个关键词,那么在匹配时要2个都匹配上了,才算为匹配


第3个查询:

DECLARE @CarRule TABLE(id INT,Keywords VARCHAR(100))


INSERT INTO @carrule
VALUES(1,'时速50%、不到100%'),
      (2,'违反禁令标志'),
      (3,'违反规定停放、拒绝立即驶离、妨碍其他车辆')




;WITH split  --拆分关键字
as
(
SELECT  c.id,
        c.keywords,
        f.col  
FROM @carrule c
CROSS apply dbo.fn_splitSTR(c.keywords,'、') f


)


--3.
SELECT s.id,
       s.keywords

FROM split s
INNER JOIN 
		(
			SELECT s.id,
			       s.keywords,
			       count(col) AS split_str_count   --拆分成了几个关键字
			FROM split s
			GROUP BY s.id,
			         s.keywords
		
		) ss
        ON s.id = ss.id

WHERE charindex(s.col,'违反规定停放、临时停车且驾驶人不在现场或驾驶人虽在现场拒绝立即驶离,妨碍其他车辆、行人通行的就能获取到') > 0

GROUP BY s.id,
         s.keywords
HAVING count(*) = max(ss.split_str_count)  --比如第一条记录拆分成了2个关键词,那么在匹配时要2个都匹配上了,才算为匹配

 

5、数据统计的问题。

http://bbs.csdn.net/topics/390618778

有2个字段,Profit, profitSum。默认profitSum的值为0。如下图


现在要做下统计,规则第一条profitSum的值就为Profit
第二条profitSum的值为第一条的profitSum+第二条的Profit
第三条profitSum的值为第二条的profitSum+第三条的Profit,

结果集如下图

 

我的解法:

--drop table tb


create table tb
(
Profit decimal(10,2),
profitSum decimal(10,2)
)


insert into tb
select 20000.0,0.00 union all
select 5.00,0.00 union all
select 0.00,0.00 union all
select 0.00,0.00 union all
select -383.40,0.00 union all
select 379.80,0.00 union all
select 3.50,0.00


;with t 
as
(
select *,
       row_number() over(order by @@servername) as rownum
from tb
)

select profit,
       (select sum(profit) 
        from t t2 
        where t2.rownum <=  t1.rownum)  as profitSum
from t t1

/*
profit	    profitSum
20000.00	20000.00
5.00	    20005.00
0.00	    20005.00
0.00	    20005.00
-383.40	    19621.60
379.80	    20001.40
3.50	    20004.90
*/


 

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

 

随机推荐程序问答结果

 

 

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