避免重复的行 [英] avoid the duplicate rows

问题描述

这是我的查询

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

推荐答案

在您改进问题之前，您可以尝试的最好的事情是使用层次结构并检查最适合您的条件然后尝试在同一个选择查询中更新结果。

Until you improve your question the best thing you can try is use hierarchy and check the condition which is best for you and then try to update the result in the same select query.

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