sql 查重

dangyj 2018-02-06 05:23:33
有表如下


请问一下,怎么对比近两年和近三年重复的数据有多少?需要算出重复的总数

主要目的是统计一下近两年和近三年中重复走访的公司数量。

谢谢大神
...全文
630 6 打赏 收藏 转发到动态 举报
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
RINK_1 2018-02-07
  • 打赏
  • 举报
回复

SELECT CASE WHEN GROUPING(item0)=1 THEN '总计' ELSE item0 END AS ITEM0,
SUM(CASE WHEN DATEDIFF(YEAR,item1,GETDATE())>=0 AND DATEDIFF(YEAR,item1,GETDATE())<=1 THEN 1 ELSE 0 END) AS PAST_2,
SUM(CASE WHEN DATEDIFF(YEAR,item1,GETDATE())>=0 AND DATEDIFF(YEAR,item1,GETDATE())<=2 THEN 1 ELSE 0 END) AS PAST_3
FROM TABLE
WHERE DATEDIFF(YEAR,item1,GETDATE())>=0 AND DATEDIFF(YEAR,item1,GETDATE())<=2
GROUP BY item0 WITH ROLLUP
HAVING SUM(CASE WHEN DATEDIFF(YEAR,item1,GETDATE())>=0 AND DATEDIFF(YEAR,item1,GETDATE())<=1 THEN 1 ELSE 0 END)>1 
OR SUM(CASE WHEN DATEDIFF(YEAR,item1,GETDATE())>=0 AND DATEDIFF(YEAR,item1,GETDATE())<=2 THEN 1 ELSE 0 END)>1
文盲老顾 2018-02-07
  • 打赏
  • 举报
回复
基本内容都差不多,问题在于2年内这个定义的解释,比如现在是2018/2/7,那么2016/2/1是算在两年内还是算在两年外 我给的命令是算在两年外的,楼上几位是算在两年内的
二月十六 2018-02-07
  • 打赏
  • 举报
回复
总数的在外边在套一层
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
id INT PRIMARY KEY,
item0 NVARCHAR(10),
item1 DATETIME
)
GO
INSERT INTO t VALUES (1,'a.公司','2018-02-01')
INSERT INTO t VALUES (2,'a.公司','2017-02-01')
INSERT INTO t VALUES (3,'b.公司','2017-02-01')
INSERT INTO t VALUES (4,'b.公司','2018-02-01')
INSERT INTO t VALUES (7,'a.公司','2017-02-01')
INSERT INTO t VALUES (9,'b.公司','2017-02-01')
INSERT INTO t VALUES (10,'c.公司','2016-02-01')
INSERT INTO t VALUES (11,'b.公司','2017-02-01')
INSERT INTO t VALUES (12,'b.公司','2016-02-01')
INSERT INTO t VALUES (13,'a.公司','2016-02-01')
INSERT INTO t VALUES (14,'b.公司','2017-02-01')
INSERT INTO t VALUES (15,'c.公司','2018-02-01')
INSERT INTO t VALUES (16,'c.公司','2018-02-01')
INSERT INTO t VALUES (17,'a.公司','2017-02-01')
INSERT INTO t VALUES (18,'b.公司','2017-02-01')
INSERT INTO t VALUES (19,'a.公司','2016-02-01')

SELECT SUM(rep_cnt) FROM (
SELECT t.item0,SUM(CASE WHEN DATEDIFF(YEAR,item1,GETDATE())<=2 THEN 1 ELSE 0 END) AS rep_cnt
FROM t
GROUP BY t.item0
HAVING COUNT(1)>1)t


文盲老顾 2018-02-07
  • 打赏
  • 举报
回复
with t as (
	select 1 as id,'a.公司' as item0,convert(date,'2018-2-1') as item1
	union all
	select 2,'a.公司','2017-2-1'
	union all
	select 3,'b.公司','2017-2-1'
	union all
	select 4,'b.公司','2018-2-1'
	union all
	select 7,'a.公司','2017-2-1'
	union all
	select 9,'b.公司','2017-2-1'
	union all
	select 10,'c.公司','2016-2-1'
	union all
	select 11,'b.公司','2017-2-1'
	union all
	select 12,'b.公司','2016-2-1'
	union all
	select 13,'a.公司','2016-2-1'
	union all
	select 14,'b.公司','2017-2-1'
	union all
	select 15,'c.公司','2018-2-1'
	union all
	select 16,'c.公司','2018-2-1'
	union all
	select 17,'a.公司','2017-2-1'
	union all
	select 18,'b.公司','2017-2-1'
	union all
	select 19,'a.公司','2016-2-1'
),tt as (
	select item0,count(0) as cnt,3 as years from t where item1>dateadd(year,-3,getdate()) group by item0
	union all
	select item0,count(0) as cnt,2 as years from t where item1>dateadd(year,-2,getdate()) group by item0
)
select * from tt
union all
select convert(varchar,years)+'年内重复合计',sum(cnt),years from tt group by years
order by years
dangyj 2018-02-07
  • 打赏
  • 举报
