解决方案 »
- 加入条件限制行列转换问题?
- 一个网页的查询要做分页显示,如何才能知道要查询的记录在第几页?sql语句该如何写?
- 0分求助:小数点后面多出.000000000002
- 求一个函数,急急急、、、
- 100分求助Sql Server 2005 使用DTS导出拉丁字符集到Excel时乱码
- 一个SQL
- sql关联删除
- 快来拿分了
- 在分析服务器中看分析数据时,出错Provider cannot be found. It may not be properly installed.是什么原因呢?
- '115x55x26'怎么截取得到'55'
- 请教一个SQL存储过程写法,如何根据某表记录遍历出记录对应的多个表?!
- 高分请高手帮忙SQL语句,在线等,急!!!
m int,
rmb int,
txt varchar(20))
go
insert into t
select 5, 20, '12|43|45|' union all
select 5, 23, '10|34|' union all
select 5, 14, '12|68|' union all
select 5, 24, '45|17|36|' union all
select 5, 10, '12|36|89|55|69|' union all
select 5, 10, '45|69|56|33|20|' union all
select 5, 10, '45|40|58|25|17|' union all
select 5, 20, '12|05|16|45|32|' union all
select 5, 10, '15|48|46'
gowith tmp
as
(select m,rmb,replace(txt,'|','') as 号码 from t)select 号码,sum(m) as '投注次数',sum(rmb) as '投注金额' from tmp group by 号码
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (txt varchar(100),rmb int )
insert into #tb
select '1,2,5',30 union all
select '2,5,6',20 union all
select '1,3',10 union all
select '1',40
select txt=substring( txt+',',number,charindex(',',txt+',',number)-number),
rmb=sum(rmb),
n=count(*)
from #tb,master..spt_values
where substring(','+txt,number,1)=','
and type='p' and number>0
group by substring( txt+',',number,charindex(',',txt+',',number)-number)
txt rmb n
----------------------------------------------------------------------------------------------------- ----------- -----------
1 80 3
2 50 2
3 10 1
5 50 2
6 20 1(5 行受影响)
go
insert into t
select 5, 20, '12|43|45|' union all
select 5, 23, '10|34|' union all
select 5, 14, '12|68|' union all
select 5, 24, '45|17|36|' union all
select 5, 10, '12|36|89|55|69|' union all
select 5, 10, '45|69|56|33|20|' union all
select 5, 10, '45|40|58|25|17|' union all
select 5, 20, '12|05|16|45|32|' union all
select 5, 10, '15|48|46'
go
with cte as(
select rmb,convert(varchar,txt)as txt,convert(varchar(30),'') as flg from t where charindex('|',txt)=0
union all
select rmb,convert(varchar,left(txt,charindex('|',txt)-1)) as txt,right(txt,len(txt)-charindex('|',txt)) as flg from t where charindex('|',txt)>1
union all
select rmb,convert(varchar,left(flg,charindex('|',flg)-1)) as txt,right(flg,len(flg)-charindex('|',flg)) as flg from cte where charindex('|',flg)>1
)select txt,count(*) as tzcs,sum(rmb)as zje from cte group by txt
go
drop table t
/*
txt tzcs zje
------------------------------ ----------- -----------
05 1 20
10 1 23
12 4 64
15 1 10
16 1 20
17 2 34
20 1 10
25 1 10
32 1 20
33 1 10
34 1 23
36 2 34
40 1 10
43 1 20
45 5 84
48 1 10
55 1 10
56 1 10
58 1 10
68 1 14
69 2 20
89 1 10(22 行受影响)*/
create table t(m int,rmb int,txt varchar(20))
go
insert into t
select 5, 20, '3|1|5|' union all
select 5, 23, '1|10|20|30|40|' union all
select 5, 14, '12|43|45|' union all
select 5, 24, '10|34|' union all
select 5, 10, '12|68|' union all
select 5, 10, '45|17|36|' union all
select 5, 10, '12|36|89|55|69|' union all
select 5, 20, '45|69|56|33|20|' union all
select 5, 20, '45|40|58|25|17|' union all
select 5, 20, '12|05|16|45|32|' union all
select 5, 10, '15|48|46'
go
with cte as(
select rmb,convert(varchar,txt)as txt,convert(varchar(30),'') as flg from t where charindex('|',txt)=0
union all
select rmb,convert(varchar,left(txt,charindex('|',txt)-1)) as txt,right(txt,len(txt)-charindex('|',txt)) as flg from t where charindex('|',txt)>1
union all
select rmb,convert(varchar,left(flg,charindex('|',flg)-1)) as txt,right(flg,len(flg)-charindex('|',flg)) as flg from cte where charindex('|',flg)>1
)select txt,count(*) as tzcs,sum(rmb)as zje from cte group by txt
go
drop table t
/*
txt tzcs zje
------------------------------ ----------- -----------
05 1 20
1 2 43
10 2 47
12 4 54
15 1 10
16 1 20
17 2 30
20 2 43
25 1 20
3 1 20
30 1 23
32 1 20
33 1 20
34 1 24
36 2 20
40 2 43
43 1 14
45 5 84
48 1 10
5 1 20
55 1 10
56 1 20
58 1 20
68 1 10
69 2 30
89 1 10(26 行受影响)*/
select 5, 20, '3|1|5|' union all
select 5, 23, '1|10|20|30|40|' union all
select 5, 14, '12|43|45|' union all
select 5, 24, '10|34|' union all
select 5, 10, '12|68|' union all
select 5, 10, '45|17|36|' union all
select 5, 10, '12|36|89|55|69|' union all
select 5, 20, '45|69|56|33|20|' union all
select 5, 20, '45|40|58|25|17|' union all
select 5, 20, '12|05|16|45|32|' union all
select 5, 10, '15|48|46'这每个前面都有一个5,这个是做什么的?