求教一个sql查询语句的写法

[c65dc65d]

如图所示，每台手术可能由2个或1个医生完成，2个医生的时候，每个医生工作量算作0.5，一个医生就算作1，请教工作量统计的sql语句如何写，谢谢。

[Tiger_Zhao]

我也来一个，借用7楼的 test 表。
思路：将 'a',NULL 或 NULL,'a' 都看成 'a','a'，各记 0.5 分。

SELECT P, SUM(Q) Q
  FROM (
        SELECT ISNULL(A,B) P, 0.5 Q FROM test
        UNION ALL
        SELECT ISNULL(B,A) P, 0.5 Q FROM test
       ) T
 GROUP BY P

P     Q                                        
----- ---------------------------------------- 
a                                          2.5 
b                                          1.5 
c                                          1.0 
d                                          1.5 
e                                          2.0 
f                                          1.5 

[昵称90天可改一次]

create table test (A varchar(5),B varchar(5),C varchar(50))
insert test 
select 'a','c','******' union all
select 'b','c','******' union all
select 'a','e','******' union all
select 'd','a','******' union all
select 'a','f','******' union all
select 'b',NULL,'******' union all
select 'e',NULL,'******' union all
select 'a','e','******' union all 
select 'f',NULL,'******' union all
select NULL,'d','******' 
with t AS(
	select a.A from (
			select A from test where A IS NOT NULL
			union all
			select B from test where B IS NOT NULL
			) a
			group by A
)
select A,SUM(value)
from (
	select t.A,value=case when tt.A IS not null then 0.5 else 1 end
	from t 
		left join test tt
	 on t.A=tt.A
	union all 
	select t.A,case when tt.B IS not null then 0.5 else 1 end
	from t 
		left join test tt
	 on t.A=tt.B
 ) b
 group by A
 
 /*
a	2.5
b	2.0
c	2.0
d	1.0
e	1.5
f	1.0
 */

[c65dc65d]

各位老大的答案另小弟茅塞顿开，非常感谢！

[c65dc65d]

但小弟分数有限，只能给先回答的老大了，见谅啊各位。

[昵称90天可改一次]

上面错了 ·· 用这个

create table test (A varchar(5),B varchar(5),C varchar(50))
insert test 
select 'a','c','******' union all
select 'b','c','******' union all
select 'a','e','******' union all
select 'd','a','******' union all
select 'a','f','******' union all
select 'b',NULL,'******' union all
select 'e',NULL,'******' union all
select 'a','e','******' union all 
select 'f',NULL,'******' union all
select NULL,'d','******' 
with t AS(
	select a.A from (
			select A from test where A IS NOT NULL
			union all
			select B from test where B IS NOT NULL
			) a
			group by A
)

select A,SUM(value)
from (
	select t.A,value=case when tt.B IS not null then 0.5 else 1 end
	from t 
		inner join test tt
	 on t.A=tt.A
	union all 
	select t.A,case when tt.A IS not null then 0.5 else 1 end
	from t 
		inner join test tt
	 on t.A=tt.B
 ) b
 group by A
 
 /*
a	2.5
b	1.5
c	1.0
d	1.5
e	2.0
f	1.5
 */

[Mr_Nice]

CREATE TABLE TD (doc1 VARCHAR(1),doc2 VARCHAR(1),手术名称 VARCHAR(10))
GO

INSERT INTO TD(doc1,doc2,手术名称)
SELECT 
'a','c','123'
UNION ALL
SELECT 
'b','c','234'
UNION ALL
SELECT 
'b','','456'


SELECT * FROM TD




SELECT  doc ,
        SUM(num) as num
FROM    ( SELECT    doc1 AS doc ,
                    手术名称 ,
                    CASE WHEN EXISTS ( SELECT   1
                                       FROM     TD D
                                       WHERE    D.手术名称 = TD.手术名称
                                                AND D.doc2 <> TD.doc1
                                                AND D.doc2 = '' ) THEN 1
                         ELSE 0.5
                    END AS num
          FROM      TD
          UNION ALL
          SELECT    doc2 AS doc ,
                    手术名称 ,
                    CASE WHEN EXISTS ( SELECT   1
                                       FROM     TD D
                                       WHERE    D.手术名称 = TD.手术名称
                                                AND D.doc1 <> TD.doc2
                                                AND D.doc1 = '' ) THEN 1
                         ELSE 0.5
                    END AS num
          FROM      TD
        ) T
WHERE   T.doc <> ''
GROUP BY doc 



/*
doc	num
a	0.5
b	1.5
c	1.0*/

[sh65489]

select doc,sum(fcount) from (select doc1 doc,sum(case when isnull(doc2,'')='' then 1 else 0.5 end) fcount from tab group by doc1 union all select doc2 doc,sum(case when isnull(doc1,'')='' then 1 else 0.5 end) fcount from tab group by doc2 ) group by doc

[shinger126]

create table test (A varchar(5),B varchar(5),C varchar(50)) insert test select 'a','c','******' union all select 'b','c','******' union all select 'a','e','******' union all select 'd','a','******' union all select 'a','f','******' union all select 'b',NULL,'******' union all select 'e',NULL,'******' union all select 'a','e','******' union all select 'f',NULL,'******' union all select NULL,'d','******' select A,SUM(counts) counts from ( select a,case when ISNULL(b,'')='' then 1 else 0.5 end counts from test where ISNULL(a,'')<>'' union all select B,case when ISNULL(a,'')='' then 1 else 0.5 end counts from test where ISNULL(b,'')<>'' ) t group by a

[道玄希言]

CREATE TABLE T3 (doc1 NVARCHAR(10),doc2 NVARCHAR(10),手术名称 NVARCHAR(20))
GO
 
INSERT INTO T3(doc1,doc2,手术名称)
SELECT 
'a','c','123'
UNION ALL
SELECT 
'b','c','234'
UNION ALL
SELECT 
'b','','456'
UNION ALL
SELECT 
'','d','4516'

select doc, convert(decimal(8,2), count(doc))/2 as dwork from 
(
select (case doc1 when null then doc2 when ('') then doc2 else doc1 end) as doc from T3 AS A
union all
select (case doc2 when null then doc1 when ('') then doc1 else doc2 end) as doc from T3 AS B
) C
group by C.doc

a 0.50 b 1.50 c 1.00 d 1.00

[霜寒月冷]

思路很多，自己写了个，在看看其他的写法，呵呵。

drop table test
create table test (A varchar(5),B varchar(5),C varchar(50))
insert test 
select 'a','c','******' union all
select 'b','c','******' union all
select 'a','e','******' union all
select 'd','a','******' union all
select 'a','f','******' union all
select 'b',NULL,'******' union all
select 'e',NULL,'******' union all
select 'a','e','******' union all 
select 'f',NULL,'******' union all
select NULL,'d','******' 
go
;
with cte as
(
select * ,case when (A is not null  and B is null) or

( B is not null  and A is null) then 1 else 0.5 end as 分数

from test

)
select A,sum(分数) from
(
select A, sum( case  when A is not null then 分数 else 0 end) 分数    from cte where A is not null group by A 
union all 
select B, sum( case  when B is not null then 分数 else 0 end) 分数    from cte where B is not null group by B 
)t  group by A

--A	(无列名)
--a	2.5
--b	1.5
--c	1.0
--d	1.5
--e	2.0
--f	1.5