在论坛中出现的比较难的sql有关问题：2
在论坛中出现的比较难的sql问题：2
最近，在论坛中，遇到了不少比较难的sql问题，虽然自己都能解决，但发现过几天后，就记不起来了，也忘记解决的方法了。
所以，觉得有必要记录下来，这样以后再次碰到这类问题，也能从中获取解答的思路。
1、时间间隔计算.
http://bbs.****.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.****.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.****.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.****.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.****.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
*/