还是给一个字段赋值的有关问题
日期：2014-05-18 浏览次数：20420 次
还是给一个字段赋值的问题
先谢谢大虾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
*/
------解决方案--------------------
先谢谢大虾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
*/
------解决方案--------------------
- SQL code
declare @tb1 table (e int,c varchar(1))
insert into @tb1
select 1,'a' union all
select 2,'a' union all
select 2,'b' union all
select 2,'c' union all
select 3,'s' union all
select 3,'a'
declare @tb2 table (e int,c varchar(1))
insert into @tb2
select 1,'a' union all
select 1,'a' union all
select 2,'b' union all
select 2,'c' union all
select 2,'s' union all
select 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 (*) desc
select * from @tb
/*
a b c
----------- ----------- ----
NULL NULL 2
NULL NULL 3
NULL NULL 1
NULL NULL 2
NULL NULL 1
NULL 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 e
union all
select e,2,count (*) from @tb2 group by e
)a
order by tn,ecount desc
--中间的1,2表示不同的表，后面的count (*)和1、2配合重新排序可以保持你要的结果
select * from @tbb
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
