yy_id y_name yy_lx
1 名字 1
2 名字 2
3 名字 3
4 名字 4
5 名字 5
6 名字 6
7 名字 7
8 名字 1
9 名字 2
10 名字 3
我想查询出 最后一出现的 一次 yylx = 1到7 数据
1 名字 1
2 名字 2
3 名字 3
4 名字 4
5 名字 5
6 名字 6
7 名字 7
8 名字 1
9 名字 2
10 名字 3
我想查询出 最后一出现的 一次 yylx = 1到7 数据
from tb t
where
not exists(select 1 from tb where y_name=t.y_name and yy_lx=t.yy_lx and yy_id>t.yy_id)
from tb k
where not exists(select * from tb where k.yy_lx=yy_lx and yy_id>k.yy_id)
select *
from tb t
where
yy_lx between 1 and 7
and
not exists(select 1 from tb where yy_lx between 1 and 7 and y_name=t.y_name and yy_lx=t.yy_lx and yy_id>t.yy_id)
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-08-26 10:44:46
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([yy_id] int,[y_name] varchar(4),[yy_lx] int)
insert [tb]
select 1,'名字',1 union all
select 2,'名字',2 union all
select 3,'名字',3 union all
select 4,'名字',4 union all
select 5,'名字',5 union all
select 6,'名字',6 union all
select 7,'名字',7 union all
select 8,'名字',1 union all
select 9,'名字',2 union all
select 10,'名字',3
--------------开始查询--------------------------
select
*
from
tb t
where
not exists(select 1 from tb where y_name=t.y_name and yy_lx=t.yy_lx and yy_id>t.yy_id)
----------------结果----------------------------
/*yy_id y_name yy_lx
----------- ------ -----------
4 名字 4
5 名字 5
6 名字 6
7 名字 7
8 名字 1
9 名字 2
10 名字 3(7 行受影响)
*/
(SELECT TOP 7 * FROM TB T1
INNER JOIN T2 ON T2.YY_ID=T1.YY_ID+1 AND T2.YY_LX=2
INNER JOIN T3 ON T3.YY_ID=T1.YY_ID+2 AND T3.YY_LX=3
INNER JOIN T4 ON T4.YY_ID=T1.YY_ID+3 AND T4.YY_LX=4
INNER JOIN T5 ON T5.YY_ID=T1.YY_ID+4 AND T5.YY_LX=5
INNER JOIN T6 ON T6.YY_ID=T1.YY_ID+5 AND T6.YY_LX=6
INNER JOIN T7 ON T7.YY_ID=T1.YY_ID+6 AND T7.YY_LX=7
WHERE T1.YY_LX=1
ORDER BY YY_ID DESC) T
ORDER BY YY_ID ASC
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-08-26 10:46:31
-------------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (yy_id INT,y_name VARCHAR(4),yy_lx INT)
INSERT INTO @T
SELECT 1,'名字',1 UNION ALL
SELECT 2,'名字',2 UNION ALL
SELECT 3,'名字',3 UNION ALL
SELECT 4,'名字',4 UNION ALL
SELECT 5,'名字',5 UNION ALL
SELECT 6,'名字',6 UNION ALL
SELECT 7,'名字',7 UNION ALL
SELECT 8,'名字',1 UNION ALL
SELECT 9,'名字',2 UNION ALL
SELECT 10,'名字',3 UNION ALL
SELECT 11,'名字',4 UNION ALL
SELECT 12,'名字',5 UNION ALL
SELECT 13,'名字',6 UNION ALL
SELECT 14,'名字',7 UNION ALL
SELECT 15,'名字',8 UNION ALL
SELECT 16,'名字',9 --SQL查询如下:
SELECT TOP 1 rowid,MIN(yy_id) AS minID,MAX(yy_id) AS maxID INTO #tmp
FROM (
SELECT rowid = ROW_NUMBER() OVER(ORDER BY yy_id) - yy_lx,*
FROM @T
) AS A
GROUP BY rowid
HAVING COUNT(*) >=7 AND MIN(yy_lx) = 1 AND MAX(yy_lx) >=7
ORDER BY rowid DESC;
SELECT A.* FROM @T AS A JOIN #tmp ON yy_id BETWEEN minID AND minID + 6;
DROP TABLE #tmp;/*
yy_id y_name yy_lx
----------- ------ -----------
8 名字 1
9 名字 2
10 名字 3
11 名字 4
12 名字 5
13 名字 6
14 名字 7(7 行受影响)*/
---------------------------------------------------------------
--无耻地抄袭小F的数据
--另一种猜测
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-08-26 10:44:46
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([yy_id] int,[y_name] varchar(4),[yy_lx] int)
insert [tb]
select 1,'名字',1 union all
select 2,'名字',2 union all
select 3,'名字',3 union all
select 4,'名字',4 union all
select 5,'名字',5 union all
select 6,'名字',6 union all
select 7,'名字',7 union all
select 8,'名字',1 union all
select 9,'名字',2 union all
select 10,'名字',3
--------------开始查询--------------------------
DECLARE @YY_ID INT
SELECT TOP 1 @YY_ID=T1.YY_ID FROM TB T1
INNER JOIN TB T2 ON T2.YY_ID=T1.YY_ID+1 AND T2.YY_LX=2
INNER JOIN TB T3 ON T3.YY_ID=T1.YY_ID+2 AND T3.YY_LX=3
INNER JOIN TB T4 ON T4.YY_ID=T1.YY_ID+3 AND T4.YY_LX=4
INNER JOIN TB T5 ON T5.YY_ID=T1.YY_ID+4 AND T5.YY_LX=5
INNER JOIN TB T6 ON T6.YY_ID=T1.YY_ID+5 AND T6.YY_LX=6
INNER JOIN TB T7 ON T7.YY_ID=T1.YY_ID+6 AND T7.YY_LX=7
WHERE T1.YY_LX=1
ORDER BY T1.YY_ID DESCSELECT TOP 7 * FROM TB WHERE YY_ID>=@YY_ID----------------结果----------------------------
/*yy_id y_name yy_lx
----------- ------ -----------
1 名字 1
2 名字 2
3 名字 3
4 名字 4
5 名字 5
6 名字 6
7 名字 7(7 行受影响)
*/
---------------------------------------------------------------
--加入一组符合条件的数据,取出编号最大的一级1-7
--无耻地抄袭小F的数据
--另一种猜测
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-08-26 10:44:46
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([yy_id] int,[y_name] varchar(4),[yy_lx] int)
insert [tb]
select 1,'名字',1 union all
select 2,'名字',2 union all
select 3,'名字',3 union all
select 4,'名字',4 union all
select 5,'名字',5 union all
select 6,'名字',6 union all
select 7,'名字',7 union all
select 8,'名字',1 union all
select 9,'名字',2 union all
select 10,'名字',1 union all
select 11,'名字',2 union all
select 12,'名字',3 union all
select 13,'名字',4 union all
select 14,'名字',5 union all
select 15,'名字',6 union all
select 16,'名字',7 union all
select 17,'名字',3
--------------开始查询--------------------------
DECLARE @YY_ID INT
SELECT TOP 1 @YY_ID=T1.YY_ID FROM TB T1
INNER JOIN TB T2 ON T2.YY_ID=T1.YY_ID+1 AND T2.YY_LX=2
INNER JOIN TB T3 ON T3.YY_ID=T1.YY_ID+2 AND T3.YY_LX=3
INNER JOIN TB T4 ON T4.YY_ID=T1.YY_ID+3 AND T4.YY_LX=4
INNER JOIN TB T5 ON T5.YY_ID=T1.YY_ID+4 AND T5.YY_LX=5
INNER JOIN TB T6 ON T6.YY_ID=T1.YY_ID+5 AND T6.YY_LX=6
INNER JOIN TB T7 ON T7.YY_ID=T1.YY_ID+6 AND T7.YY_LX=7
WHERE T1.YY_LX=1
ORDER BY T1.YY_ID DESCSELECT TOP 7 * FROM TB WHERE YY_ID>=@YY_ID----------------结果----------------------------
/*yy_id y_name yy_lx
----------- ------ -----------
10 名字 1
11 名字 2
12 名字 3
13 名字 4
14 名字 5
15 名字 6
16 名字 7(7 行受影响)
*/
ZL试试这个,梁哥那个应该也行
---------------------------------------------------------------
--LZ试试我这个查询是否是你要的
--无耻地抄袭小F的数据
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-08-26 10:44:46
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([yy_id] int,[y_name] varchar(4),[yy_lx] int)
insert [tb]
select 1,'名字',1 union all
select 2,'名字',2 union all
select 3,'名字',3 union all
select 4,'名字',4 union all
select 5,'名字',5 union all
select 6,'名字',6 union all
select 7,'名字',7 union all
select 8,'名字',1 union all
select 9,'名字',2 union all
select 10,'名字',1 union all
select 11,'名字',2 union all
select 12,'名字',3 union all
select 13,'名字',4 union all
select 14,'名字',5 union all
select 15,'名字',1 union all
select 16,'名字',2 union all
select 17,'名字',5
--------------开始查询--------------------------
DECLARE @YY_ID INT
SELECT TOP 1 @YY_ID=T1.YY_ID FROM TB T1
WHERE T1.YY_LX=1
ORDER BY T1.YY_ID DESC
SELECT YY_ID,Y_NAME,YY_LX FROM (
SELECT TOP 7 ROW_NUMBER() OVER (ORDER BY YY_LX ASC) 'ROW',* FROM TB T1
WHERE T1.YY_ID>=@YY_ID ) T WHERE ROW=YY_LX----------------结果----------------------------
/*yy_id y_name yy_lx
----------- ------ -----------
15 名字 1
16 名字 2(7 行受影响)
*/