请教各位大虾:表如下:
id parentid no
1 101 0
2 101 1
3 101 2
4 102 0
5 102 1
6 103 0
7 104 0
8 105 0
9 106 0
10 106 1
11 106 2
需求:
将同一个parentid 的数据中,NO值最大的数据的id查出
id parentid no
1 101 0
2 101 1
3 101 2
4 102 0
5 102 1
6 103 0
7 104 0
8 105 0
9 106 0
10 106 1
11 106 2
需求:
将同一个parentid 的数据中,NO值最大的数据的id查出
group by parentid
select parentid, max(no) from tablename
group by parentid
create table #temp(id int, parentid int, no int)
insert into #temp
select 1, 101, 0 union all
select 2, 101, 1 union all
select 3, 102, 2 union all
select 4, 102, 2select b.* from
(select parentid, maxno = max([no]) from #temp group by parentid) a
cross apply
(select * from #temp where parentid = a.parentid and [no] = a.maxno) b
order by b.id
CREATE TABLE #t(id INT,parentid INT,no INT)
INSERT #t
SELECT 1, 101,0 UNION ALL
SELECT 2 ,101, 1 UNION ALL
SELECT 3 ,101, 2 UNION ALL
SELECT 4, 102, 0 uNION ALL
SELECT 5, 102, 1 SELECT id FROM (SELECT parentid,MAX(no) as no FROM #t
GROUP BY parentid)a,#t b WHERE a.parentid=b.parentid AND a.no=b.noid
-----------
5
3(2 行受影响)
declare @table table (id int,parentid int,no int)
insert into @table
select 1,101,0 union all
select 2,101,1 union all
select 3,101,2 union all
select 4,102,0 union all
select 5,102,1 union all
select 6,103,0 union all
select 7,104,0 union all
select 8,105,0 union all
select 9,106,0 union all
select 10,106,1 union all
select 11,106,2select parentid,MAX(no) AS no from @table GROUP BY parentid
/*
parentid no
----------- -----------
101 2
102 1
103 0
104 0
105 0
106 2
*/
create table t1
(
id int,
pid int,
ino int
)
insert into t1
select 1, 101, 0 union all
select 2, 101, 1 union all
select 3, 101, 2 union all
select 4, 102, 0 union all
select 5, 102, 1 union all
select 6, 103, 0 union all
select 7, 104, 0 union all
select 8, 105, 0 union all
select 9, 106, 0 union all
select 10, 106, 1 union all
select 11, 106, 2;with abc as
(select pid,max(ino) as ino from t1 group by pid)
select * from t1 where exists (select 1 from abc a where a.pid=t1.pid and a.ino=t1.ino)
select a.* from ta a join (Select parentid,max(no) as no from ta b group by parentid) c
on c.parentid=a.parentid and c.no=a.no
group by parentid
insert into @table
select 1,101,0 union all
select 2,101,1 union all
select 3,101,2 union all
select 4,102,0 union all
select 5,102,1 union all
select 6,103,0 union all
select 7,104,0 union all
select 8,105,0 union all
select 9,106,0 union all
select 10,106,1 union all
select 11,106,2select parentid,MAX(no) AS no from @table GROUP BY parentid
if object_id('tempdb.dbo.D') is not null drop table D
go
create table D([id] int,[parentid] int,[no] int)
insert D
select 1,101,0 union all
select 2,101,1 union all
select 3,101,2 union all
select 4,102,0 union all
select 5,102,1 union all
select 6,103,0 union all
select 7,104,0 union all
select 8,105,0 union all
select 9,106,0 union all
select 10,106,1 union all
select 11,106,2
*//*将同一个parentid 的数据中,NO值最大的数据查出*/
select D.parentid,MAX(D.no) as maxno
from D
group by D.parentid/*将同一个parentid 的数据中,no值最大的数据的id查出*/
select b.* from
(select parentid, maxno = max([no]) from D group by parentid) a
cross apply
(select * from D where parentid = a.parentid and [no] = a.maxno) b
order by b.id
--建表
CREATE TABLE #table
(
id INT,
parentid INT,
[NO] INT
)
INSERT INTO #table
SELECT 1 ,101 ,0 UNION ALL
SELECT 2 ,101 ,1 UNION ALL
SELECT 3 ,101 ,2 UNION ALL
SELECT 4 ,102 ,0 UNION ALL
SELECT 5 ,102 ,1 UNION ALL
SELECT 6 ,103 ,0 UNION ALL
SELECT 7 ,104 ,0 UNION ALL
SELECT 8 ,105 ,0 UNION ALL
SELECT 9 ,106 ,0 UNION ALL
SELECT 10 ,106 ,1 UNION ALL
SELECT 11 ,106 ,2
--查询
SELECT a.* FROM #table a,
(SELECT parentid,MAX([NO]) maxno FROM #table
GROUP BY parentid) b
WHERE a.parentid=b.parentid
AND a.[no]=b.maxno
ORDER BY a.idDROP TABLE #table
--结果
id parentid NO
3 101 2
5 102 1
6 103 0
7 104 0
8 105 0
11 106 2
(
ID INT ,
parentid INT ,
NO INT
)
INSERT INTO A VALUES(1,101,0);
INSERT INTO A VALUES(2,101,1);
INSERT INTO A VALUES(3,101,2);
INSERT INTO A VALUES(4,102,0);
INSERT INTO A VALUES(5,102,1);
INSERT INTO A VALUES(6,103,0);
INSERT INTO A VALUES(7,104,0);
INSERT INTO A VALUES(8,105,0);
INSERT INTO A VALUES(9,106,0);
INSERT INTO A VALUES(10,106,1);
INSERT INTO A VALUES(11,106,2);SELECT * FROM A WHERE (parentid,NO) IN ( SELECT parentid,max(NO) FROM A WHERE no=(SELECT MAX(NO) FROM A) GROUP BY parentid )DROP TABLE A
select id from tb where no in (select max(no) from tb group by parentid)