//原来的ID数据
select ID,saleID,saleName from saleclient where [id] in
(select parentID from saleclient
where addtime<='2011-7-9' and parentID<>0 )
//当前的ID数据
select ID,saleID,saleName from saleclient where parentID in
(select parentID from saleclient
where addtime<='2011-7-9' and parentID<>0 )我有一个表,表中有个代表原来ID的parentID字段,它表示这条数据变换前的ID。
第一条SQL根据[id] in
(select parentID from saleclient
where addtime<='2011-7-9' and parentID<>0 )把原来的销售员ID和销售员名称查询出来
第二条SQL根据parentID in
(select parentID from saleclient
where addtime<='2011-7-9' and parentID<>0 )把现在的销售员ID和销售员名称查询出来
现在我想把这两条语句合并成“并列”表,请问SQL语句怎么写?第一条:第二条:合并成一个“并列”表:
;with a as
(
--原来的ID数据
select ID,saleID,saleName,rid=row_number() over (order by getdate())
from saleclient where [id] in
(select parentID from saleclient
where addtime<='2011-7-9' and parentID<>0 )
),b as
(
--当前的ID数据
select ID,saleID,saleName,rid=row_number() over (order by getdate())
from saleclient where parentID in
(select parentID from saleclient
where addtime<='2011-7-9' and parentID<>0 )
)select *
from a,b
where a.rid = b.rid
(select row_number() over(order by getdate())no,ID,saleID,saleName from saleclient where [id] in
(select parentID from saleclient
where addtime<='2011-7-9' and parentID<>0 ))
,cte2 as
(select row_number() over(order by getdate())no,ID,saleID,saleName from saleclient where parentID in
(select parentID from saleclient
where addtime<='2011-7-9' and parentID<>0 ))select * from cte1 a,cte2 b where a.no=b.no
*
from
(
select ,id0=row_number()over(order by getdate()),ID,saleID,saleName from saleclient where [id] in
(select parentID from saleclient
where addtime<='2011-7-9' and parentID<>0 )
) a
left join
(
select id0=row_number()over(order by getdate()),ID,saleID,saleName from saleclient where parentID in
(select parentID from saleclient
where addtime<='2011-7-9' and parentID<>0 )
)
b
on
a.id0=b.id0