--明白点了,这样呢? Create View Test As Select 用户ID,sum(收益金额) as 收益金额 From ( Select 用户ID,收益 From 收益A Union All Select 用户ID,收益 From 收益B ) A Group By 用户ID Order by 收益金额 DESC
--測試 create table A (用戶 varchar(20),收益 int) insert into A select 'aa',5 union select 'bb',6 union select 'aa',7create table B (用戶 varchar(20),收益 int) insert into B select 'aa',8 union select 'bb',8 union select 'aa',4--求合 select 用戶,sum(收益) as 收益 from (select 用戶,收益 from A union select 用戶,收益 from B) T group by 用戶 order by 收益-- drop table A drop table B--The result: 用戶 收益 -------------------- ----------- bb 14 aa 24(2 row(s) affected)
錯了,修改下: 加個all --求合 select 用戶,sum(收益) as 收益 from (select 用戶,收益 from A union all select 用戶,收益 from B) T group by 用戶 order by 收益
1.Create View Test As Select 用户ID,sum(收益金额) as 收益金额,MIN(B.性别) as 性别,MIN(B.姓名) as 姓名 From ( Select 用户ID,收益 From 收益A Union All Select 用户ID,收益 From 收益B ) A Right Join 用户资料表 B On A.用户ID=B.用户ID Group By 用户ID Order by 收益金额 DESC 2.用VIEW比在前台程序端使用SQL效率要好一些!
1.如果每個用戶的性別什麼的(你要查找)屬性是唯一的,並且在A,B表中都存在,可以直接放 select後面,group by 後面. 如果在A,B表中不是都存在的,那最好用join了.2.用view可能效率高些,但是費資源.
UNION
SELECT * FROM B
ORDER BY (Afield1+Afield2+...+AfieldN)设 AfieldX为收益求合字段.
不知楼主是否这个意思
Create View Test
As
Select 用户ID,sum(收益金额) as 收益金额
From
( Select 用户ID,收益
From 收益A
Union All
Select 用户ID,收益
From 收益B
) A
Group By 用户ID
Order by 收益金额 DESC
create table A (用戶 varchar(20),收益 int)
insert into A
select 'aa',5
union
select 'bb',6
union
select 'aa',7create table B (用戶 varchar(20),收益 int)
insert into B
select 'aa',8
union
select 'bb',8
union
select 'aa',4--求合
select 用戶,sum(收益) as 收益
from (select 用戶,收益 from A union select 用戶,收益 from B) T
group by 用戶
order by 收益--
drop table A
drop table B--The result:
用戶 收益
-------------------- -----------
bb 14
aa 24(2 row(s) affected)
--求合
select 用戶,sum(收益) as 收益
from (select 用戶,收益 from A union all select 用戶,收益 from B) T
group by 用戶
order by 收益
小弟对SQL不熟,只是做东东要用到,时间又紧就来不及学了如果还要把用户名,性别什么的一起select出来,是把它们都加到select中,然后再加到Group by里边么?还有一个问题就是,在程序执行这样的SQL,和在库里建一个VIEW,效率上有多大差别啊?
As
Select 用户ID,sum(收益金额) as 收益金额,MIN(B.性别) as 性别,MIN(B.姓名) as 姓名
From
( Select 用户ID,收益
From 收益A
Union All
Select 用户ID,收益
From 收益B
) A Right Join 用户资料表 B
On A.用户ID=B.用户ID
Group By 用户ID
Order by 收益金额 DESC
2.用VIEW比在前台程序端使用SQL效率要好一些!
select後面,group by 後面.
如果在A,B表中不是都存在的,那最好用join了.2.用view可能效率高些,但是費資源.