求一SQL语句

[wjh_36]

表如下：

ID WZID WZBT RYDM RYNC YHQX
5 10 花飘万家雪 40 0 t
6 11 大规模 50 ly037003 ccc t
7 11 大规模 30 ly037001 aaa j
8 11 大规模 20 ly037002 bbb
9 11 大规模 30 ly037004 ddd t


想显示的结果为
wzid wzbt fs
10 花飘万家雪 90
11 大规模 40


急，在线等

[dawugui]

是按照WZID , WZBT分组求合计吗?

create table tb(ID int,WZID int,WZBT varchar(20),RYDM int)
insert into tb values(5 ,10 ,'花飘万家雪', 40)
insert into tb values(6 ,11 ,'大规模' ,50)
insert into tb values(7 ,11 ,'大规模' ,30)
insert into tb values(8 ,11 ,'大规模' ,20)
insert into tb values(9 ,11 ,'大规模' ,30)
go

select WZID , WZBT , fs = sum(RYDM) from tb group by WZID ,  WZBT order by WZID ,  WZBT

/*
WZID        WZBT                 fs          
----------- -------------------- ----------- 
10          花飘万家雪                40
11          大规模                  130

（所影响的行数为 2 行）
*/

drop table tb

[wjh_36]

是按照WZID , WZBT分组求合计,但后面还有个YHQX分（j,t,和空）
如果同个WZID,WZBT有的YHQX有2个T，1个J和一个空，那么要YHQX是两个T的相加除以2加上其他的

[wjh_36]

上面的表错了，我在发下表结构
ID WZID WZBT RYDM RYNC YHQX
5 10 花飘万家雪 40 0 t
6 11 大规模 50 ccc t
7 11 大规模 30 aaa j
8 11 大规模 20 bbb
9 11 大规模 30 ddd t

[wjh_36]

显示的结果为
wzid wzbt fs
10 花飘万家雪 40
11 大规模 90

大规模为90是两个T的相加除以2加上YHQX为J和空的
（50+30）/2+30+20=90

[feixianxxx]

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 行受影响)

*/

[bean_sql]

create table tb(ID int,WZID int,WZBT varchar(20),RYDM int)
insert into tb values(5 ,10 ,'花飘万家雪', 40)
insert into tb values(6 ,11 ,'大规模' ,50)
insert into tb values(7 ,11 ,'大规模' ,30)
insert into tb values(8 ,11 ,'大规模' ,20)
insert into tb values(9 ,11 ,'大规模' ,30)
go

declare @tb table(WZID int,WZBT varchar(20),RYDM int)
declare @num1 int,@num2 int,@num3 int ,@num4 int ,@value int
select @num1= rydm from tb where id=6
select @num2= rydm from tb where id=7
select @num3= rydm from tb where id=8
select @num4= rydm from tb where id=9
set @value=(@num1+@num2)/2+@num3+@num4
insert into @tb select top 2 wzid,wzbt,rydm from tb
update @tb set RYDM=@value where wzid=11
select * from @tb

[xiaoliaoyun]

CREATE TABLE tb
    (
      ID INT ,
      WZID INT ,
      WZBT VARCHAR(20) ,
      RYDM INT ,
      RYNC VARCHAR(10) ,
      YHQX VARCHAR(10)
    )
INSERT  INTO tb
VALUES  ( 5, 10, '花飘万家雪', 40, '0', 't' )
INSERT  INTO tb
VALUES  ( 6, 11, '大规模', 50, 'ccc', 't' )
INSERT  INTO tb
VALUES  ( 7, 11, '大规模', 30, 'aaa', 'j' )
INSERT  INTO tb
VALUES  ( 8, 11, '大规模', 20, 'bbb', NULL )
INSERT  INTO tb
VALUES  ( 9, 11, '大规模', 30, 'ddd', 't' )
GO


SELECT WZID,
    WZBT
    ,fs = AVG(CASE WHEN YHQX = 't' THEN RYDM END)
        + ISNULL(SUM(CASE WHEN YHQX = 'j' OR YHQX IS NULL THEN RYDM END),0)
FROM tb
GROUP BY WZID,WZBT
ORDER BY WZID

[wjh_36]

xiaoliaoyun

(流浪的云)
SELECT WZID,
WZBT
,fs = AVG(CASE WHEN YHQX = 't' THEN RYDM END)
+ AVG(CASE WHEN YHQX = 'j' THEN RYDM END)
+AVG(CASE WHEN YHQX = '' THEN RYDM END)
FROM tb
GROUP BY WZID,WZBT
ORDER BY WZID
好像应该这样，但有的显示不出分

[htl258_Tony]

--> 生成测试数据表: [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 行受影响)
*/

[wjh_36]

SELECT WZID,
WZBT
,fs = ISNULL(AVG(CASE WHEN YHQX = 't' THEN RYDM END),0)
+ ISNULL(AVG(CASE WHEN YHQX = 'j' THEN RYDM END),0)
+ISNULL(AVG(CASE WHEN YHQX = '' THEN RYDM END),0)
FROM tb
GROUP BY WZID,WZBT
ORDER BY WZID
是不是应该这样