1. select convert(char,date,23) date,count(1) cnt from T group by convert(char,date,23)2. select *,count(1)over(partition by convert(char,date,23)) cnt from tb
1. select convert(varchar(10),date,120) as [date],sum(pv) pv from t group by convert(varchar(10),date,120)
1. select convert(varchar(10),date,120) as [date],sum(pv) pv from tb group by convert(varchar(10),date,120) 2. select lid,sum(pv) as pv from tb where datediff(dd,date,getdate())=0 group by lid
1.根据date计算累计的pvselect pv=count(1), convert(char,date,23) date, from T group by convert(char,date,23)2.以栏目号为列(有几个栏目就有几列),查询当日访问量declare @sql varchar(8000) set @sql = 'select convert(char,date,23) date ' select @sql = @sql + ' , sum(case ltrim(lid) when ''' + ltrim(lid) + ''' then 1 else 0 end) [' + ltrim(lid) + ']' from (select distinct lid from T) as a set @sql = @sql + ' from T group by convert(char,date,23) date' exec(@sql)
1.select distinct a.dt,(select sum(pv) as pv from tb where convert(int,dt)<=convert(int,a.dt) ) as pv from tb as a group by a.dt 2.select c.dt,isnull(a.pv,0) as '001',isnull(b.pv,0) as '002' from (select distinct dt from tb) as c left join (select dt,pv,'m' as m from tb where lid='001') as a on a.dt=c.dt left join (select dt,pv,'m' as m from tb where lid='002') as b on c.dt=b.dt and a.m=b.m
--第一问 select distinct a.dt,(select sum(pv) as pv from tb where convert(int,dt)<=convert(int,a.dt) ) as pv from tb as a group by a.dt --第二问 select c.dt,isnull(a.pv,0) as '001',isnull(b.pv,0) as '002' from (select distinct dt from tb) as c left join (select dt,pv,'m' as m from tb where lid='001') as a on a.dt=c.dt left join (select dt,pv,'m' as m from tb where lid='002') as b on c.dt=b.dt and a.m=b.m --像第二问最好写成过程或者函数,这种方法如果有10个项目则union连接10个 --不知道一句话有没有更好的办法
第一个 不知道是你写错了还是怎么 第一个查询,我要给出:date pv 20100526 3 20100527 10 这不要改是7? 要是7的话 1:select data,sum(pv) from test group by data 第二个要行转列 declare @s nvarchar(4000) set @s='' select @s=@s+','+quotename([lid])+'=max(case when [lid]='+quotename([lid],'''')+' then [pv] else 0 end)' from test group by [lid]exec('select [data]' +@s+' from test group by [data]')
上面代码有错 要改是求和 不是max 呵呵 declare @s nvarchar(4000) set @s='' select @s=@s+','+quotename([lid])+'=sum(case when [lid]='+quotename([lid],'''')+' then [pv] else 0 end)' from test group by [lid]exec('select [data]' +@s+' from test group by [data]')
你的问题是累计求和问题.Ⅰ第一个问题 1)表结构和数据相关 CREATE TABLE #test (date datetime, lid varchar(20), pv int)--DELETE FROM #test INSERT INTO #test SELECT '20100526', '001', 1 UNION ALl SELECT '20100526','002' ,2 UNION ALL SELECT '20100527', '001', 3 UNION ALL SELECT '20100527' ,'002', 4 UNION ALL SELECT '20100528' ,'001', 2 UNION ALL SELECT '20100528' ,'002', 3 UNION ALL SELECT '20100528' ,'003', 4--SELECT * FROM #test2) SELECT DISTINCT date, (SELECT SUM(pv) FROM #test b WHERE a.date >= b.date) AS Running_total FROM #test a GROUP BY a.lid,a.date ORDER BY 2--按照date累加取和 date Running_total ----------------------- ------------- 2010-05-26 00:00:00.000 3 2010-05-27 00:00:00.000 10 2010-05-28 00:00:00.000 19(3 row(s) affected) SELECT date,lid, (SELECT SUM(pv) FROM #test b WHERE a.date >= b.date AND a.lid = b.lid) AS Running_total FROM #test a GROUP BY date,lid ORDER BY 3--按照date和lid累加取和 date lid Running_total ----------------------- -------------------- ------------- 2010-05-26 00:00:00.000 001 1 2010-05-26 00:00:00.000 002 2 2010-05-27 00:00:00.000 001 4 2010-05-28 00:00:00.000 003 4 2010-05-27 00:00:00.000 002 6 2010-05-28 00:00:00.000 001 6 2010-05-28 00:00:00.000 002 9(7 row(s) affected)Ⅱ第二个问题 参照楼上各位.
select convert(char,date,23) date,count(1) cnt from T group by convert(char,date,23)2.
select *,count(1)over(partition by convert(char,date,23)) cnt from tb
1.
select convert(varchar(10),date,120) as [date],sum(pv) pv
from t
group by convert(varchar(10),date,120)
select
convert(varchar(10),date,120) as [date],sum(pv) pv
from
tb
group by
convert(varchar(10),date,120)
2.
select
lid,sum(pv) as pv
from
tb
where
datediff(dd,date,getdate())=0
group by
lid
set @sql = 'select convert(char,date,23) date '
select @sql = @sql + ' , sum(case ltrim(lid) when ''' + ltrim(lid) + ''' then 1 else 0 end) [' + ltrim(lid) + ']'
from (select distinct lid from T) as a
set @sql = @sql + ' from T group by convert(char,date,23) date'
exec(@sql)
20100526 001 1
20100526 002 2
20100527 001 3
20100527 002 4第一个查询,我要给出:date pv
20100526 3
20100527 10第二个查询,我要给出:date 001 002
20100526 1 2
20100527 3 4
group by a.dt
2.select c.dt,isnull(a.pv,0) as '001',isnull(b.pv,0) as '002'
from
(select distinct dt from tb) as c left join
(select dt,pv,'m' as m from tb where lid='001') as a
on a.dt=c.dt left join
(select dt,pv,'m' as m from tb where lid='002') as b on c.dt=b.dt and a.m=b.m
--第一问
select distinct a.dt,(select sum(pv) as pv from tb where convert(int,dt)<=convert(int,a.dt) ) as pv from tb as a
group by a.dt
--第二问
select c.dt,isnull(a.pv,0) as '001',isnull(b.pv,0) as '002'
from
(select distinct dt from tb) as c left join
(select dt,pv,'m' as m from tb where lid='001') as a
on a.dt=c.dt left join
(select dt,pv,'m' as m from tb where lid='002') as b on c.dt=b.dt and a.m=b.m
--像第二问最好写成过程或者函数,这种方法如果有10个项目则union连接10个
--不知道一句话有没有更好的办法
第一个查询,我要给出:date pv
20100526 3
20100527 10 这不要改是7?
要是7的话
1:select data,sum(pv) from test group by data
第二个要行转列
declare @s nvarchar(4000)
set @s=''
select @s=@s+','+quotename([lid])+'=max(case when [lid]='+quotename([lid],'''')+' then [pv] else 0 end)'
from test group by [lid]exec('select [data]' +@s+' from test group by [data]')
declare @s nvarchar(4000)
set @s=''
select @s=@s+','+quotename([lid])+'=sum(case when [lid]='+quotename([lid],'''')+' then [pv] else 0 end)'
from test group by [lid]exec('select [data]' +@s+' from test group by [data]')
1)表结构和数据相关
CREATE TABLE #test
(date datetime,
lid varchar(20),
pv int)--DELETE FROM #test
INSERT INTO #test
SELECT '20100526', '001', 1
UNION ALl
SELECT '20100526','002' ,2
UNION ALL
SELECT '20100527', '001', 3
UNION ALL
SELECT '20100527' ,'002', 4
UNION ALL
SELECT '20100528' ,'001', 2
UNION ALL
SELECT '20100528' ,'002', 3
UNION ALL
SELECT '20100528' ,'003', 4--SELECT * FROM #test2)
SELECT DISTINCT date,
(SELECT SUM(pv) FROM #test b WHERE a.date >= b.date) AS Running_total
FROM #test a
GROUP BY a.lid,a.date
ORDER BY 2--按照date累加取和
date Running_total
----------------------- -------------
2010-05-26 00:00:00.000 3
2010-05-27 00:00:00.000 10
2010-05-28 00:00:00.000 19(3 row(s) affected)
SELECT date,lid,
(SELECT SUM(pv) FROM #test b WHERE a.date >= b.date AND a.lid = b.lid) AS Running_total
FROM #test a
GROUP BY date,lid
ORDER BY 3--按照date和lid累加取和
date lid Running_total
----------------------- -------------------- -------------
2010-05-26 00:00:00.000 001 1
2010-05-26 00:00:00.000 002 2
2010-05-27 00:00:00.000 001 4
2010-05-28 00:00:00.000 003 4
2010-05-27 00:00:00.000 002 6
2010-05-28 00:00:00.000 001 6
2010-05-28 00:00:00.000 002 9(7 row(s) affected)Ⅱ第二个问题 参照楼上各位.