一些比较难的sql问题：2(七)
words
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个都匹配上了，才算为匹配
6、数据统计的问题。
有2个字段，Profit, profitSum。默认profitSum的值为0。如下图
现在要做下统计，规则第一条profitSum的值就为Profit
第二条profitSum的值为第一条的profitSum+第二条的Profit
第三条profitSum的值为第二条的profitSum+第三条的Profit，
结果集如下图
[sql]
--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
*/