select identity(int,1,1) as NID,OID,TITLEX,ABOUTX,TT into #T from
(
select IDnews as oID,Tnews as ,Cnews as ABOUTX,'a' as TT from news
union ALL
select IDorder as OID,Torder as TITLEX,Corder as ABOUTX,'b' as TT from order
) a
select * from #T 就可以了
(
select IDnews as oID,Tnews as ,Cnews as ABOUTX,'a' as TT from news
union ALL
select IDorder as OID,Torder as TITLEX,Corder as ABOUTX,'b' as TT from order
) a
select * from #T 就可以了
identity(int,1,1) as NID,a.*
into #t
from
(select OID=IDnews ,TITLEX = Tnews ,ABOUTX = Cnews ,TT = 'a' from news where Tnews like '%西瓜%'
union all
select OID=IDorder,TITLEX = Torder,ABOUTX = Corder,TT = 'b' from order where Torder like '%西瓜%') a
order by
a.TT,a.OIDselect * from #t order by NID
select identity(int,1,1) as NID,OID,TITLEX,ABOUTX,TT into #T from
(
select IDnews as oID,Tnews as TITLEX,Cnews as ABOUTX,'a' as TT from news
union ALL
select IDorder as OID,Torder as TITLEX,Corder as ABOUTX,'b' as TT from order
) a
select * from #T 就可以了
select identity(int,1,1) as NID,OID,TITLEX,ABOUTX,TT from
(
select IDnews as oID,Tnews as ,Cnews as ABOUTX,'a' as TT from news
union ALL
select IDorder as OID,Torder as TITLEX,Corder as ABOUTX,'b' as TT from order
) a
INTO #t FROM news
INSERT INTO #t SELECT identity(int,1,1) as NID, IDorder AS OID, Torder as TITLEX,Corder as ABOUTX, TT=b FROM order
SELECT * FROM #t
insert @news
values(1,'大西瓜','饿饭大师傅但是')
insert @news
values(2,'小西瓜','饿饭大师傅的撒反对')
select * from @newsdeclare @order table(IDorder int,Torder varchar(20),Corder varchar(50))
insert @order
values(1,'老西瓜','大撒反对撒法')
insert @order
values(2,'嫩西瓜','土豆熟了')
select * from @order--NID=identity(int,1,1),
select NID=identity(int,1,1),b.* into #t
from (
select IDnews as OID,Tnews as TITLEX,Cnews as ABOUTX,'a' as TT from @news
union
select IDorder as OID,Torder as TITLEX,Corder as ABOUTX,'b' as TT from @order
) bselect * from #t
drop table #t
declare @news table(IDnews int,Tnews varchar(20),Cnews varchar(50))
insert @news
values(1,'大西瓜','饿饭大师傅但是')
insert @news
values(2,'小西瓜','饿饭大师傅的撒反对')
select * from @newsdeclare @order table(IDorder int,Torder varchar(20),Corder varchar(50))
insert @order
values(1,'老西瓜','大撒反对撒法')
insert @order
values(2,'嫩西瓜','土豆熟了')
select * from @order--NID=identity(int,1,1),
select NID=identity(int,1,1),b.* into #t
from (
select IDnews as OID,Tnews as TITLEX,Cnews as ABOUTX,'a' as TT from @news
union all
select IDorder as OID,Torder as TITLEX,Corder as ABOUTX,'b' as TT from @order
) bselect * from #t
drop table #t
Create table news (IDnews int identity(1,1),Tnews varchar(10),Cnews varchar(20))
insert into news select '大西瓜','饿饭大师傅但是'
union all select '小西瓜','饿饭大师傅的撒反对'Create table [order] (IDorder int identity(1,1),Torder varchar(10),Corder varchar(20))
insert into [order] select '老西瓜','大撒反对撒法'
union all select '嫩西瓜','土豆熟了'--查询
select identity(int,1,1) as NID,* into #T from
(
select
OID=IDnews,TITLEX=Tnews,ABOUTX=Cnews,TT=TT
from
(
select IDnews ,Tnews ,Cnews ,'a' as TT from news
union all
select IDorder,Torder,Corder,'b' as TT from [order]
)
A
)
B--结果
NID OID TITLEX ABOUTX TT
----------- ----------- ---------- -------------------- ----
1 1 大西瓜 饿饭大师傅但是 a
2 2 小西瓜 饿饭大师傅的撒反对 a
3 1 老西瓜 大撒反对撒法 b
4 2 嫩西瓜 土豆熟了 b(所影响的行数为 4 行)--删除环境
drop table news
drop table [order]
drop table #T
--我的优化下!
select identity(int,1,1) as NID,OID=IDnews,TITLEX=Tnews,ABOUTX=Cnews,TT=TT
into #T
from
(
select IDnews ,Tnews ,Cnews ,'a' as TT from news
union all
select IDorder,Torder,Corder,'b' as TT from [order]
)
A