請教各位高手，我想篩選一批資料，根據ID取出departID最大的一組資料，將其他的資料去掉（如圖只取出黃色背景資料）。
實在么有頭緒，請教各位專家。
uj5u.com熱心網友回復：
根據name查出departid最大的資料是嗎uj5u.com熱心網友回復：
根據ID取出departID最大的一組資料
uj5u.com熱心網友回復：
select ID,max(departID)departID,name from (
select 1 as ID,101 departID,'蘋果' name union all
select 1 as ID,101 departID,'橘子' name union all
select 1 as ID,101 departID,'葡萄' name union all
select 1 as ID,102 departID,'蘋果' name union all
select 1 as ID,102 departID,'橘子' name union all
select 1 as ID,102 departID,'葡萄' name union all
select 2 as ID,151 departID,'蘋果' name union all
select 2 as ID,151 departID,'橘子' name union all
select 2 as ID,151 departID,'葡萄' name union all
select 2 as ID,115 departID,'蘋果' name union all
select 2 as ID,115 departID,'橘子' name union all
select 2 as ID,115 departID,'葡萄' name union all
select 2 as ID,113 departID,'蘋果' name union all
select 2 as ID,113 departID,'橘子' name union all
select 2 as ID,113 departID,'葡萄' name ) a group by ID,name order by ID
uj5u.com熱心網友回復：
select a.*,b.namefrom
(select ID,max(departID) departID
from
(select 1 as ID,101 departID,'蘋果' name union all
select 1 as ID,101 departID,'橘子' name union all
select 1 as ID,101 departID,'葡萄' name union all
select 1 as ID,102 departID,'蘋果' name union all
select 1 as ID,102 departID,'橘子' name union all
select 1 as ID,102 departID,'葡萄' name union all
select 2 as ID,151 departID,'蘋果' name union all
select 2 as ID,151 departID,'橘子' name union all
select 2 as ID,151 departID,'葡萄' name union all
select 2 as ID,115 departID,'蘋果' name union all
select 2 as ID,115 departID,'橘子' name union all
select 2 as ID,115 departID,'葡萄' name union all
select 2 as ID,113 departID,'橙子' name union all
select 2 as ID,113 departID,'甘蔗' name union all
select 2 as ID,113 departID,'菠蘿' name
) a group by ID order by ID
) as a
inner join
(select 1 as ID,101 departID,'蘋果' name union all
select 1 as ID,101 departID,'橘子' name union all
select 1 as ID,101 departID,'葡萄' name union all
select 1 as ID,102 departID,'蘋果' name union all
select 1 as ID,102 departID,'橘子' name union all
select 1 as ID,102 departID,'葡萄' name union all
select 2 as ID,151 departID,'蘋果' name union all
select 2 as ID,151 departID,'橘子' name union all
select 2 as ID,151 departID,'葡萄' name union all
select 2 as ID,115 departID,'蘋果' name union all
select 2 as ID,115 departID,'橘子' name union all
select 2 as ID,115 departID,'葡萄' name union all
select 2 as ID,113 departID,'橙子' name union all
select 2 as ID,113 departID,'甘蔗' name union all
select 2 as ID,113 departID,'菠蘿' name ) as b
on a.departid = b.departid
uj5u.com熱心網友回復：
可以用子查詢，先取出每個ID對應的最大departID，再inner join取出nameDECLARE @t TABLE(ID INT NOT NULL, departID INT NOT NULL,[name] NVARCHAR(100) NOT NULL)
INSERT @t(ID, departID, name) VALUES
( 1 ,101 ,'蘋果' ),( 1 ,101 ,'橘子' ),( 1 ,101 ,'葡萄' ),( 1 ,102 ,'蘋果' ),
( 1 ,102 ,'橘子' ),( 1 ,102 ,'葡萄' ),( 2 ,151 ,'蘋果' ),( 2 ,151 ,'橘子' ),
( 2 ,151 ,'葡萄' ),( 2 ,115 ,'蘋果' ),( 2 ,115 ,'橘子' ),( 2 ,115 ,'葡萄' ),
( 2 ,113 ,'蘋果' ),( 2 ,113 ,'橘子' ),( 2 ,113 ,'葡萄' )
SELECT b.* FROM (
SELECT id,MAX(departID) departID FROM @t GROUP BY id) a INNER JOIN @t b ON b.ID = a.ID AND a.departID=b.departID
ORDER BY b.id,b.departID,b.[name]
uj5u.com熱心網友回復：
select id,max(deparid),name into table2 from table1 group by id,name轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/43048.html
標籤：基礎類
上一篇：mysql子查詢添加記錄
下一篇：SSIS設定動態連接字串報錯