在线求一个SQL语句~~~~ 有关累计的

wqrz 2013-12-19 03:43:59
我有一个表， 要全部生成结果， 如果能用一个SQL语句搞定最好，



其中：
从vip第一次消费记录的每个月都要生成， 比如A 2013年1月份开始消费， 2月份没消费， 2月份也要出来，
B从2月份开始消费， 那记录就从2月份开始



这是原始表语句：


create table #xs(vip varchar(20), date datetime, sale float)

insert into #xs(vip, date, sale)

select 'A' as vip,'2013-1-1' as date,	230 as sale union all

select 'A','2013-1-5',	400 union all

select 'A','2013-3-6',	500 union all

select 'A','2013-4-30',600 union all

select 'B','2013-2-3',200 union all

select 'B','2013-3-5',300 union all

select 'B','2013-4-24',400 



select * from #xs
...全文
355 14 打赏 收藏 转发到动态 举报
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
wqrz 2013-12-19
  • 打赏
  • 举报
 回复
引用 13 楼 stublue 的回复:
[quote=引用 12 楼 t101lian 的回复:] [quote=引用 11 楼 stublue 的回复:] [quote=引用 8 楼 wqrz 的回复:] [quote=引用 4 楼 stublue 的回复:] 

if object_id('tempdb..#xs') is not null
drop table #xs;

create table #xs(vip varchar(20), date datetime, sale float)
insert into #xs(vip, date, sale)
select 'A' as vip,'2013-1-1' as date,	230 as sale union all
select 'A','2013-1-5',	400 union all
select 'A','2013-3-6',	500 union all
select 'A','2013-4-30',600 union all
select 'B','2013-2-3',200 union all
select 'B','2013-3-5',300 union all
select 'B','2013-4-24',400 

