各省的注册总量 select city,count(*) Counts from 注册表 group by city增长量 select aa.city,aa.counts-bb.counts as 增量 from (select city,count(*) Counts,convert(char(10),registerdate,120) as AADate from 注册表 group by city,convert(char(10),registerdate,120))AA left join (select city,count(*) Counts,convert(char(10),registerdate-1,120) as BBDate from 注册表 group by city,convert(char(10),registerdate-1,120))bb on aa.city=bb.city and aa.aadate=bb.bbdate
这只算出了各省每天的注册总量 我需要各省的注册总量和每天注册的增加量 各省的我明白,select count(*),city from base group by city 就可以了!但每天的注册增加量怎么给写到这个sql里!
上边是各省每天的增长量各省的注册总量: select count(id) , city from t1 group by city
select count(id) , convert(char(10),registerdate,120) as RegisterDate, city from t1 group by city , convert(char(10),registerdate,120)
create table #注册字段表 (id int,city varchar(10),registerdate datetime)insert #注册字段表 values(1,'aa','2001-1-1') insert #注册字段表 values(2,'aa','2001-1-2') insert #注册字段表 values(3,'aa','2001-1-2') insert #注册字段表 values(4,'aa','2001-1-3') insert #注册字段表 values(5,'aa','2001-1-3') insert #注册字段表 values(6,'aa','2001-1-4') select city,registerdate,(select count(*) from 注册字段表 where city=a.city) 注册总量, (select count(*) from #注册字段表 where city=a.city datediff(day,registerdate,a.registerdate)=1) t from (select city,cast(convert(char(10),registerdate,120) as datetime) registerdate from #注册字段表) a group by city,registerdatedrop table #注册字段表
select a1.city ,ndallq as 注册总量 , cast(a1.ndq*100/a2.ndallq as numeric(9,2))/100 as 每天的增长量 from (select city,convert(char(10),registerdate,101) as nd1 ,count(*) as ndq from 注册字段表 group by city,convert(char(10),registerdate,101) ) as a1 ,(select city, count(*) as ndallq from 注册字段表 group by city ) as a2 where a1.city = a2.city
--补充下 select a1.city ,ndallq as 注册总量 ,a1.ndq as 每天注册量 , cast(a1.ndq*100/a2.ndallq as numeric(9,2))/100 as 每天的增长量 from (select city,convert(char(10),registerdate,101) as nd1 ,count(*) as ndq from 注册字段表 group by city,convert(char(10),registerdate,101) ) as a1 ,(select city, count(*) as ndallq from 注册字段表 group by city ) as a2 where a1.city = a2.city
--不好意思,再补充下,这样比较清楚,TMD,我真罗嗦 select a1.city ,ndallq as 注册总量 ,a1.nd1 as 日子,a1.ndq as 每天注册量 , cast(a1.ndq*100/a2.ndallq as numeric(9,2))/100 as 每天的增长量 from (select city,convert(char(10),registerdate,101) as nd1 ,count(*) as ndq from 注册字段表 group by city,convert(char(10),registerdate,101) ) as a1 ,(select city, count(*) as ndallq from 注册字段表 group by city ) as a2 where a1.city = a2.city
create table #注册字段表 (id int,city varchar(10),registerdate datetime)insert #注册字段表 values(1,'aa','2001-1-1') insert #注册字段表 values(2,'aa','2001-1-2') insert #注册字段表 values(3,'aa','2001-1-2') insert #注册字段表 values(4,'aa','2001-1-3') insert #注册字段表 values(5,'aa','2001-1-3') insert #注册字段表 values(6,'aa','2001-1-4') select city,registerdate,(select count(*) from #注册字段表 where city=a.city) 注册总量, 天数量-(select count(*) from #注册字段表 where city=a.city and datediff(day,registerdate,a.registerdate)=1) 天增量 from (select city,cast(convert(char(10),registerdate,120) as datetime) registerdate,count(*) 天数量 from #注册字段表 group by city,cast(convert(char(10),registerdate,120) as datetime)) a drop table #注册字段表
一句搞定:select city ,registerdate 前一个有效日期 , (select count(*) from T where city=a.city and datediff(day,registerdate,a.registerdate)=0 ) 前一个有效日期注册量 , (select min(registerdate) from T where city = a.city and datediff(day,a.registerdate,registerdate)>0 ) 后一个有效日期 , (select count(*) from T where city = a.city and registerdate = (select min(registerdate) from T where city = a.city and datediff(day,a.registerdate,registerdate)>0 ) ) 后一个有效日期注册量 ,(select count(*) from T where city = a.city and registerdate = (select min(registerdate) from T where city = a.city and datediff(day,a.registerdate,registerdate)>0 ) ) - (select count(*) from T where city=a.city and datediff(day,registerdate,a.registerdate)=0 ) 差量
from T a group by city,registerdate
相当标准的SQL, 没有使用派生表,基本兼容多种数据库 SQL 语法:select city ,registerdate 前一个有效日期 , (select count(*) from T where city=a.city and datediff(day,registerdate,a.registerdate)=0 ) 前一个有效日期注册量 , (select min(registerdate) from T where city = a.city and datediff(day,a.registerdate,registerdate)>0 ) 后一个有效日期 , (select count(*) from T where city = a.city and registerdate = (select min(registerdate) from T where city = a.city and datediff(day,a.registerdate,registerdate)>0 ) ) 后一个有效日期注册量 ,(select count(*) from T where city = a.city and registerdate = (select min(registerdate) from T where city = a.city and datediff(day,a.registerdate,registerdate)>0 ) ) - (select count(*) from T where city=a.city and datediff(day,registerdate,a.registerdate)=0 ) 差量
from T a group by city,registerdate
哈哈。。小干部,是我理解错了? 不是"各省注册总量,以及各省今天的注册人数比昨天的注册人数多多少"吗?create table #注册字段表 (id int,city varchar(10),registerdate datetime)insert #注册字段表 values(1,'aa','2001-1-1') insert #注册字段表 values(2,'aa','2001-1-2') insert #注册字段表 values(3,'aa','2001-1-2') insert #注册字段表 values(4,'aa','2001-1-3') insert #注册字段表 values(5,'aa','2001-1-3') insert #注册字段表 values(6,'aa','2001-1-4') insert #注册字段表 values(1,'bb','2001-1-1') insert #注册字段表 values(2,'bb','2001-1-2') insert #注册字段表 values(3,'bb','2001-1-2') insert #注册字段表 values(4,'bb','2001-1-3') insert #注册字段表 values(5,'bb','2001-1-3') insert #注册字段表 values(6,'bb','2001-1-3')select city,registerdate,(select count(*) from #注册字段表 where city=a.city) 注册总量,天数量-(select count(*) from #注册字段表 where city=a.city and datediff(day,registerdate,a.registerdate)=1) 天增量 from (select city,cast(convert(char(10),registerdate,120) as datetime) registerdate,count(*) 天数量 from #注册字段表 group by city,cast(convert(char(10),registerdate,120) as datetime)) a order by citydrop table #注册字段表你看看!
数据的日期要不连续呢? 我认为我是对的!我的思路是: 求出同一 city 中的比registerdate大的最小日期的数量,减掉 registerdate 的数量!不难! 典型的"自相关子查询应用"!
这么复杂的回应,结果一看问题,原来这么简单呀!?不知我理解得对不对呦?select s.city,s.[日期],s.[总量],s.[总量]-t.[总量] [增长量] from (select city,convert(varchar,registerdate,102) [日期],count(*) [总量] from T group by city,convert(varchar,registerdate,102)) s inner join (select city,convert(varchar,registerdate,102) [日期],count(*) [总量] from T group by city,convert(varchar,registerdate,102)) t where s.[日期]=t.[日期]+1
sorry! 上边错把 on 写成 where 喽!
select s.city,s.[日期],s.[总量],s.[总量]-t.[总量] [增长量] from (select city,convert(varchar,registerdate,102) [日期],count(*) [总量] from T group by city,convert(varchar,registerdate,102)) s inner join (select city,convert(varchar,registerdate,102) [日期],count(*) [总量] from T group by city,convert(varchar,registerdate,102)) t on s.city=t.city and s.[日期]=t.[日期]+1
select city,count(*) Counts from 注册表 group by city增长量
select aa.city,aa.counts-bb.counts as 增量 from
(select city,count(*) Counts,convert(char(10),registerdate,120) as AADate from 注册表 group by city,convert(char(10),registerdate,120))AA
left join
(select city,count(*) Counts,convert(char(10),registerdate-1,120) as BBDate from 注册表 group by city,convert(char(10),registerdate-1,120))bb
on aa.city=bb.city and aa.aadate=bb.bbdate
我需要各省的注册总量和每天注册的增加量
各省的我明白,select count(*),city from base group by city 就可以了!但每天的注册增加量怎么给写到这个sql里!
select count(id) , city
from t1
group by city
另外,我希望能将它们写在一个sql里
拜托,再想想法子,偶都头大了一下午了~
from t1
group by city , registerdate 采取措施防止日期减后为0的情况
各省注册总量,以及各省今天的注册人数比昨天的注册人数多多少
from t1
group by city , convert(char(10),registerdate,120)
insert #注册字段表 values(2,'aa','2001-1-2')
insert #注册字段表 values(3,'aa','2001-1-2')
insert #注册字段表 values(4,'aa','2001-1-3')
insert #注册字段表 values(5,'aa','2001-1-3')
insert #注册字段表 values(6,'aa','2001-1-4')
select city,registerdate,(select count(*) from 注册字段表 where city=a.city) 注册总量,
(select count(*) from #注册字段表 where city=a.city datediff(day,registerdate,a.registerdate)=1) t
from (select city,cast(convert(char(10),registerdate,120) as datetime) registerdate from #注册字段表) a group by city,registerdatedrop table #注册字段表
from 注册字段表
group by city,convert(char(10),registerdate,101) ) as a1 ,(select city, count(*) as ndallq from 注册字段表
group by city ) as a2 where a1.city = a2.city
select a1.city ,ndallq as 注册总量 ,a1.ndq as 每天注册量 , cast(a1.ndq*100/a2.ndallq as numeric(9,2))/100 as 每天的增长量 from (select city,convert(char(10),registerdate,101) as nd1 ,count(*) as ndq
from 注册字段表
group by city,convert(char(10),registerdate,101) ) as a1 ,(select city, count(*) as ndallq from 注册字段表
group by city ) as a2 where a1.city = a2.city
select a1.city ,ndallq as 注册总量 ,a1.nd1 as 日子,a1.ndq as 每天注册量 , cast(a1.ndq*100/a2.ndallq as numeric(9,2))/100 as 每天的增长量 from (select city,convert(char(10),registerdate,101) as nd1 ,count(*) as ndq
from 注册字段表
group by city,convert(char(10),registerdate,101) ) as a1 ,(select city, count(*) as ndallq from 注册字段表
group by city ) as a2 where a1.city = a2.city
insert #注册字段表 values(2,'aa','2001-1-2')
insert #注册字段表 values(3,'aa','2001-1-2')
insert #注册字段表 values(4,'aa','2001-1-3')
insert #注册字段表 values(5,'aa','2001-1-3')
insert #注册字段表 values(6,'aa','2001-1-4')
select city,registerdate,(select count(*) from #注册字段表 where city=a.city) 注册总量,
天数量-(select count(*) from #注册字段表 where city=a.city and datediff(day,registerdate,a.registerdate)=1) 天增量
from (select city,cast(convert(char(10),registerdate,120) as datetime) registerdate,count(*) 天数量 from #注册字段表 group by city,cast(convert(char(10),registerdate,120) as datetime)) a drop table #注册字段表
,registerdate 前一个有效日期
, (select count(*)
from T
where city=a.city
and datediff(day,registerdate,a.registerdate)=0
) 前一个有效日期注册量
, (select min(registerdate)
from T
where city = a.city
and datediff(day,a.registerdate,registerdate)>0
) 后一个有效日期
, (select count(*)
from T
where city = a.city
and registerdate = (select min(registerdate)
from T
where city = a.city
and datediff(day,a.registerdate,registerdate)>0
)
) 后一个有效日期注册量 ,(select count(*)
from T
where city = a.city
and registerdate = (select min(registerdate)
from T
where city = a.city
and datediff(day,a.registerdate,registerdate)>0
)
)
-
(select count(*)
from T
where city=a.city
and datediff(day,registerdate,a.registerdate)=0
) 差量
from T a
group by city,registerdate
没有使用派生表,基本兼容多种数据库 SQL 语法:select city
,registerdate 前一个有效日期
, (select count(*)
from T
where city=a.city
and datediff(day,registerdate,a.registerdate)=0
) 前一个有效日期注册量
, (select min(registerdate)
from T
where city = a.city
and datediff(day,a.registerdate,registerdate)>0
) 后一个有效日期
, (select count(*)
from T
where city = a.city
and registerdate = (select min(registerdate)
from T
where city = a.city
and datediff(day,a.registerdate,registerdate)>0
)
) 后一个有效日期注册量 ,(select count(*)
from T
where city = a.city
and registerdate = (select min(registerdate)
from T
where city = a.city
and datediff(day,a.registerdate,registerdate)>0
)
)
-
(select count(*)
from T
where city=a.city
and datediff(day,registerdate,a.registerdate)=0
) 差量
from T a
group by city,registerdate
不是"各省注册总量,以及各省今天的注册人数比昨天的注册人数多多少"吗?create table #注册字段表 (id int,city varchar(10),registerdate datetime)insert #注册字段表 values(1,'aa','2001-1-1')
insert #注册字段表 values(2,'aa','2001-1-2')
insert #注册字段表 values(3,'aa','2001-1-2')
insert #注册字段表 values(4,'aa','2001-1-3')
insert #注册字段表 values(5,'aa','2001-1-3')
insert #注册字段表 values(6,'aa','2001-1-4')
insert #注册字段表 values(1,'bb','2001-1-1')
insert #注册字段表 values(2,'bb','2001-1-2')
insert #注册字段表 values(3,'bb','2001-1-2')
insert #注册字段表 values(4,'bb','2001-1-3')
insert #注册字段表 values(5,'bb','2001-1-3')
insert #注册字段表 values(6,'bb','2001-1-3')select city,registerdate,(select count(*) from #注册字段表 where city=a.city) 注册总量,天数量-(select count(*) from #注册字段表 where city=a.city and datediff(day,registerdate,a.registerdate)=1) 天增量 from (select city,cast(convert(char(10),registerdate,120) as datetime) registerdate,count(*) 天数量 from #注册字段表 group by city,cast(convert(char(10),registerdate,120) as datetime)) a order by citydrop table #注册字段表你看看!
我认为我是对的!我的思路是:
求出同一 city 中的比registerdate大的最小日期的数量,减掉 registerdate 的数量!不难!
典型的"自相关子查询应用"!
from
(select city,convert(varchar,registerdate,102) [日期],count(*) [总量] from T group by city,convert(varchar,registerdate,102)) s
inner join
(select city,convert(varchar,registerdate,102) [日期],count(*) [总量] from T group by city,convert(varchar,registerdate,102)) t
where s.[日期]=t.[日期]+1
from
(select city,convert(varchar,registerdate,102) [日期],count(*) [总量] from T group by city,convert(varchar,registerdate,102)) s
inner join
(select city,convert(varchar,registerdate,102) [日期],count(*) [总量] from T group by city,convert(varchar,registerdate,102)) t
on s.city=t.city and s.[日期]=t.[日期]+1