请教合并两个表不同的数据，并进行数据统一
1)、 合并两个表不同的数据到新表
select [registTime] as 注册时间
,[telNo] as 注册人数
,[registType] as 注册类型
into[临时表3]
from [T_onLine]
where([registTime] is not null and [registTime]<>'')
and[registTime] between '2011-11-22 23:59:59.999'and '2011-11-24 00:00:00.000'
and[registType] not in('9994','9995','9998')
union
select [registTime] as 注册时间
,[telNo] as 注册人数
,[registType] as 注册类型
from [T_unRegistLog]
where([registTime] is not null and [registTime]<>'')
and[registTime] between '2011-11-22 23:59:59.999'and '2011-11-24 00:00:00.000'
and[registType] not in('9994','9995','9998')
and [telNo] not in (select [telNo] from [T_onLine])
2）、统计数据
select convert(varchar(10),[注册时间],120) as [注册时间]
,count(distinct [注册人数]) as [注册人数]
,count(distinct [注册类型]) as [注册类型]
from [临时表3]
group by convert(varchar(10),[注册时间] ,120)
我想请教哈，能否合成一条不用建新表就可以统计，是否能优化
[解决办法]
SELECT CONVERT(VARCHAR(10), [注册时间], 120) AS [注册时间], COUNT(DISTINCT [注册人数]) AS [注册人数], COUNT(DISTINCT [注册类型]) AS [注册类型]FROM ( SELECT [registTime] AS 注册时间, [telNo] AS 注册人数, [registType] AS 注册类型 FROM [T_onLine] WHERE ( [registTime] IS NOT NULL AND [registTime] <> '' --如果是registTime是DateTime类型的,这句多余,并且会影响性能 ) AND [registTime] BETWEEN '2011-11-22 23:59:59.999' --考虑registTime字段的类型 AND '2011-11-24 00:00:00.000' AND [registType] NOT IN ('9994', '9995', '9998') UNION --如果2个union的结果集肯定不会有重复的值,就用union all SELECT [registTime] AS 注册时间, [telNo] AS 注册人数, [registType] AS 注册类型 FROM [T_unRegistLog] WHERE ( [registTime] IS NOT NULL AND [registTime] <> '' ) AND [registTime] BETWEEN '2011-11-22 23:59:59.999' --registTime建立索引 AND '2011-11-24 00:00:00.000' AND [registType] NOT IN ('9994', '9995', '9998') AND [telNo] NOT IN (SELECT --telNo建立索引 [telNo] FROM [T_onLine]) ) TGROUP BY CONVERT(VARCHAR(10), [注册时间], 120)
[解决办法]
嵌套即可。
select convert(varchar(10),[注册时间],120) as [注册时间]
,count(distinct [注册人数]) as [注册人数]
,count(distinct [注册类型]) as [注册类型]
from (select [registTime] as 注册时间
,[telNo] as 注册人数
,[registType] as 注册类型
from [T_onLine]
where([registTime] is not null and [registTime]<>'')
and[registTime] between '2011-11-22 23:59:59.999'and '2011-11-24 00:00:00.000'
and[registType] not in('9994','9995','9998')
union
select [registTime] as 注册时间
,[telNo] as 注册人数
,[registType] as 注册类型
from [T_unRegistLog]
where([registTime] is not null and [registTime]<>'')
and[registTime] between '2011-11-22 23:59:59.999'and '2011-11-24 00:00:00.000'
and[registType] not in('9994','9995','9998')
and [telNo] not in (select [telNo] from [T_onLine])) [临时表3]
group by convert(varchar(10),[注册时间] ,120)
[解决办法]
select convert(varchar(10),[注册时间],120) as [注册时间] ,count(distinct [注册人数]) as [注册人数] ,count(distinct [注册类型]) as [注册类型]from (select [registTime] as 注册时间 ,[telNo] as 注册人数 ,[registType] as 注册类型 from [T_onLine]where([registTime] is not null and [registTime]<>'') and[registTime] between '2011-11-22 23:59:59.999'and '2011-11-24 00:00:00.000' and[registType] not in('9994','9995','9998')union all select [registTime] as 注册时间 ,[telNo] as 注册人数 ,[registType] as 注册类型from [T_unRegistLog]where([registTime] is not null and [registTime]<>'') and[registTime] between '2011-11-22 23:59:59.999'and '2011-11-24 00:00:00.000' and[registType] not in('9994','9995','9998') and [telNo] not in (select [telNo] from [T_onLine])) [临时表3]group by convert(varchar(10),[注册时间] ,120)