查询时怎么实现分条件支查询

[jishengzu]

有表ServiceBill
id sfid zgid zlid quantity
----------------------------------------------------
1 1 0 3 1
2 0 2 3 2
3 0 0 3 1
4 1 0 0 3
5 0 2 0 1


和员工表Employee
id type name
--------------------------------
1 1 师傅
2 2 中工
3 3 助理

查询结果是
id type name quantity
---------------------------------------
1 1 师傅 4
2 2 中工 3
3 3 助理 4

我自己写的语句是 结果不行错误。

select *,(select count(quantity) from ServiceBill where 1=1 and 
(case when emp.type=1 then sfid=emp.id when emp.type=2 then zgid=emp.id 
when emp.type=3 then zlid=emp.id end)) from Employee emp

[jishengzu]

哎发上来后布局全变了

[jishengzu]

有表ServiceBill
id sfid zgid zlid quantity
----------------------------------------------------
1 1 0 3 1
2 0 2 3 2
3 0 0 3 1
4 1 0 0 3
5 0 2 0 1


和员工表Employee
id type name
--------------------------------
1 1 师傅
2 2 中工
3 3 助理

查询结果是
id type name quantity
---------------------------------------
1 1 师傅 4
2 2 中工 3
3 3 助理 4

[永生天地]

select *,(select sum(quantity) from ServiceBill where 1=1 and emp.id=
(case when emp.type=1 then sfid when emp.type=2 then zgid
when emp.type=3 then zlid end)) from Employee emp

id type name
----------- ----------- ---------- -----------
1 1 师傅 4
2 2 中工 3
3 3 助理 4

（所影响的行数为 3 行）

[dawugui]

create table ServiceBill(id int,sfid int,zgid int,zlid int,quantity int)
insert into ServiceBill values(1 ,1 ,0 ,3 ,1)
insert into ServiceBill values(2 ,0 ,2 ,3 ,2)
insert into ServiceBill values(3 ,0 ,0 ,3 ,1)
insert into ServiceBill values(4 ,1 ,0 ,0 ,3)
insert into ServiceBill values(5 ,0 ,2 ,0 ,1)
create table Employee(id int,type int,name varchar(10))
insert into Employee values(1 ,1 ,'师傅')
insert into Employee values(2 ,2 ,'中工')
insert into Employee values(3 ,3 ,'助理')
go

select m.* , quantity = 
       isnull((select sum(quantity) from ServiceBill where sfid = m.type),0) + 
       isnull((select sum(quantity) from ServiceBill where zgid = m.type),0) + 
       isnull((select sum(quantity) from ServiceBill where zlid = m.type),0) 

from Employee m

drop table ServiceBill,Employee

/*
id          type        name       quantity    
----------- ----------- ---------- ----------- 
1           1           师傅         4
2           2           中工         3
3           3           助理         4

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

[jishengzu]

有表ServiceBill
id sfid zgid zlid quantity
----------------------------------------------------
1---1---0-----3----1
2-- 0---2----3-----2
3---0---0----3-----1
4---1---0-----0----3
5---0---2-----0----1


和员工表Employee
id type name
--------------------------------
1- 1--- 师傅
2 -2 ---中工
3 -3--- 助理

查询结果是
id type name quantity
---------------------------------------
1 --1 --师傅 ---4
2-- 2 --中工 ---3
3 --3 --助理 ---4


没答案就散分了，进来接分吧

[永生天地]

select *,quantity=(select sum(quantity) from ServiceBill where 1=1 and emp.id=
(case when emp.type=1 then sfid when emp.type=2 then zgid
when emp.type=3 then zlid end)) from Employee emp

/*
id          type        name       quantity    
----------- ----------- ---------- ----------- 
1           1           师傅         4
2           2           中工         3
3           3           助理         4

（所影响的行数为 3 行）

*/

[you_tube]

ServiceBill跟Employee有关联么？

[jishengzu]

谢谢啦，我还以为无解。