insert into B(ID,NUMS) select ID,max(NUMS) from A group by ID
DECLARE @TB TABLE([ID] VARCHAR(3), [NUMS] INT) INSERT @TB SELECT '001', 30 UNION ALL SELECT '001', 50 UNION ALL SELECT '001', 20 UNION ALL SELECT '002', 20 UNION ALL SELECT '002', 80SELECT ID,MAX(NUMS) AS NUMS FROM @TB GROUP BY ID /* ID NUMS ---- ----------- 001 50 002 80 */
select ID, max(NUMS) as NUMS GROUP BY ID
select ID,max(NUMS) as NUMS from A GROUP BY ID
select * from A t where not exists(select 1 from A where id=t.id and nums>t.nums)
insert into B(ID,NUMS) select ID,max(NUMS) from A group by ID 不知道是楼主的意思咱们没有理解还是什么的。感觉没有必要使用动态SQL的。
INSERT @TB
SELECT '001', 30 UNION ALL
SELECT '001', 50 UNION ALL
SELECT '001', 20 UNION ALL
SELECT '002', 20 UNION ALL
SELECT '002', 80SELECT ID,MAX(NUMS) AS NUMS
FROM @TB
GROUP BY ID
/*
ID NUMS
---- -----------
001 50
002 80
*/
ID,
max(NUMS) as NUMS
GROUP BY
ID
select ID,max(NUMS) as NUMS from A GROUP BY ID
不知道是楼主的意思咱们没有理解还是什么的。感觉没有必要使用动态SQL的。