select * from table1
union
select * from table2
union
select * from table3
union
select * from table2
union
select * from table3
解决方案 »
- SqlServer 合并文件组问题!
- 修改数据库属性值
- 根据输入的时间段与客户ID号,求一储存过程算出租用天数与合计数量,并包括上月份物品结余使用天数
- 一个简单问题
- 请帮我看看这段存储过程怎么个调用
- UPDATE触发器时获取不到唯一标识?
- isnull在ACCESS用法
- 放分求一存储过程
- 怪事!怎么有的机子可以查到,有的不行?select * from employees where birthdate like '%1963%'
- sql server 7标准版安装到winnt上后,在局域网中另一台微机连接时出现只允许一台连入的错误
- 请问存储过程太长是否会影响数据库处理速度
- 怎么用like找出以0开头的由任意个0组成的字符串?
select distinct d.* from (select * from a
union
select * from b
union
select * from c
) d
union
select * from 表2
union
select * from 表2
如果是横向显示,才是用full join
(select * from table1
union
select * from table2
union
select * from table3)f
CustomerID char (18) COLLATE Chinese_PRC_CI_AS NOT NULL ,
UserstatusName char (26) COLLATE Chinese_PRC_CI_AS NULL ,
Balance float default 0.00,
Arrearage float default 0.00 ,
setsrvName Char(10) '
)
说明:
1)str1是一个变量;
表MobileUserstatus1
131 申请停 0.0 0.0 5100
136 正常 59.0 7.0 5100
132 申请停 0.0 0.0 5100
MobileUserstatus2
131 申请停 0.0 0.0 5100
135 正常 59.0 7.0 5100
132 申请停 0.0 0.0 5100
MobileUserstatus3
138 申请停 0.0 0.0 5100
136 正常 59.0 7.0 5100
132 申请停 0.0 0.0 5100
得到的结果
131 申请停 0.0 0.0 5100
136 正常 59.0 7.0 5100
132 申请停 0.0 0.0 5100
135 正常 59.0 7.0 5100
138 申请停 0.0 0.0 5100
select * from MobileUserstatus1
union
select * from MobileUserstatus2
union
select * from MobileUserstatus3
,UserstatusName=isnull(a.UserstatusName,isnull(b.UserstatusName,c.UserstatusName))
,Balance=isnull(a.Balance,isnull(b.Balance,c.Balance))
,Arrearage=isnull(a.Arrearage,isnull(b.Arrearage,c.Arrearage))
,setsrvName=isnull(a.setsrvName,isnull(b.setsrvName,c.setsrvName))
from MobileUserstatus1 a
full join MobileUserstatus2 b on a.CustomerID=b.CustomerID
full join MobileUserstatus3 c on a.CustomerID=c.CustomerID
,UserstatusName=isnull(a.UserstatusName,isnull(b.UserstatusName,c.UserstatusName))
,Balance=isnull(a.Balance,isnull(b.Balance,c.Balance))
,Arrearage=isnull(a.Arrearage,isnull(b.Arrearage,c.Arrearage))
,setsrvName=isnull(a.setsrvName,isnull(b.setsrvName,c.setsrvName))
from MobileUserstatus1 a
full join MobileUserstatus2 b on a.CustomerID=b.CustomerID
full join MobileUserstatus3 c on a.CustomerID=c.CustomerID
customerID setsrvName Balance arearage status1 status2 status3
131 5100 0.0 0.0 申请停 申请停
136 5100 59.0 7.0 申请停 正常 正常
,UserstatusName=isnull(a.UserstatusName,isnull(b.UserstatusName,c.UserstatusName))
,Balance=isnull(a.Balance,isnull(b.Balance,c.Balance))
,Arrearage=isnull(a.Arrearage,isnull(b.Arrearage,c.Arrearage))
,setsrvName=isnull(a.setsrvName,isnull(b.setsrvName,c.setsrvName))
,status1=isnull(a.setsrvName,'')
,status2=isnull(b.setsrvName,'')
,status3=isnull(c.setsrvName,'')
from MobileUserstatus1 a
full join MobileUserstatus2 b on a.CustomerID=b.CustomerID
full join MobileUserstatus3 c on a.CustomerID=c.CustomerID