还是给一个字段赋值的问题
先谢谢大虾maco_wang的帮助,可是我发现问题没说得完整,如果要将从两个表(TB1、TB2)中查询的结果同时插入表TB的C列中怎么办呢?比如
select e from tb1 group by e order by count (*) desc 和
select e from tb2 group by e order by count (*) desc
上面这两条命令的结果能不能同时插入表TB的C列中呢?表TB的C列为空。先谢谢!!
这个连接是原来的提问,没说完整。http://topic.csdn.net/u/20120202/16/76485795-1390-4a44-ad1d-e108bbf03993.html?1879215201
[解决办法]
go
if OBJECT_ID('a') is not null
drop table a
go
create table a(
id varchar(10),
name varchar(10)
)
go
insert a
select '1001','tracy' union all
select '1001','kobe' union all
select '1002','lily' union all
select '1003','tom' union all
select '1003','kate' union all
select '1001','nash'
go
if OBJECT_ID('b') is not null
drop table b
go
create table b(
id varchar(10),
name varchar(10)
)
go
insert b
select '1001','tracy' union all
select '1001','kobe' union all
select '1002','lily' union all
select '1003','tom' union all
select '1003','kate' union all
select '1001','nash'
go
if OBJECT_ID('c') is not null
drop table c
go
create table c(
name varchar(10)
)
go
insert c
select name from a group by name
union all
select name from b group by name
select *from c
/*
name
kate
kobe
lily
nash
tom
tracy
kate
kobe
lily
nash
tom
tracy
*/
[解决办法]
declare @tb1 table (e int,c varchar(1))insert into @tb1select 1,'a' union allselect 2,'a' union allselect 2,'b' union allselect 2,'c' union allselect 3,'s' union allselect 3,'a'declare @tb2 table (e int,c varchar(1))insert into @tb2select 1,'a' union allselect 1,'a' union allselect 2,'b' union allselect 2,'c' union allselect 2,'s' union allselect 3,'a'declare @tb table(a int ,b int,c varchar(1))--第一种情况是分成2步:insert into @tb (c)select e from @tb1 group by e order by count (*) desc insert into @tb (c)select e from @tb2 group by e order by count (*) descselect * from @tb/*a b c----------- ----------- ----NULL NULL 2NULL NULL 3NULL NULL 1NULL NULL 2NULL NULL 1NULL NULL 3*/--如果不想分为两步我来一步完成,假设再定义一个表变量declare @tbb table(a int ,b int,c varchar(1))insert into @tbb (c)select e from (select e,1 as tn,count (*) as ecount from @tb1 group by eunion allselect e,2,count (*) from @tb2 group by e)a order by tn,ecount desc --中间的1,2表示不同的表,后面的count (*)和1、2配合重新排序可以保持你要的结果select * from @tbb