例如有表table1如下:
table1:
User1,User2 value1 value2
A B 2 30
A C 3 20
B B 2 10需求:创建一个新的视图如下:
user1 value如何用SQL语句计算A的value=(2×30+3×20)/(2+3)
B的value=2*10/2
A 2×30+3×20)/(2+3)
B 2*10/2
table1:
User1,User2 value1 value2
A B 2 30
A C 3 20
B B 2 10需求:创建一个新的视图如下:
user1 value如何用SQL语句计算A的value=(2×30+3×20)/(2+3)
B的value=2*10/2
A 2×30+3×20)/(2+3)
B 2*10/2
as
select
user1,
sum(value1*value2)/cas when sum(value1)=0 then 1 else sum(value1) end
from
table1
group by
user1go
select * from View_tb1
from table
group by user1
---测试数据---
if object_id('[table1]') is not null drop table [table1]
go
create table [table1]([User1] varchar(1),[User2] varchar(1),[value1] int,[value2] int)
insert [table1]
select 'A','B',2,30 union all
select 'A','C',3,20 union all
select 'B','B',2,10
---查询---
create view View_tb1
as
select
user1,
value=sum(value1*value2)/case when sum(value1)=0 then 1 else sum(value1) end
from
table1
group by
user1go
select * from View_tb1drop table table1
drop view View_tb1
---结果---
user1 value
----- -----------
A 24
B 10(所影响的行数为 2 行)
CREATE VIEW VIEWTA1
AS
SELECT USER1,SUM([VALUE]AS [VALUE] FROM
(SELECT USER1,VALUE1*VALUE2/SUM(VALUE1) AS[VALUE] FROM TABLE1 GROUP BY USER1)AS T
GROUP BY USER1
group by user1
insert @T1
SELECT 'A','B',2,30 UNION ALL
SELECT 'A','C',3,20 UNION ALL
SELECT 'B','B',2,10select user1,[value]=convert(float(2),round(sum(value1*value2)*1.0/sum(value1),2))
from @T1
group by user1user1 value
----- -------------
A 24
B 10
CREATE TABLE TABLE1( User1 VARCHAR(1),value1 INT, value2 INT)
INSERT TABLE1
SELECT 'A' , 2 , 30 UNION ALL
SELECT 'A' , 3 , 20 UNION ALL
SELECT 'B' , 2 , 10 SELECT USER1,SUM([VALUE])/SUM(VALUE1)AS [VALUE] FROM
(SELECT USER1,VALUE1,VALUE1*VALUE2 AS [VALUE] FROM TABLE1 )AS T
GROUP BY USER1
USER1 VALUE
----- -----------
A 24
B 10(所影响的行数为 2 行)