27,579
社区成员
发帖
与我相关
我的任务
分享
--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([id] [int],[wzid] [int],[wzbt] [nvarchar](10),[fs] [int],[lx] [nvarchar](10))
INSERT INTO [tb]
SELECT '5','10','花飘万家雪','80','t' UNION ALL
SELECT '6','11','大规模','50','t' UNION ALL
SELECT '7','11','大规模','30','j' UNION ALL
SELECT '8','11','大规模','20',NULL UNION ALL
SELECT '10','11','大规模','30','t'
--SELECT * FROM [tb]
-->SQL查询如下:
;WITH t AS
(
SELECT *,sumfs=AVG(fs)OVER(PARTITION BY wzid,lx),
flag=ROW_NUMBER()OVER(PARTITION BY wzid,lx ORDER BY id)
FROM tb
)
SELECT [wzid], [wzbt],SUM(sumfs) fs
FROM t
WHERE flag=1
GROUP BY [wzid], [wzbt]
ORDER BY 1
/*
wzid wzbt fs
----------- ---------- -----------
10 花飘万家雪 80
11 大规模 90
(2 行受影响)
*/
create table tests
(
WZID int,
WZBT varchar(100),
RYDM int,
RYNC varchar(10),
yhqx varchar(10)
)
insert tests select
10, '花飘万家雪', 40,'0', 't' union all select
11, '大规模', 50 ,'ccc' ,'t' union all select
11, '大规模', 30 ,'aaa' ,'j' union all select
11, '大规模', 20 ,'bbb' ,null union all select
11 ,'大规模', 30 ,'ddd' ,'t'
go
select WZID,WZBT,FS=SUM(FS)
FROM (
select *
from (
select WZID,WZBT,avg(RYDM) as fs
from tests
where yhqx='t'
group by WZID,WZBT) k
union all
select *
from (
select WZID,WZBT,sum(RYDM) as fs
from tests
where yhqx<>'t' OR yhqx IS NULL
group by WZID,WZBT) k) K
GROUP BY WZID,WZBT
ORDER BY WZID
/*
WZID WZBT FS
----------- ---------------------------------------------------------------------------------------------------- -----------
10 花飘万家雪 40
11 大规模 90
(2 行受影响)
*/