数据库结构如下
id xid year
1 1 2000
2 1 2001
3 1 2002
4 1 2005
5 2 2000
6 2 2002
7 2 2003
8 2 2007
9 3 2000求大于连续n年的记录,year必须是连续的,year有断年的情况例如连续三年,2001.2002.2003 也可能是2001.2003.2004.2005也有可能是2001.2002.2003.2004.2005.2006,连续年就是年是连续的(这句话好像有问题)求高手指点注:在sql里面写,如果能在程序中写,我就会判断了2.返回group buy 的 xid即可
id xid year
1 1 2000
2 1 2001
3 1 2002
4 1 2005
5 2 2000
6 2 2002
7 2 2003
8 2 2007
9 3 2000求大于连续n年的记录,year必须是连续的,year有断年的情况例如连续三年,2001.2002.2003 也可能是2001.2003.2004.2005也有可能是2001.2002.2003.2004.2005.2006,连续年就是年是连续的(这句话好像有问题)求高手指点注:在sql里面写,如果能在程序中写,我就会判断了2.返回group buy 的 xid即可
INSERT dbo.TB
SELECT 1, '2000' UNION ALL
SELECT 1, '2001' UNION ALL
SELECT 1, '2002' UNION ALL
SELECT 1, '2005' UNION ALL
SELECT 2, '2000' UNION ALL
SELECT 2, '2002' UNION ALL
SELECT 2, '2003' UNION ALL
SELECT 2, '2007' UNION ALL
SELECT 3, '2000'
SELECT a.*
FROM dbo.TB a ,
( SELECT [year] - id AS col ,
xid
FROM dbo.TB
GROUP BY xid ,
[year] - id
HAVING COUNT([year] - id) = 3--可以查询任意连续数
) b
WHERE a.xid = b.xid
AND ( a.[year] - a.id ) = b.col
DROP TABLE dbo.TB
/*
id xid year
----------- ----------- ----
1 1 2000
2 1 2001
3 1 2002(3 行受影响)
*/
(select xid,cast(year as int)-id sid from tb
group by xid,cast(year as int)-id
having COUNT(*)>=3) b
where a.xid=b.xid
and cast(a.year as int)-a.id=b.sid