declare @x char(10) declare @y char(20) create table #bb(xm char(10),jg char(20)) declare cur_aa cursor for select xm,jg from t2 open cur_aa fetch next from cur_aa into @x,@y while(@@fetch_status=0) begin insert into #bb(xm,jg) select @x,substring(@y,1,2) fetch next from cur_aa into @x,@y end Close cur_aa deallocate cur_aa select jg as '省份',count(*) as '人数' from #bb group by jg union select '总计',count(*) from #bb drop table #bb
declare @x char(10) declare @y char(20) create table #bb(xm char(10),jg char(20)) declare cur_aa cursor for select xm,jg from t2 open cur_aa fetch next from cur_aa into @x,@y while(@@fetch_status=0) begin insert into #bb(xm,jg) select @x,substring(@y,1,2) fetch next from cur_aa into @x,@y end Close cur_aa deallocate cur_aa select jg as '省份',count(*) as '人数' from #bb group by jg union select '总计',count(*) from #bb order by jg drop table #bb
select province,count(xm) from t1 left join t2 on charindex(province,jg)<>0 group by province union select '总计',count(*) from t2
declare @t1 table (province varchar(100)) declare @t2 table (xm varchar(100),jg varchar(100))insert into @t1 select '广东' union select '广西' union select '湖南'insert into @t2 select 'aa','广东省' union select 'bb','广东' union select 'cc','广东省广州' union select 'dd','广西' union select 'ee','广西省' union select 'ff','广西省桂林市' union select 'gg','湖南'select province,count(xm) as 人数 from @t1 left join @t2 on charindex(province,jg)<>0 group by province union select '总计',count(*) from @t2 结果:广东 3 广西 3 湖南 1 总计 7
declare @x char(10) declare @y char(20) create table #bb(xm char(10),jg char(20)) declare cur_aa cursor for select xm,jg from t2 open cur_aa fetch next from cur_aa into @x,@y while(@@fetch_status=0) begin insert into #bb(xm,jg) select @x,substring(@y,1,2) fetch next from cur_aa into @x,@y end Close cur_aa deallocate cur_aa select jg as '省份',count(*) as '人数' from #bb group by jg union select '总计',count(*) from #bb order by jg drop table #bb 结果与你的要求完全稳合: 省份 人数 广东 3 广西 3 湖南 1 总计 7
select 省份=case grouping(a.province) when 1 then '总计' else a.province end) ,人数=count(b.xm) from t1 a ,t2 b where b.jg like a.province+'%' group by a.province with rollup
上面的语句多写了一个括号,改:select 省份=case grouping(a.province) when 1 then '总计' else a.province end ,人数=count(b.xm) from t1 a ,t2 b where b.jg like a.province+'%' group by a.province with rollup
下面是数据测试: declare @t1 table (province varchar(100)) declare @t2 table (xm varchar(100),jg varchar(100))insert into @t1 select '广东' union select '广西' union select '湖南'insert into @t2 select 'aa','广东省' union select 'bb','广东' union select 'cc','广东省广州' union select 'dd','广西' union select 'ee','广西省' union select 'ff','广西省桂林市' union select 'gg','湖南'select 省份=case grouping(a.province) when 1 then '总计' else a.province end ,人数=count(b.xm) from @t1 a ,@t2 b where b.jg like a.province+'%' group by a.province with rollup/*--结果: 省份 人数 广东 3 广西 3 湖南 1 总计 7 --*/
create table t1(province nvarchar(1000)) insert into t1 values('广东') insert into t1 values('广西') insert into t1 values('湖南')create table t2(xm nvarchar(1000),jg nvarchar(100)) insert into t2 values('aa', '广东省') insert into t2 values('bb', '广东') insert into t2 values('cc', '广东省广州') insert into t2 values('dd', '广西') insert into t2 values('ee', '广西省') insert into t2 values('ff', '广西省桂林市') insert into t2 values('gg', '湖南') select left(jg,2) as 省份,count(xm) as 人数 from t2 join t1 on t1.province=left(jg,2) group by left(jg,2) union select '总计',count(*) from t2
select left(jg,2) as 省份,count(xm) as 人数 from t2 join t1 on t1.province=left(jg,2) group by left(jg,2) with rollup
declare @y char(20)
create table #bb(xm char(10),jg char(20))
declare cur_aa cursor for
select xm,jg
from t2
open cur_aa
fetch next from cur_aa into @x,@y
while(@@fetch_status=0)
begin
insert into #bb(xm,jg)
select @x,substring(@y,1,2)
fetch next from cur_aa into @x,@y
end
Close cur_aa
deallocate cur_aa
select jg as '省份',count(*) as '人数' from #bb
group by jg
union
select '总计',count(*) from #bb
drop table #bb
declare @x char(10)
declare @y char(20)
create table #bb(xm char(10),jg char(20))
declare cur_aa cursor for
select xm,jg
from t2
open cur_aa
fetch next from cur_aa into @x,@y
while(@@fetch_status=0)
begin
insert into #bb(xm,jg)
select @x,substring(@y,1,2)
fetch next from cur_aa into @x,@y
end
Close cur_aa
deallocate cur_aa
select jg as '省份',count(*) as '人数' from #bb
group by jg
union
select '总计',count(*) from #bb
order by jg
drop table #bb
union select '总计',count(*) from t2
declare @t2 table (xm varchar(100),jg varchar(100))insert into @t1
select '广东'
union
select '广西'
union
select '湖南'insert into @t2
select 'aa','广东省'
union
select 'bb','广东'
union
select 'cc','广东省广州'
union
select 'dd','广西'
union
select 'ee','广西省'
union
select 'ff','广西省桂林市'
union
select 'gg','湖南'select province,count(xm) as 人数 from @t1 left join @t2 on charindex(province,jg)<>0 group by province
union select '总计',count(*) from @t2
结果:广东 3
广西 3
湖南 1
总计 7
declare @y char(20)
create table #bb(xm char(10),jg char(20))
declare cur_aa cursor for
select xm,jg
from t2
open cur_aa
fetch next from cur_aa into @x,@y
while(@@fetch_status=0)
begin
insert into #bb(xm,jg)
select @x,substring(@y,1,2)
fetch next from cur_aa into @x,@y
end
Close cur_aa
deallocate cur_aa
select jg as '省份',count(*) as '人数' from #bb
group by jg
union
select '总计',count(*) from #bb
order by jg
drop table #bb
结果与你的要求完全稳合:
省份 人数
广东 3
广西 3
湖南 1
总计 7
,人数=count(b.xm)
from t1 a ,t2 b where b.jg like a.province+'%'
group by a.province with rollup
,人数=count(b.xm)
from t1 a ,t2 b where b.jg like a.province+'%'
group by a.province with rollup
declare @t1 table (province varchar(100))
declare @t2 table (xm varchar(100),jg varchar(100))insert into @t1
select '广东'
union
select '广西'
union
select '湖南'insert into @t2
select 'aa','广东省'
union
select 'bb','广东'
union
select 'cc','广东省广州'
union
select 'dd','广西'
union
select 'ee','广西省'
union
select 'ff','广西省桂林市'
union
select 'gg','湖南'select 省份=case grouping(a.province) when 1 then '总计' else a.province end
,人数=count(b.xm)
from @t1 a ,@t2 b where b.jg like a.province+'%'
group by a.province with rollup/*--结果:
省份 人数
广东 3
广西 3
湖南 1
总计 7
--*/
create table t1(province nvarchar(1000))
insert into t1 values('广东')
insert into t1 values('广西')
insert into t1 values('湖南')create table t2(xm nvarchar(1000),jg nvarchar(100))
insert into t2 values('aa', '广东省')
insert into t2 values('bb', '广东')
insert into t2 values('cc', '广东省广州')
insert into t2 values('dd', '广西')
insert into t2 values('ee', '广西省')
insert into t2 values('ff', '广西省桂林市')
insert into t2 values('gg', '湖南')
select left(jg,2) as 省份,count(xm) as 人数
from t2 join t1 on t1.province=left(jg,2)
group by left(jg,2)
union select '总计',count(*) from t2
select left(jg,2) as 省份,count(xm) as 人数
from t2 join t1 on t1.province=left(jg,2)
group by left(jg,2)
with rollup