CREATE TABLE t(userID INT ,docID INT ,docYear int)
INSERT INTO t (userID,docID,docYear)
SELECT 1,1,2006 UNION ALL
SELECT 2,15,2006 UNION ALL
SELECT 3,27,2006 UNION ALL
SELECT 2,34,2007 UNION ALL
SELECT 2,36,2007 UNION ALL
SELECT 3,37,2008 UNION ALL
SELECT 3,40,2009 怎么过滤1年、2年、3年有记录的人员啊?
INSERT INTO t (userID,docID,docYear)
SELECT 1,1,2006 UNION ALL
SELECT 2,15,2006 UNION ALL
SELECT 3,27,2006 UNION ALL
SELECT 2,34,2007 UNION ALL
SELECT 2,36,2007 UNION ALL
SELECT 3,37,2008 UNION ALL
SELECT 3,40,2009 怎么过滤1年、2年、3年有记录的人员啊?
group by userId,docYear
having count(userId)=1--这里可以为2,3
group by userId
having count(userId)=1--这里可以为2,3
-- Author : htl258(Tony)
-- Date : 2010-05-06 15:17:57
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [T]
IF OBJECT_ID('[T]') IS NOT NULL
DROP TABLE [T]
GO
CREATE TABLE t(userID INT ,docID INT ,docYear int)
INSERT INTO t (userID,docID,docYear)
SELECT 1,1,2006 UNION ALL
SELECT 2,15,2006 UNION ALL
SELECT 3,27,2006 UNION ALL
SELECT 2,34,2007 UNION ALL
SELECT 2,36,2007 UNION ALL
SELECT 3,37,2008 UNION ALL
SELECT 3,40,2009 select userID,COUNT(distinct docyear) as docyear
from t
group by userID
/*
userID docyear
----------- -----------
1 1
2 2
3 3(3 行受影响)
*/