请教一个union，sum 和group by一起用的语句

[w_zhen_x]

有两个结构完全一样的表，我想将两个表汇聚到一起形成一张表，语句如下

create view v1 as Select id as id, type as type, sum(s1) as s1, sum(s2) as s2, min(s3) as s3, max(s4) as s4 From t1 group by id,type union Select id as id, type as type, sum(s1) as s1, sum(s2) as s2, min(s3) as s3, max(s4) as s4 From t2 group by id,type

执行完之后查询 select * from v1

发现2张表分别汇聚后再通过union形成了一张表。并不是我想要的。 要达到我想要的，我还需要在这个视图的基础上再次汇聚才可以。
create view v2 as Select id as id, type as type, sum(s1) as s1, sum(s2) as s2, min(s3) as s3, max(s4) as s4 From v1 group by id,type

这个SQL怎么写，可以一次形成我两句才能达到的效果

[w_zhen_x]

[Quote=引用楼主 w_zhen_x 的回复:]
有两个结构完全一样的表，我想将两个表汇聚到一起形成一张表，语句如下

create view v1 as Select id as id, type as type, sum(s1) as s1, sum(s2) as s2, min(s3) as s3, max(s4) as s4 From t1 group by id,type union Select id as id, ty……
[/Quote]

我之前这么试过，提示错误，View's SELECT contains a subquery in the FROM clause，一直没搞明白哪错了

[w_zhen_x]

[Quote=引用 1 楼 lxq19851204 的回复:]

SQL code
Select * From (
(Select id as id, type as type, sum(s1) as s1, sum(s2) as s2, min(s3) as s3, max(s4) as s4 From t1 group by id,type)
union all
(Select id as id, type as type, sum(s1) as s1,……
[/Quote]
跟楼上的一样，好像不支持子查询

[rucypli]

做两个视图不可以吗

[w_zhen_x]

[Quote=引用 5 楼 rucypli 的回复:]

做两个视图不可以吗
[/Quote]
我目前就是这么做的，想知道有没有更好的办法

[lxq19851204]

Select * From (
(Select id as id, type as type, sum(s1) as s1, sum(s2) as s2, min(s3) as s3, max(s4) as s4 From t1 group by id,type)
union all
(Select id as id, type as type, sum(s1) as s1, sum(s2) as s2, min(s3) as s3, max(s4) as s4 From t2 group by id,type))T

[xiaoweqicss]

select sum(s3), sum(s1) from (select s3,s1 from t1 union select s3,s1 from t2) as
ss;

[w_zhen_x]

没有人知道吗

[janylilala]

我觉得2楼应该是你想要的答案，我把他的意思写全点：
create view as
select id as id, type as type,sum(s1) as s1, sum(s2) as s2, min(s3) as s3, max(s4) as s4 from (
(select id as id, type as type, s1, s2, s3, s4 From t1 )
union
(select id as id, type as type, s1, s2, s3, s4 From t2 )
) a group id,type

我自己测试的语句如下：

CREATE TABLE test1(id INT,tname VARCHAR(20),num INT);
CREATE TABLE test2(id INT,tname VARCHAR(20),num INT);
INSERT INTO test1(id,tname,num) VALUES(1,'王',2),(2,'张',6);
INSERT INTO test1(id,tname,num) VALUES(3,'王',3),(4,'张',9);

SELECT id ,tname,COUNT(*) FROM
(
SELECT id,tname,num FROM test1
UNION
SELECT id,tname,num FROM test2
) a GROUP BY id,tname;

希望能对你有帮助