sql去除重复记录

select
* from
(select * from tmp group by userName having count(userName)= 1
union select * from tmp where id in ( select id from tmp group by userName having count(userName)> 1)
) aaa order by id

评论

6 楼 zero1111 2015-04-28  

  commons-collections-3.1.jar
      commons-dbcp-1.2.1.jar
      commons-pool-1.2.jar
      msutil.jar
      msbase.jar
      mssqlserver.jar

5 楼 zero1111 2015-04-28  

log4j.appender.STDOUT.Threshold=trace
log4j.category.org.hibernate.SQL=trace
log4j.category.org.hibernate.type=trace

4 楼 zero1111 2015-04-28  

   ResourceLoader loader = new DefaultResourceLoader();
        Resource resource =  loader.getResource("classpath:exportTemplate.json");

3 楼 zero1111 2015-04-28  

    <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-validator</artifactId>
            <version>${hibernate.validator.version}</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>${hibernate-core.version}</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-c3p0</artifactId>
            <version>4.1.4.Final</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-entitymanager</artifactId>
            <version>${hibernate-entitymanager.version}</version>
        </dependency>

2 楼 zero1111 2015-03-19  

按月统计
declare @Year int
set @Year=2015
select
    m as Date0,
    sum(
        case when datepart(month,BEGIN_TIME)=m
        then   1 else 0 end
       )  as count0
from
    [EVENTS] c,
    (
        select 1 m
        union all select 2
        union all select 3
        union all select 4
        union all select 5
        union all select 6
        union all select 7
        union all select 8
        union all select 9
        union all select 10
        union all select 11
        union all select 12
    ) aa
where
    @Year=year(BEGIN_TIME)
group by  
    m

1 楼 zero1111 2015-03-19  

————————————————————————————————————————————————————————————————————————
按小时分组统计工作量

declare @DateTime datetime set @DateTime=getdate()

  select right(100+a,2)+ ':00 -> '+right(100+b,2)+ ':00 ' as DateSpan,
  sum( case when datepart(hour,BEGIN_TIME)> =a and datepart(hour,BEGIN_TIME) <b then 1 else 0 end )
  as countA
   from [EVENTS] c ,
   (select 0 a,1 b union all select 1,2 union all
   select 2,3
   union all
   select 3,4 union all
   select 4,5 union all
   select 5,6 union all
   select 6,7 union all
   select 7,8 union all
   select 8,9 union all
   select 9,10 union all
   select 10,11 union all
   select 11,12 union all
   select 12,13 union all
   select 13,14 union all
   select 14,15 union all
   select 15,16 union all
   select 16,17 union all
   select 17,18 union all
   select 18,19 union all
   select 19,20 union all
   select 20,21 union all
   select 21,22 union all
   select 22,23 union all
   select 23,24 )
    aa where datediff(day,@DateTime,BEGIN_TIME)=0
     group by right(100+a,2)+ ':00 -> '+right(100+b,2)+ ':00 '
  order by DateSpan asc