;with t as 
(
select dateadd(month, number,'20130101') MonthDay
from master.dbo.spt_values
where type='p'
)
,tRange as 
(
select vip,convert(varchar(6),min(date),112)+'01' MinMonth,convert(varchar(6),max(date),112)+'01' MaxMonth
from #xs
group by vip
),tDimDate as
(
select tr.vip,t.MonthDay StartDay,dateadd(d,-1,DATEADD(month,1,t.MonthDay)) EndDay
from tRange tr
join t 
on tr.MinMonth<=t.MonthDay
and tr.MaxMonth>=t.MonthDay
),tdata as (
select t.vip,EndDay
,DATEDIFF(d,(select top 1 date from #xs xs where xs.date <=t.EndDay and xs.vip=t.vip order by xs.date desc  ),EndDay) NoVisitDays
,count(x.date) VisitDays
,sum(isnull(sale,0)) Sales
from tDimDate t
left join #xs x
on x.[date] between t.StartDay and t.EndDay
and t.vip=x.vip
group by t.vip,EndDay
)
select vip,EndDay,NoVisitDays,VisitDays
,sum(VisitDays) over(partition by vip order by EndDay ) sumVisitDays
,sum(Sales) over(partition by vip order by EndDay ) sumSales
from tdata
哥你这语句是不是漏掉了什么？ 怎么我看sum(VisitDays) over(partition by vip order by EndDay ) sumVisitDays 这个怪怪的？[/quote] 我这边是正常的啊。 没见过 sum（） over() 这种写法吗[/quote] 你用的是 2008吧？ 05里面这样写不行，去掉order by EndDay才可以[/quote] 


if object_id('tempdb..#xs') is not null
drop table #xs;

create table #xs(vip varchar(20), date datetime, sale float)
insert into #xs(vip, date, sale)
select 'A' as vip,'2013-1-1' as date,	230 as sale union all
select 'A','2013-1-5',	400 union all
select 'A','2013-3-6',	500 union all
select 'A','2013-4-30',600 union all
select 'B','2013-2-3',200 union all
select 'B','2013-3-5',300 union all
select 'B','2013-4-24',400 

;with t as 
(
select dateadd(month, number,'20130101') MonthDay
from master.dbo.spt_values
where type='p'
)
,tRange as 
(
select vip,convert(varchar(6),min(date),112)+'01' MinMonth,convert(varchar(6),max(date),112)+'01' MaxMonth
from #xs
group by vip
),tDimDate as
(
select tr.vip,t.MonthDay StartDay,dateadd(d,-1,DATEADD(month,1,t.MonthDay)) EndDay
from tRange tr
join t 
on tr.MinMonth<=t.MonthDay
and tr.MaxMonth>=t.MonthDay
),tdata as (
select t.vip,EndDay
,DATEDIFF(d,(select top 1 date from #xs xs where xs.date <=t.EndDay and xs.vip=t.vip order by xs.date desc  ),EndDay) NoVisitDays
,count(x.date) VisitDays
,sum(isnull(sale,0)) Sales
from tDimDate t
left join #xs x
on x.[date] between t.StartDay and t.EndDay
and t.vip=x.vip
group by t.vip,EndDay
)
select vip,EndDay,NoVisitDays,VisitDays
,(select sum(VisitDays) from tdata b where a.vip=b.vip and b.EndDay<=a.EndDay  ) sumVisitDays
,(select sum(Sales) from tdata c where a.vip=c.vip and c.EndDay<=a.EndDay  )  sumSales
from tdata a
order by 1,2
那就还是用子查询吧！ 我用的2012[/quote] 我用的2005 难怪看上去这么奇怪的语句。。。 ok 结贴了 感谢ls各位大神！ 尤其是stublue和jinfengyiye
Leon_He2014 2013-12-19
  • 打赏
  • 举报
 回复
引用 12 楼 t101lian 的回复:
[quote=引用 11 楼 stublue 的回复:] [quote=引用 8 楼 wqrz 的回复:] [quote=引用 4 楼 stublue 的回复:] 

if object_id('tempdb..#xs') is not null
drop table #xs;

create table #xs(vip varchar(20), date datetime, sale float)
insert into #xs(vip, date, sale)
select 'A' as vip,'2013-1-1' as date,	230 as sale union all
select 'A','2013-1-5',	400 union all
select 'A','2013-3-6',	500 union all
select 'A','2013-4-30',600 union all
select 'B','2013-2-3',200 union all
select 'B','2013-3-5',300 union all
select 'B','2013-4-24',400 

;with t as 
(
select dateadd(month, number,'20130101') MonthDay
from master.dbo.spt_values
where type='p'
)
,tRange as 
(
select vip,convert(varchar(6),min(date),112)+'01' MinMonth,convert(varchar(6),max(date),112)+'01' MaxMonth
from #xs
group by vip
),tDimDate as
(
select tr.vip,t.MonthDay StartDay,dateadd(d,-1,DATEADD(month,1,t.MonthDay)) EndDay
from tRange tr
join t 
on tr.MinMonth<=t.MonthDay
and tr.MaxMonth>=t.MonthDay
),tdata as (
select t.vip,EndDay
,DATEDIFF(d,(select top 1 date from #xs xs where xs.date <=t.EndDay and xs.vip=t.vip order by xs.date desc  ),EndDay) NoVisitDays
,count(x.date) VisitDays
,sum(isnull(sale,0)) Sales
from tDimDate t
left join #xs x
on x.[date] between t.StartDay and t.EndDay
and t.vip=x.vip
group by t.vip,EndDay
)
select vip,EndDay,NoVisitDays,VisitDays
,sum(VisitDays) over(partition by vip order by EndDay ) sumVisitDays
,sum(Sales) over(partition by vip order by EndDay ) sumSales
from tdata
哥你这语句是不是漏掉了什么？ 怎么我看sum(VisitDays) over(partition by vip order by EndDay ) sumVisitDays 这个怪怪的？[/quote] 我这边是正常的啊。 没见过 sum（） over() 这种写法吗[/quote] 你用的是 2008吧？ 05里面这样写不行，去掉order by EndDay才可以[/quote] 


if object_id('tempdb..#xs') is not null
drop table #xs;

create table #xs(vip varchar(20), date datetime, sale float)
insert into #xs(vip, date, sale)
select 'A' as vip,'2013-1-1' as date,	230 as sale union all
select 'A','2013-1-5',	400 union all
select 'A','2013-3-6',	500 union all
select 'A','2013-4-30',600 union all
select 'B','2013-2-3',200 union all
select 'B','2013-3-5',300 union all
select 'B','2013-4-24',400 

;with t as 
(
select dateadd(month, number,'20130101') MonthDay
from master.dbo.spt_values
where type='p'
)
,tRange as 
(
select vip,convert(varchar(6),min(date),112)+'01' MinMonth,convert(varchar(6),max(date),112)+'01' MaxMonth
from #xs
group by vip
),tDimDate as
(
select tr.vip,t.MonthDay StartDay,dateadd(d,-1,DATEADD(month,1,t.MonthDay)) EndDay
from tRange tr
join t 
on tr.MinMonth<=t.MonthDay
and tr.MaxMonth>=t.MonthDay
),tdata as (
select t.vip,EndDay
,DATEDIFF(d,(select top 1 date from #xs xs where xs.date <=t.EndDay and xs.vip=t.vip order by xs.date desc  ),EndDay) NoVisitDays
,count(x.date) VisitDays
,sum(isnull(sale,0)) Sales
from tDimDate t
left join #xs x
on x.[date] between t.StartDay and t.EndDay
and t.vip=x.vip
group by t.vip,EndDay
)
select vip,EndDay,NoVisitDays,VisitDays
,(select sum(VisitDays) from tdata b where a.vip=b.vip and b.EndDay<=a.EndDay  ) sumVisitDays
,(select sum(Sales) from tdata c where a.vip=c.vip and c.EndDay<=a.EndDay  )  sumSales
from tdata a
order by 1,2
那就还是用子查询吧！ 我用的2012
t101lian 2013-12-19
  • 打赏
  • 举报
 回复
引用 11 楼 stublue 的回复:
[quote=引用 8 楼 wqrz 的回复:]
[quote=引用 4 楼 stublue 的回复:]


if object_id('tempdb..#xs') is not null

drop table #xs;



create table #xs(vip varchar(20), date datetime, sale float)

insert into #xs(vip, date, sale)

select 'A' as vip,'2013-1-1' as date,	230 as sale union all

select 'A','2013-1-5',	400 union all

select 'A','2013-3-6',	500 union all

select 'A','2013-4-30',600 union all

select 'B','2013-2-3',200 union all

select 'B','2013-3-5',300 union all

select 'B','2013-4-24',400 



;with t as 

(

select dateadd(month, number,'20130101') MonthDay

from master.dbo.spt_values

where type='p'

)

,tRange as 

(

select vip,convert(varchar(6),min(date),112)+'01' MinMonth,convert(varchar(6),max(date),112)+'01' MaxMonth

from #xs

group by vip

),tDimDate as

(

select tr.vip,t.MonthDay StartDay,dateadd(d,-1,DATEADD(month,1,t.MonthDay)) EndDay

from tRange tr

join t 

on tr.MinMonth<=t.MonthDay

and tr.MaxMonth>=t.MonthDay

),tdata as (

select t.vip,EndDay

,DATEDIFF(d,(select top 1 date from #xs xs where xs.date <=t.EndDay and xs.vip=t.vip order by xs.date desc  ),EndDay) NoVisitDays

,count(x.date) VisitDays

,sum(isnull(sale,0)) Sales

from tDimDate t

left join #xs x

on x.[date] between t.StartDay and t.EndDay

and t.vip=x.vip

group by t.vip,EndDay

)

select vip,EndDay,NoVisitDays,VisitDays

,sum(VisitDays) over(partition by vip order by EndDay ) sumVisitDays

,sum(Sales) over(partition by vip order by EndDay ) sumSales

from tdata


哥你这语句是不是漏掉了什么？ 怎么我看sum(VisitDays) over(partition by vip order by EndDay ) sumVisitDays 这个怪怪的？[/quote]


我这边是正常的啊。
没见过 sum（） over() 这种写法吗[/quote]

你用的是 2008吧？ 05里面这样写不行，去掉order by EndDay才可以
Leon_He2014 2013-12-19
  • 打赏
  • 举报
 回复
引用 8 楼 wqrz 的回复:
[quote=引用 4 楼 stublue 的回复:]


if object_id('tempdb..#xs') is not null

drop table #xs;



create table #xs(vip varchar(20), date datetime, sale float)

insert into #xs(vip, date, sale)

select 'A' as vip,'2013-1-1' as date,	230 as sale union all

select 'A','2013-1-5',	400 union all

select 'A','2013-3-6',	500 union all

select 'A','2013-4-30',600 union all

select 'B','2013-2-3',200 union all

select 'B','2013-3-5',300 union all

select 'B','2013-4-24',400 



;with t as 

(

select dateadd(month, number,'20130101') MonthDay

from master.dbo.spt_values

where type='p'

)

,tRange as 

(

select vip,convert(varchar(6),min(date),112)+'01' MinMonth,convert(varchar(6),max(date),112)+'01' MaxMonth

from #xs

group by vip

),tDimDate as

(

select tr.vip,t.MonthDay StartDay,dateadd(d,-1,DATEADD(month,1,t.MonthDay)) EndDay

from tRange tr

join t 

on tr.MinMonth<=t.MonthDay

and tr.MaxMonth>=t.MonthDay

),tdata as (

select t.vip,EndDay

,DATEDIFF(d,(select top 1 date from #xs xs where xs.date <=t.EndDay and xs.vip=t.vip order by xs.date desc  ),EndDay) NoVisitDays

,count(x.date) VisitDays

,sum(isnull(sale,0)) Sales

from tDimDate t

left join #xs x

on x.[date] between t.StartDay and t.EndDay

and t.vip=x.vip

group by t.vip,EndDay

)

select vip,EndDay,NoVisitDays,VisitDays

,sum(VisitDays) over(partition by vip order by EndDay ) sumVisitDays

,sum(Sales) over(partition by vip order by EndDay ) sumSales

from tdata


哥你这语句是不是漏掉了什么？ 怎么我看sum(VisitDays) over(partition by vip order by EndDay ) sumVisitDays 这个怪怪的？[/quote]


我这边是正常的啊。
没见过 sum（） over() 这种写法吗
wqrz 2013-12-19
  • 打赏
  • 举报
 回复
引用 9 楼 t101lian 的回复:
[quote=引用 8 楼 wqrz 的回复:] [quote=引用 4 楼 stublue 的回复:] 

if object_id('tempdb..#xs') is not null
drop table #xs;

create table #xs(vip varchar(20), date datetime, sale float)
insert into #xs(vip, date, sale)
select 'A' as vip,'2013-1-1' as date,	230 as sale union all
select 'A','2013-1-5',	400 union all
select 'A','2013-3-6',	500 union all
select 'A','2013-4-30',600 union all
select 'B','2013-2-3',200 union all
select 'B','2013-3-5',300 union all
select 'B','2013-4-24',400 

;with t as 
(
select dateadd(month, number,'20130101') MonthDay
from master.dbo.spt_values
where type='p'
)
,tRange as 
(
select vip,convert(varchar(6),min(date),112)+'01' MinMonth,convert(varchar(6),max(date),112)+'01' MaxMonth
from #xs
group by vip
),tDimDate as
(
select tr.vip,t.MonthDay StartDay,dateadd(d,-1,DATEADD(month,1,t.MonthDay)) EndDay
from tRange tr
join t 
on tr.MinMonth<=t.MonthDay
and tr.MaxMonth>=t.MonthDay
),tdata as (
select t.vip,EndDay
,DATEDIFF(d,(select top 1 date from #xs xs where xs.date <=t.EndDay and xs.vip=t.vip order by xs.date desc  ),EndDay) NoVisitDays
,count(x.date) VisitDays
,sum(isnull(sale,0)) Sales
from tDimDate t
left join #xs x
on x.[date] between t.StartDay and t.EndDay
and t.vip=x.vip
group by t.vip,EndDay
)
select vip,EndDay,NoVisitDays,VisitDays
,sum(VisitDays) over(partition by vip order by EndDay ) sumVisitDays
,sum(Sales) over(partition by vip order by EndDay ) sumSales
from tdata
哥你这语句是不是漏掉了什么？ 怎么我看sum(VisitDays) over(partition by vip order by EndDay ) sumVisitDays 这个怪怪的？[/quote] 你把order by EndDay去掉就不报错啦，[/quote] 去掉结果也错了
t101lian 2013-12-19
  • 打赏
  • 举报
 回复
引用 8 楼 wqrz 的回复:
[quote=引用 4 楼 stublue 的回复:] 

if object_id('tempdb..#xs') is not null
drop table #xs;

create table #xs(vip varchar(20), date datetime, sale float)
insert into #xs(vip, date, sale)
select 'A' as vip,'2013-1-1' as date,	230 as sale union all
select 'A','2013-1-5',	400 union all
select 'A','2013-3-6',	500 union all
select 'A','2013-4-30',600 union all
select 'B','2013-2-3',200 union all
select 'B','2013-3-5',300 union all
select 'B','2013-4-24',400 

;with t as 
(
select dateadd(month, number,'20130101') MonthDay
from master.dbo.spt_values
where type='p'
)
,tRange as 
(
select vip,convert(varchar(6),min(date),112)+'01' MinMonth,convert(varchar(6),max(date),112)+'01' MaxMonth
from #xs
group by vip
),tDimDate as
(
select tr.vip,t.MonthDay StartDay,dateadd(d,-1,DATEADD(month,1,t.MonthDay)) EndDay
from tRange tr
join t 
on tr.MinMonth<=t.MonthDay
and tr.MaxMonth>=t.MonthDay
),tdata as (
select t.vip,EndDay
,DATEDIFF(d,(select top 1 date from #xs xs where xs.date <=t.EndDay and xs.vip=t.vip order by xs.date desc  ),EndDay) NoVisitDays
,count(x.date) VisitDays
,sum(isnull(sale,0)) Sales
from tDimDate t
left join #xs x
on x.[date] between t.StartDay and t.EndDay
and t.vip=x.vip
group by t.vip,EndDay
)
select vip,EndDay,NoVisitDays,VisitDays
,sum(VisitDays) over(partition by vip order by EndDay ) sumVisitDays
,sum(Sales) over(partition by vip order by EndDay ) sumSales
from tdata
哥你这语句是不是漏掉了什么？ 怎么我看sum(VisitDays) over(partition by vip order by EndDay ) sumVisitDays 这个怪怪的？[/quote] 你把order by EndDay去掉就不报错啦，
wqrz 2013-12-19
  • 打赏
  • 举报
 回复
引用 4 楼 stublue 的回复:

if object_id('tempdb..#xs') is not null
drop table #xs;

create table #xs(vip varchar(20), date datetime, sale float)
insert into #xs(vip, date, sale)
select 'A' as vip,'2013-1-1' as date,	230 as sale union all
select 'A','2013-1-5',	400 union all
select 'A','2013-3-6',	500 union all
select 'A','2013-4-30',600 union all
select 'B','2013-2-3',200 union all
select 'B','2013-3-5',300 union all
select 'B','2013-4-24',400 

;with t as 
(
select dateadd(month, number,'20130101') MonthDay
from master.dbo.spt_values
where type='p'
)
,tRange as 
(
select vip,convert(varchar(6),min(date),112)+'01' MinMonth,convert(varchar(6),max(date),112)+'01' MaxMonth
from #xs
group by vip
),tDimDate as
(
select tr.vip,t.MonthDay StartDay,dateadd(d,-1,DATEADD(month,1,t.MonthDay)) EndDay
from tRange tr
join t 
on tr.MinMonth<=t.MonthDay
and tr.MaxMonth>=t.MonthDay
),tdata as (
select t.vip,EndDay
,DATEDIFF(d,(select top 1 date from #xs xs where xs.date <=t.EndDay and xs.vip=t.vip order by xs.date desc  ),EndDay) NoVisitDays
,count(x.date) VisitDays
,sum(isnull(sale,0)) Sales
from tDimDate t
left join #xs x
on x.[date] between t.StartDay and t.EndDay
and t.vip=x.vip
group by t.vip,EndDay
)
select vip,EndDay,NoVisitDays,VisitDays
,sum(VisitDays) over(partition by vip order by EndDay ) sumVisitDays
,sum(Sales) over(partition by vip order by EndDay ) sumSales
from tdata
哥你这语句是不是漏掉了什么？ 怎么我看sum(VisitDays) over(partition by vip order by EndDay ) sumVisitDays 这个怪怪的？
gw6328 2013-12-19
  • 打赏
  • 举报
 回复 

create table #xs(vip varchar(20), date datetime, sale float)
insert into #xs(vip, date, sale)
select 'A' as vip,'2013-1-1' as date,    230 as sale union all
select 'A','2013-1-5',    400 union all
select 'A','2013-3-6',    500 union all
select 'A','2013-4-30',600 union all
select 'B','2013-2-3',200 union all
select 'B','2013-3-5',300 union all
select 'B','2013-4-24',400 
 
--select * from #xs

;WITH cte AS (
	SELECT vip,[date],DATEADD(dd,-1,DATEADD(mm,DATEDIFF(m,0,[date]),0)) bomonth,DATEADD(dd,-1,DATEADD(mm,DATEDIFF(m,0,[date])+1,0)) AS eomonth,sale
	FROM #xs 
),c2 AS (
	SELECT vip,eomonth,COUNT(1) AS monthCount,SUM(sale) AS sale FROM cte GROUP BY vip,[bomonth],eomonth
),c3 AS(
	SELECT a.number,b.vip FROM master..spt_values a JOIN (SELECT DISTINCT vip FROM #xs) b ON 1=1 WHERE a.type='p' AND a.number BETWEEN 1 AND 12 
),c4 AS (
	SELECT a.*,b.number,b.vip AS vipx FROM c2 a  RIGHT JOIN c3 b ON a.vip=b.vip AND MONTH(a.eomonth)=b.number
	--ORDER BY b.vip,b.number
),c5 AS (
	SELECT * FROM c4 a WHERE a.eomonth IS NOT NULL OR 
	EXISTS(SELECT 1 FROM c4 x WHERE a.vipx=x.vip AND  a.number<x.number) 
	AND EXISTS(SELECT 1 FROM c4 x WHERE a.vipx =x.vip AND a.number>x.number)

),c6 AS (
	SELECT vipx,CASE WHEN eomonth IS NOT NULL THEN eomonth ELSE (
			SELECT DATEADD(mm,a.number-MAX(number),MAX(eomonth)) FROM c5 x WHERE x.vipx=a.vipx 
		) END  AS eomonth
	,CASE WHEN monthCount is NOT NULL THEN monthCount ELSE 0 END AS monthCount
	,CASE WHEN sale is NOT NULL THEN sale ELSE 0 END AS sale
	FROM c5 a 
)
SELECT a.vipx,a.eomonth,a.monthCount
,(SELECT SUM(monthcount) FROM c6 x WHERE x.vipx=a.vipx AND x.eomonth<=a.eomonth) AS totalCount
,(SELECT SUM(sale) FROM c6 x WHERE x.vipx=a.vipx AND x.eomonth<=a.eomonth) AS TotalSale
FROM c6 a ORDER BY a.vipx,a.eomonth
--
/*
vipx                 eomonth                 monthCount  totalCount  TotalSale
-------------------- ----------------------- ----------- ----------- ----------------------
A                    2013-01-31 00:00:00.000 2           2           630
A                    2013-02-28 00:00:00.000 0           2           630
A                    2013-03-31 00:00:00.000 1           3           1130
A                    2013-04-30 00:00:00.000 1           4           1730
B                    2013-02-28 00:00:00.000 1           1           200
B                    2013-03-31 00:00:00.000 1           2           500
B                    2013-04-30 00:00:00.000 1           3           900
*/

DROP TABLE #xs;
只能做为参考,太乱了.我去
t101lian 2013-12-19
  • 打赏
  • 举报
 回复
引用 5 楼 wqrz 的回复:
额 我忘记说了 我还有一个日期的参照表：
表字段没给全， 这两个表怎么关联？
wqrz 2013-12-19
  • 打赏
  • 举报
 回复
额 我忘记说了 我还有一个日期的参照表：

Leon_He2014 2013-12-19
  • 打赏
  • 举报
 回复 

if object_id('tempdb..#xs') is not null
drop table #xs;

create table #xs(vip varchar(20), date datetime, sale float)
insert into #xs(vip, date, sale)
select 'A' as vip,'2013-1-1' as date,	230 as sale union all
select 'A','2013-1-5',	400 union all
select 'A','2013-3-6',	500 union all
select 'A','2013-4-30',600 union all
select 'B','2013-2-3',200 union all
select 'B','2013-3-5',300 union all
select 'B','2013-4-24',400 

;with t as 
(
select dateadd(month, number,'20130101') MonthDay
from master.dbo.spt_values
where type='p'
)
,tRange as 
(
select vip,convert(varchar(6),min(date),112)+'01' MinMonth,convert(varchar(6),max(date),112)+'01' MaxMonth
from #xs
group by vip
),tDimDate as
(
select tr.vip,t.MonthDay StartDay,dateadd(d,-1,DATEADD(month,1,t.MonthDay)) EndDay
from tRange tr
join t 
on tr.MinMonth<=t.MonthDay
and tr.MaxMonth>=t.MonthDay
),tdata as (
select t.vip,EndDay
,DATEDIFF(d,(select top 1 date from #xs xs where xs.date <=t.EndDay and xs.vip=t.vip order by xs.date desc  ),EndDay) NoVisitDays
,count(x.date) VisitDays
,sum(isnull(sale,0)) Sales
from tDimDate t
left join #xs x
on x.[date] between t.StartDay and t.EndDay
and t.vip=x.vip
group by t.vip,EndDay
)
select vip,EndDay,NoVisitDays,VisitDays
,sum(VisitDays) over(partition by vip order by EndDay ) sumVisitDays
,sum(Sales) over(partition by vip order by EndDay ) sumSales
from tdata
t101lian 2013-12-19
  • 打赏
  • 举报
 回复
把VIP 和date 这两列查出来都费劲，， 坐等大神
霜寒月冷 2013-12-19
  • 打赏
  • 举报
 回复
多久每消费可以这个[code=sql]select datediff (DAY ,date,DATEADD(MONTH, 1, date - DAY(date) + 1) - 1) as 多久没消费 from #xs[code]
Yole 2013-12-19
  • 打赏
  • 举报
 回复
要是动态的，一条语句有点儿费劲！ 1、要拼接出每个会员从开始消费月份到结束消费月的的所有月份。 2、然后左连接计算出每月消费次数 3、递归计算累计数
使用sql语句查询的数据按时间递增累加
使用sql语句将查询的数据中的值按时间递增累加
如何使用SQL Server数据库查询累计值
本文将为大家介绍如何使用SQL Server数据库查询累计值。
sql 累计占比_SQL语句-求总和的百分比
问题:求特定列中的值占总和的百分比。例如，确定所有DEPTNO 10工资占总工资的百分比(DEPTNO 10的工资在总工资中的百分比数)。解决方案总的来说，在SQL中计算占总数的百分比跟书面计算一样：先除后乘。这个例子要计算表EMP中DEPTNO 10工资所占的百分比。首先，算出DEPTNO 10的工资，然后除以表中的工资总和，最后一步，乘以100，则返回一个表示百分比的值。MySQL和Postg...
用SQL语句查询累计值
表 tday qty1 42 13 7SELECT t1.day, (SELECT SUM(t2.qty) FROM t AS t2 WHERE t2.day <= t1.day) as acqty -- 累计数量FROM t AS t1查询结果day qty1 42 53 12 转载于:https://www.cnblogs.com/JoeDZ...
sql语句实现累计数
前言，要实现按某个字段统计，直接用count/sum……group by语句就可以实现，但是要实现累计统计，比如按时间累计统计，从12月3号开始累计数据，比如：4号统计3、4号的数据，5号统计3、4、5号的数据，那么可以有类似如下的语句： with tab as(SELECT GIVE_DAY,COUNT(DISTINCT MOBILE) NUM FROM(SELECT a.*,t...
MS-SQL Server

34,873

社区成员

254,640

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章