SELECT temp.*, baseinfo.group FROM (SELECT extendtable.id, extendtable.name, COUNT(extendtable.name) AS count, SUM(extendtable.variable) AS sumVar FROM ( SELECT * FROM extendtable1 UNION ALL SELECT * FROM extendtable3 UNION ALL SELECT * FROM extendtable4 UNION ALL SELECT * FROM extendtable5 ..... )extendtable WHERE extendtable.variable > 0 GROUP BY extendtable.id, extendtable.name)temp INNER jOIN baseinfo ON temp.id = baseinfo.id ;SELECT baseinfo.group, COUNT(baseinfo.group) FROM (SELECT extendtable.id, extendtable.name, COUNT(extendtable.name) AS count, SUM(extendtable.variable) AS sumVar FROM ( SELECT * FROM extendtable1 UNION ALL SELECT * FROM extendtable3 UNION ALL SELECT * FROM extendtable4 UNION ALL SELECT * FROM extendtable5 ..... )extendtable WHERE extendtable.variable > 0 GROUP BY extendtable.id, extendtable.name)temp INNER jOIN baseinfo ON temp.id = baseinfo.id GROUP BY baseinfo.group ;
create table #a ( id int , name varchar(50) ) insert into #a select 1,'中国' Union all select 2,'美国' Union all select 3,'英国' Union all select 4,'法国' create table #b ( cid int , name varchar(50), score int ) insert into #b(cid,name,score) select 1,'中国',5 Union all select 1,'中国',10 Union all select 2,'美国',5 Union all select 3,'英国',8 Union all select 3,'英国',5 create table #c ( cid int , name varchar(50), score int ) insert into #c(cid,name,score) select 1,'中国',5 Union all select 2,'美国',12 Union all select 2,'美国',5 Union all select 3,'英国',8 Union all select 3,'英国',6 select b.数量,b.区间,#a.name from ( select cid,name,COUNT(name) as 数量,CAST( MIN(score) as varchar(20)) +'-'+CAST( MAX(score) as varchar(20)) as 区间 from ( select cid,name,score from #b union all select cid,name,score from #c )as temp group by name,cid )b right join #a on #a.id=b.ciddrop table #a drop table #b drop table #c
FROM
(SELECT
extendtable.id,
extendtable.name,
COUNT(extendtable.name) AS count,
SUM(extendtable.variable) AS sumVar
FROM
(
SELECT * FROM extendtable1
UNION ALL
SELECT * FROM extendtable3
UNION ALL
SELECT * FROM extendtable4
UNION ALL
SELECT * FROM extendtable5
.....
)extendtable
WHERE extendtable.variable > 0
GROUP BY extendtable.id, extendtable.name)temp
INNER jOIN baseinfo
ON temp.id = baseinfo.id
;SELECT baseinfo.group, COUNT(baseinfo.group)
FROM
(SELECT
extendtable.id,
extendtable.name,
COUNT(extendtable.name) AS count,
SUM(extendtable.variable) AS sumVar
FROM
(
SELECT * FROM extendtable1
UNION ALL
SELECT * FROM extendtable3
UNION ALL
SELECT * FROM extendtable4
UNION ALL
SELECT * FROM extendtable5
.....
)extendtable
WHERE extendtable.variable > 0
GROUP BY extendtable.id, extendtable.name)temp
INNER jOIN baseinfo
ON temp.id = baseinfo.id
GROUP BY baseinfo.group
;
(
id int ,
name varchar(50)
)
insert into #a
select 1,'中国' Union all
select 2,'美国' Union all
select 3,'英国' Union all
select 4,'法国' create table #b
(
cid int ,
name varchar(50),
score int
)
insert into #b(cid,name,score)
select 1,'中国',5 Union all
select 1,'中国',10 Union all
select 2,'美国',5 Union all
select 3,'英国',8 Union all
select 3,'英国',5
create table #c
(
cid int ,
name varchar(50),
score int
)
insert into #c(cid,name,score)
select 1,'中国',5 Union all
select 2,'美国',12 Union all
select 2,'美国',5 Union all
select 3,'英国',8 Union all
select 3,'英国',6 select b.数量,b.区间,#a.name from
(
select cid,name,COUNT(name) as 数量,CAST( MIN(score) as varchar(20)) +'-'+CAST( MAX(score) as varchar(20)) as 区间 from
(
select cid,name,score from #b
union all
select cid,name,score from #c
)as temp group by name,cid
)b
right join #a on #a.id=b.ciddrop table #a
drop table #b
drop table #c