问题描述
这是我的查询
this is my query
select distinct tb.ES_TITLE,tb.ES_AUTHOR,tb.ES_SUBJECT,tb.ES_EDITION
,sum(tb.ES_TOTAL_ITEMS)as ES_TOTAL_ITEMS,sum(tb.ES_AVAILABLE)as ES_AVAILABLE
,sum(tb.ES_OUT) as ES_OUT,tb.ES_MISSING,tb.ES_ACCESS_NO
from (select ES_TITLE,ES_AUTHOR,ES_SUBJECT,ES_EDITION,COUNT(*) as ES_TOTAL_ITEMS,
isnull(case when ES_STATUS='Active' then count(*) end,0) as ES_AVAILABLE,
isnull(case when ES_STATUS='Issued' then count(*) end,0) as ES_OUT,'0' as ES_MISSING,
'' as ES_ACCESS_NO from es_library_books b
inner join ES_DEPARTMENTS d on d.ES_ID=b.ES_DEPT_ID
left join ES_BRANCHES on ES_BRANCHES.Es_Id=b.ES_BRANCH
where b.ES_BRANCH=1 and ES_BRANCHES.ES_INSTITUTE_ID= 1
group by ES_TITLE,ES_AUTHOR,ES_SUBJECT,ES_EDITION,ES_STATUS
having b.ES_AUTHOR like '%Gupta J.B%')as tb
group by tb.ES_TITLE,tb.ES_AUTHOR,tb.ES_SUBJECT,tb.ES_EDITION
,tb.ES_TOTAL_ITEMS,tb.ES_AVAILABLE
,tb.ES_OUT,tb.ES_MISSING,tb.ES_ACCESS_NO
这是我的结果
title |作者|版|总计|可用| out
----------------------------------------- ------------------------------------
电力课程| Gupta J.B | 14 | 1 | 0 | 1
电力课程| Gupta J.B | 14 | 9 | 9 | 0
电气技术| Gupta J.B | 2 | 5 | 5 | 0
能量转换| Gupta J.B | | 9 | 9 | 0
理论&表现| Gupta J.B | 14 | 4 | 0 | 4
理论&表现| Gupta J.B | 14 | 6 | 6 | 0
但我想要这个结果
title |作者|编辑|总计|可用| out
----------------------------------------- ------------------------------------
电力课程| Gupta J.B | 14 | 10 | 9 | 1
电气技术| Gupta J.B | 2 | 5 | 5 | 0
能量转换Gupta J.B | | 9 | 9 | 0
理论&表演Gupta J.B | 14 | 10 | 6 | 4
请帮助我..
this is my result
title | author | edition | total | available | out
-----------------------------------------------------------------------------
A Course In Electrical Power |Gupta J.B | 14 | 1 | 0 | 1
A Course In Electrical Power |Gupta J.B | 14 | 9 | 9 | 0
Electrical Technology | Gupta J.B | 2 | 5 | 5 | 0
Energy Conversion | Gupta J.B | | 9 | 9 | 0
Theory & Performance | Gupta J.B | 14 | 4 | 0 | 4
Theory & Performance | Gupta J.B | 14 | 6 | 6 | 0
but i want like this result
title | author | edition| total | available | out
-----------------------------------------------------------------------------
A Course In Electrical Power|Gupta J.B | 14| 10 | 9 | 1
Electrical Technology | Gupta J.B | 2| 5 | 5 | 0
Energy Conversion Gupta J.B || 9 | 9 | 0
Theory & Performance Gupta J.B | 14| 10 | 6 | 4
pls help me..
推荐答案
select tb.ES_TITLE,tb.ES_AUTHOR,tb.ES_EDITION
,sum(tb.ES_TOTAL_ITEMS)as ES_TOTAL_ITEMS,sum(tb.ES_AVAILABLE)as ES_AVAILABLE
,sum(tb.ES_OUT) as ES_OUT
from (
select ES_TITLE,ES_AUTHOR,ES_SUBJECT,ES_EDITION,COUNT(*) as ES_TOTAL_ITEMS,
isnull(case when ES_STATUS='Active' then count(*) end,0) as ES_AVAILABLE,
isnull(case when ES_STATUS='Issued' then count(*) end,0) as ES_OUT,'0' as ES_MISSING,
'' as ES_ACCESS_NO from es_library_books b
inner join ES_DEPARTMENTS d on d.ES_ID=b.ES_DEPT_ID
left join ES_BRANCHES on ES_BRANCHES.Es_Id=b.ES_BRANCH
where b.ES_BRANCH=1 and ES_BRANCHES.ES_INSTITUTE_ID= 1
group by ES_TITLE,ES_AUTHOR,ES_SUBJECT,ES_EDITION,ES_STATUS
having b.ES_AUTHOR like '%Gupta J.B%'
)as tb
group by tb.ES_TITLE,tb.ES_AUTHOR,tb.ES_EDITION
我刚从最终选择列表中删除了不必要的字段。
希望，它有帮助：）
I have just removed the unnecessary fields from the final select list.
Hope, it helps :)
这篇关于总项目，可用项目，来自sql中重复记录的项目的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！