回复
引用 1 楼 yenange 的回复:
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
	id INT PRIMARY KEY,
	item0 NVARCHAR(10),
	item1 DATETIME
)
GO
INSERT INTO t VALUES (1,'a.公司','2018-02-01')
INSERT INTO t VALUES (2,'a.公司','2017-02-01')
INSERT INTO t VALUES (3,'b.公司','2017-02-01')
INSERT INTO t VALUES (4,'b.公司','2018-02-01')
INSERT INTO t VALUES (7,'a.公司','2017-02-01')
INSERT INTO t VALUES (9,'b.公司','2017-02-01')
INSERT INTO t VALUES (10,'c.公司','2016-02-01')
INSERT INTO t VALUES (11,'b.公司','2017-02-01')
INSERT INTO t VALUES (12,'b.公司','2016-02-01')
INSERT INTO t VALUES (13,'a.公司','2016-02-01')
INSERT INTO t VALUES (14,'b.公司','2017-02-01')
INSERT INTO t VALUES (15,'c.公司','2018-02-01')
INSERT INTO t VALUES (16,'c.公司','2018-02-01')
INSERT INTO t VALUES (17,'a.公司','2017-02-01')
INSERT INTO t VALUES (18,'b.公司','2017-02-01')
INSERT INTO t VALUES (19,'a.公司','2016-02-01')

SELECT t.item0,SUM(CASE WHEN DATEDIFF(YEAR,item1,GETDATE())<=2 THEN 1 ELSE 0 END) AS rep_cnt
FROM t
GROUP BY t.item0
HAVING COUNT(1)>1
/*
item0   rep_cnt
a.公司	6
b.公司	7
c.公司	3
*/
SELECT t.item0,SUM(CASE WHEN DATEDIFF(YEAR,item1,GETDATE())<=3 THEN 1 ELSE 0 END) AS rep_cnt
FROM t
GROUP BY t.item0
HAVING COUNT(1)>1
/*
item0   rep_cnt
a.公司	6
b.公司	7
c.公司	3
*/
请问,如果我要在统计这三个公司的重复的总数为16,该怎么写呢?
吉普赛的歌 2018-02-06
  • 打赏
  • 举报
回复
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
	id INT PRIMARY KEY,
	item0 NVARCHAR(10),
	item1 DATETIME
)
GO
INSERT INTO t VALUES (1,'a.公司','2018-02-01')
INSERT INTO t VALUES (2,'a.公司','2017-02-01')
INSERT INTO t VALUES (3,'b.公司','2017-02-01')
INSERT INTO t VALUES (4,'b.公司','2018-02-01')
INSERT INTO t VALUES (7,'a.公司','2017-02-01')
INSERT INTO t VALUES (9,'b.公司','2017-02-01')
INSERT INTO t VALUES (10,'c.公司','2016-02-01')
INSERT INTO t VALUES (11,'b.公司','2017-02-01')
INSERT INTO t VALUES (12,'b.公司','2016-02-01')
INSERT INTO t VALUES (13,'a.公司','2016-02-01')
INSERT INTO t VALUES (14,'b.公司','2017-02-01')
INSERT INTO t VALUES (15,'c.公司','2018-02-01')
INSERT INTO t VALUES (16,'c.公司','2018-02-01')
INSERT INTO t VALUES (17,'a.公司','2017-02-01')
INSERT INTO t VALUES (18,'b.公司','2017-02-01')
INSERT INTO t VALUES (19,'a.公司','2016-02-01')

SELECT t.item0,SUM(CASE WHEN DATEDIFF(YEAR,item1,GETDATE())<=2 THEN 1 ELSE 0 END) AS rep_cnt
FROM t
GROUP BY t.item0
HAVING COUNT(1)>1
/*
item0   rep_cnt
a.公司	6
b.公司	7
c.公司	3
*/
SELECT t.item0,SUM(CASE WHEN DATEDIFF(YEAR,item1,GETDATE())<=3 THEN 1 ELSE 0 END) AS rep_cnt
FROM t
GROUP BY t.item0
HAVING COUNT(1)>1
/*
item0   rep_cnt
a.公司	6
b.公司	7
c.公司	3
*/

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