表结构:CREATE TABLE [dbo].[OriginUsers](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Date] [datetime] NULL,
[OriginID] [char](3) COLLATE Chinese_PRC_CI_AS NULL,
[UserCount] [int] NULL,
[BoxType] [int] NOT NULL,
CONSTRAINT [PK_OriginUsers] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]数据:
ID DATE ORIGINID USERCOUNT BOXTYPE
1 2011-07-11 111 1 21
2 2011-07-11 111 1 21
3 2011-07-11 111 2 20
4 2011-07-12 123 45 21
5 2011-07-12 123 45 21
6 2011-07-13 123 4 21
从数据中看到,每天都有一些重复的数据,写一条语句,要把每天重复的内容查出来,这条语句要怎么写呢?
[ID] [int] IDENTITY(1,1) NOT NULL,
[Date] [datetime] NULL,
[OriginID] [char](3) COLLATE Chinese_PRC_CI_AS NULL,
[UserCount] [int] NULL,
[BoxType] [int] NOT NULL,
CONSTRAINT [PK_OriginUsers] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]数据:
ID DATE ORIGINID USERCOUNT BOXTYPE
1 2011-07-11 111 1 21
2 2011-07-11 111 1 21
3 2011-07-11 111 2 20
4 2011-07-12 123 45 21
5 2011-07-12 123 45 21
6 2011-07-13 123 4 21
从数据中看到,每天都有一些重复的数据,写一条语句,要把每天重复的内容查出来,这条语句要怎么写呢?
where exists(select 1 from tb where DATE=a.DATE and ORIGINID=a.ORIGINID
and USERCOUNT=a.USERCOUNT and BOXTYPE=a.BOXTYPE and id<>a.id)
from tb a
where exists(select 1 from tb where datediff(d,DATE,a.DATE) = 0 and id<>a.id)
not exists(select distinct DATE, ORIGINID ,USERCOUNT, BOXTYPE
from tb where DATE=a.DATE and ORIGINID=a.ORIGINID
and USERCOUNT=a.USERCOUNT and BOXTYPE=a.BOXTYPE)
SELECT '2011-07-11','111',1,21
UNION ALL
SELECT '2011-07-11','111',1,21
UNION ALL
SELECT '2011-07-11','111',2,20
UNION ALL
SELECT '2011-07-12','111',45,21
UNION ALL
SELECT '2011-07-12','111',45,21
UNION ALL
SELECT '2011-07-13','123',4,21
SELECT b.* FROM OriginUsers a ,OriginUsers b
WHERE a.[date]=b.[date] AND a.originid=b.OriginID AND a.usercount=b.usercount AND a.boxtype=b.boxtype
AND a.id<b.id