给一列赋值的问题
表TB有A、B、C三个字段，现在想将从表TB1中查询的结果插入表TB的C字段，请大虾们帮帮忙
先谢谢


(SELECT e FROM(SELECT e,ct,ROW_NUMBER()OVER(ORDER BY ct DESC) rn FROM(SELECT e,COUNT (*)ct FROM TB1 GROUP BY e)t)t1 WHERE rn=1)
(SELECT e FROM(SELECT e,ct,ROW_NUMBER()OVER(ORDER BY ct DESC) rn FROM(SELECT e,COUNT (*)ct FROM TB1 GROUP BY e)t)t1 WHERE rn=2)
(SELECT e FROM(SELECT e,ct,ROW_NUMBER()OVER(ORDER BY ct DESC) rn FROM(SELECT e,COUNT (*)ct FROM TB1 GROUP BY e)t)t1 WHERE rn=3)
(SELECT e FROM(SELECT e,ct,ROW_NUMBER()OVER(ORDER BY ct DESC) rn FROM(SELECT e,COUNT (*)ct FROM TB1 GROUP BY e)t)t1 WHERE rn=4)
(SELECT e FROM(SELECT e,ct,ROW_NUMBER()OVER(ORDER BY ct DESC) rn FROM(SELECT e,COUNT (*)ct FROM TB1 GROUP BY e)t)t1 WHERE rn=5)


[解决办法]

SQL code

declare @tb table (a int,b int,c varchar(20))insert into @tbselect 1,3,null union allselect 2,3,null union allselect 3,4,null union allselect 4,5,null union allselect 5,6,nulldeclare @tb1 table (e varchar(8),col varchar(1))insert into @tb1select 15,'a' union allselect 20,'a' union allselect 15,'a' union allselect 20,'a' union allselect 36,'a' union allselect 36,'a' union allselect 36,'a' union allselect 48,'a' union allselect 51,'a' union allselect 51,'a' union allselect 61,'a' update @tb set c=b.e from @tb aleft join(select row_number() over (order by count (*) desc) as id, e from @tb1 group by e) b on a.a=b.idselect * from @tb/*a           b           c----------- ----------- --------------------1           3           362           3           153           4           204           5           515           6           61*/--如果是空表直接插入的话insert into @tb (c)select e from @tb1 group by e order by count (*) desc