22,206
社区成员
发帖
与我相关
我的任务
分享
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
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
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
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
*/