避免重复的行

问题描述

这是我的查询

this is my Query

;with mycte as
(
    select (select sum(b.ES_COST) 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 )
        as Total,b.ES_ID,b.ES_TITLE,b.ES_BOOK_TYPE,b.ES_BOOK_REMARKS,
        b.ES_DEPT_ID,b.ES_CLASS_DETAIL_ID,b.ES_ENTERED_BY,
        b.ES_SPECIALIZATION,b.ES_SUBJECT as SUBJECT,
        b.ES_ENTRY_DATE,b.ES_YEAR,d.ES_DEPT_NAME,
        b.ES_EDITION,b.ES_AUTHOR,b.ES_PUBLISHER,
        b.ES_COST,b.ES_VOLUME,b.ES_PAGES,
        b.ES_SOURCE,b.ES_BILL_NO,b.ES_BILL_DATE ,
        b.ES_BAR_CODE,b.ES_BRANCH,b.ES_CALL_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 )
    SELECT   Total,ES_TITLE, (select COUNT(b.ES_ID)
    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
    and b.ES_AUTHOR=c.ES_AUTHOR and b.ES_TITLE=c.ES_TITLE
    and b.ES_EDITION=c.ES_EDITION)
    as NumberOfbooks,ES_BOOK_TYPE,
    ES_BOOK_REMARKS,ES_DEPT_ID,
    ES_CLASS_DETAIL_ID,
    ES_ENTERED_BY,
    ES_SPECIALIZATION,SUBJECT ,
    ES_ENTRY_DATE,ES_YEAR,
    ES_DEPT_NAME,ES_EDITION,
    ES_AUTHOR,ES_PUBLISHER,
    ES_COST,ES_VOLUME,
    ES_PAGES,ES_SOURCE,
    ES_BILL_NO,ES_BILL_DATE ,
    ES_BAR_CODE,ES_BRANCH,ES_CALL_NO
FROM   mycte c
order by ES_TITLE,ES_AUTHOR,ES_EDITION

它显示为

it displays like

ES_BAR_CODE|ES_TITLE |ES_AUTHOR|NumberOfbooks|ES_COST|ES_PAGES|ES_PUBLISHER|Total
  4708     |ATextBook|MAHAJAN  |     3       | 96    | 156    | abc        | 312
  4709     |ATextBook|MAHAJAN  |     3       | 96    | 156    | abc        | 312
  4710     |ATextBook|MAHAJAN  |     3       | 96    | 156    | abc        | 312
  5670     |something|najuna   |     2       | 95    | 124    |DelhiDhan   | 190
  5689     |something|najuna   |     2       | 95    | 124    |DelhiDhan   | 190

但是我希望显示为

but i want to display like

ES_BAR_CODE|ES_TITLE|ES_AUTHOR|NumberOfbooks|ES_COST|ES_PAGES|ES_PUBLISHER|Total
4708,.,4710|ATextBook|MAHAJAN  |     3       | 96    | 156    | abc        | 312
5670,5689  |something|najuna   |     2       | 95    | 124    |DelhiDhan   | 190

推荐答案

这篇关于避免重复的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！