Sql Query中显示双重名称的问题 [英] problem with double name display in Sql Query

问题描述

先生我的代码是

sir my code is

select lv.*,d.ES_DEPT_NAME from ES_LIBRARY_VISITORS lv
left join ES_DEPARTMENTS d on d.ES_ID=lv.ES_DEPT_ID
where ES_BRANCH_ID=1 and CONVERT(VARCHAR(10), lv.ES_DATE, 111)
between '2013/10/18' and '2013/10/18' and lv.ES_USER_TYPE='Student' and lv.ES_DEPT_ID='3'
order by case lv.ES_USER_TYPE when 'Student' then 1 when 'Employee' then 2 else 3 end,
lv.ES_SYSTEM_NO

IT DISPLAY LIK

IT DISPLAY LIke

S.No    Date    User Name   User Type   Department  Id.No   Sys.No  In Time 

1 18/10/2013 MOTURISRI HANUMA SURYA KUM学生EEE（BE）2010-281 10:43 AM

2 18/10/2013 KURUHURUVENKATA SATYA SAI学生EEE（BE）2010-276 10:57 AM

3 18/10/2013 DATTISAI KUMAR学生EEE（BE）2011-282 10:58 AM

4 18/10/2013 DANNINASURYANARAYANA学生EEE（BE）2010- 263 11:00 AM

5 18/10/2013 PATNAIK PATNAIK SUMEGHA学生EEE（BE）2011-264 11:05 AM

6 18/10/2013 PATNAIK PATNAIK SUMEGHA学生EEE（BE）2011-264 11:05 AM

7 18/10/2013 PATNAIK PATNAIK SUMEGHA学生EEE（BE）2011-264 11:05 AM

8 18/10/2013 PATNAIK PATNAIK SUMEGHA学生EEE（BE）2011-264 11:06 AM

9 18/10/2013 RojaM学生EEE（BE）2011-303 11:06 AM

10 18/10/2013 GowriG学生EEE（BE）2011-269 11:07 AM

11 18/10/2013 GowriG学生EEE（BE）2011- 269 11:07 AM

12 18/10/2013 KUMARISANTHOSH KUMAR SIVA学生EEE（BE）2011-255 11:15 AM

13 18/10/2013 CHANDARAMT PARAMESWARA RAO CH学生EEE（BE）2010-258 1:44 PM

14 18/10/2013 CHANDARAMT PARAMESWARA RAO CH学生EEE（BE）2010-258 1:44 PM

15 18/10/2013 VILLURI HUNVASH KUMAR学生EEE（BE）2012-218 1:45 PM

16 18/10/2013 VILLURI HUNVASH KUMAR学生EEE（BE）2012-218 1：下午45点







但我不想在同一天重复学生姓名

1 18/10/2013 MOTURISRI HANUMA SURYA KUM Student EEE(BE) 2010-281 10:43 AM
2 18/10/2013 KURUHURUVENKATA SATYA SAI Student EEE(BE) 2010-276 10:57 AM
3 18/10/2013 DATTISAI KUMAR Student EEE(BE) 2011-282 10:58 AM
4 18/10/2013 DANNINASURYANARAYANA Student EEE(BE) 2010-263 11:00 AM
5 18/10/2013 PATNAIK PATNAIK SUMEGHA Student EEE(BE) 2011-264 11:05 AM
6 18/10/2013 PATNAIK PATNAIK SUMEGHA Student EEE(BE) 2011-264 11:05 AM
7 18/10/2013 PATNAIK PATNAIK SUMEGHA Student EEE(BE) 2011-264 11:05 AM
8 18/10/2013 PATNAIK PATNAIK SUMEGHA Student EEE(BE) 2011-264 11:06 AM
9 18/10/2013 RojaM Student EEE(BE)2011-303 11:06 AM
10 18/10/2013 GowriG Student EEE(BE) 2011-269 11:07 AM
11 18/10/2013 GowriG Student EEE(BE) 2011-269 11:07 AM
12 18/10/2013 KUMARISANTHOSH KUMAR SIVA Student EEE(BE) 2011-255 11:15 AM
13 18/10/2013 CHANDARAMT PARAMESWARA RAO CH Student EEE(BE) 2010-258 1:44 PM
14 18/10/2013 CHANDARAMT PARAMESWARA RAO CH Student EEE(BE) 2010-258 1:44 PM
15 18/10/2013 VILLURI HUNVASH KUMAR Student EEE(BE) 2012-218 1:45 PM
16 18/10/2013 VILLURI HUNVASH KUMAR Student EEE(BE) 2012-218 1:45 PM



but i didn't want repeat the student name in the same date

推荐答案

按照您希望常量的值对结果进行分组，并使用MAX作为[In Time]。



例如更改您的查询如下

Group your results by the values you expect to be "constant" and use MAX for the [In Time].

E.g. Change your query as follows

select lv.ES_DATE,lv.es_user_name,lv.ES_USER_TYPE,d.ES_DEPT_NAME,lv.ES_SYSTEM_NO,MAX(lv.ES_IN_TIME)
from ES_LIBRARY_VISITORS lv
 left join ES_DEPARTMENTS d on d.ES_ID=lv.ES_DEPT_ID
 where ES_BRANCH_ID=1 and CONVERT(VARCHAR(10), lv.ES_DATE, 111)
 between '2013/10/18' and '2013/10/18' and lv.ES_USER_TYPE='Student' and lv.ES_DEPT_ID='3'
GROUP BY lv.ES_DATE,lv.es_user_name,lv.ES_USER_TYPE,d.ES_DEPT_NAME,lv.ES_SYSTEM_NO
order by case lv.ES_USER_TYPE when 'Student' then 1 when 'Employee' then 2 else 3 end,
 lv.ES_SYSTEM_NO

注意来自@Peter Leow的评论 - 如果您有像这样的重复数据，那么您的数据和/或数据库设计显然有问题评论在发现OP的响应后删除。

如果您想要注意访问过该库的唯一客户，您可能需要更改 MAX（lv.ES_IN_TIME）到

Note the comment from @Peter Leow - there is clearly something wrong with your data and/or database design if you have duplicated data like thisComment removed after spotting the OP's response.
As you want to note the unique customers that have visited the library you might want to change MAX(lv.ES_IN_TIME) to

COUNT(lv.ES_IN_TIME)

所以你知道他们访问了多少次

so you know how many times they visited

这篇关于Sql Query中显示双重名称的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！