KID(主键,自增) FID(外键) FINA (标示,为1或0)
52 59 0
51 83 0
50 61 0
49 72 0
48 71 1
47 61 1
46 84 1
45 84 0
44 59 0
43 61 0
42 59 1
41 61 0
39 71 0
38 59 1
37 83 1
想得到 FINA 是1,nid 不重复的 前 5个nid记录
结果应该就是
48
47
46
42
37建表代码
use pubs
create table newtb
(
nid int,
fid int,
fina int
)
insert newtb values(52,59,0)
insert newtb values(51,83,0)
insert newtb values(50,61,0)
insert newtb values(49,72,0)
insert newtb values(48,71,1)
insert newtb values(47,61,1)
insert newtb values(46,84,1)
insert newtb values(45,84,0)
insert newtb values(44,59,0)
insert newtb values(43,61,0)
insert newtb values(42,59,1)
insert newtb values(41,61,0)
insert newtb values(39,71,0)
insert newtb values(38,59,1)
insert newtb values(37,83,1)
52 59 0
51 83 0
50 61 0
49 72 0
48 71 1
47 61 1
46 84 1
45 84 0
44 59 0
43 61 0
42 59 1
41 61 0
39 71 0
38 59 1
37 83 1
想得到 FINA 是1,nid 不重复的 前 5个nid记录
结果应该就是
48
47
46
42
37建表代码
use pubs
create table newtb
(
nid int,
fid int,
fina int
)
insert newtb values(52,59,0)
insert newtb values(51,83,0)
insert newtb values(50,61,0)
insert newtb values(49,72,0)
insert newtb values(48,71,1)
insert newtb values(47,61,1)
insert newtb values(46,84,1)
insert newtb values(45,84,0)
insert newtb values(44,59,0)
insert newtb values(43,61,0)
insert newtb values(42,59,1)
insert newtb values(41,61,0)
insert newtb values(39,71,0)
insert newtb values(38,59,1)
insert newtb values(37,83,1)
(
kid int,
fid int,
fina int
)
kid 写成nid了,大家帮帮忙啊
select top 5 * from (
select nid,max(fid) fid,max(fina) fina from newtb
group by nid
having max(fina)=1
) tmp order by nid desc-----------------------------
48 71 1
47 61 1
46 84 1
42 59 1
38 59 1
--> 生成测试数据: @T
DECLARE @T TABLE (KID INT,FID INT,FINA INT)
INSERT INTO @T
SELECT 52,59,0 UNION ALL
SELECT 51,83,0 UNION ALL
SELECT 50,61,0 UNION ALL
SELECT 49,72,0 UNION ALL
SELECT 48,71,1 UNION ALL
SELECT 47,61,1 UNION ALL
SELECT 46,84,1 UNION ALL
SELECT 45,84,0 UNION ALL
SELECT 44,59,0 UNION ALL
SELECT 43,61,0 UNION ALL
SELECT 42,59,1 UNION ALL
SELECT 41,61,0 UNION ALL
SELECT 39,71,0 UNION ALL
SELECT 38,59,1 UNION ALL
SELECT 37,83,1--SQL查询如下:SELECT TOP 5 *
FROM @T AS T
WHERE NOT EXISTS
(
SELECT *
FROM @T
WHERE FID=T.FID
AND KID>T.KID
AND FINA=1
)
AND FINA=1
ORDER BY KID DESC/*
KID FID FINA
----------- ----------- -----------
48 71 1
47 61 1
46 84 1
42 59 1
37 83 1(5 行受影响)
*/
select top 5 * from (
select max(nid) nid,fid,max(fina) fina from newtb
group by fid
having max(fina)=1
) tmp order by nid desc-------------------------------52 59 1
51 83 1
50 61 1
48 71 1
46 84 1
create table newtb
(
nid int,
fid int,
fina int
)
insert newtb values(52,59,0)
insert newtb values(51,83,0)
insert newtb values(50,61,0)
insert newtb values(49,72,0)
insert newtb values(48,71,1)
insert newtb values(47,61,1)
insert newtb values(46,84,1)
insert newtb values(45,84,0)
insert newtb values(44,59,0)
insert newtb values(43,61,0)
insert newtb values(42,59,1)
insert newtb values(41,61,0)
insert newtb values(39,71,0)
insert newtb values(38,59,1)
insert newtb values(37,83,1)
SELECT * FROM (SELECT * FROM NEWTB WHERE FINA=1) A
WHERE (SELECT COUNT(*) FROM (SELECT * FROM NEWTB WHERE FINA=1)B WHERE B.FID=A.FID AND NID>A.NID)<1
ORDER BY NID DESC DROP TABLE NEWTB/**
48 71 1
47 61 1
46 84 1
42 59 1
37 83 1
where fina = 1 and kid not in(
select a.kid
from (Select * From newtb Where fina = 1) a, (Select * From newtb Where fina = 1) b
where a.kid <> b.kid and a.fid = b.fid and a.kid < b.kid )
这样也可以,结贴了,多谢大家