跪求 两个select 语句查询结果合并

[qq_18556097]

SQL1:
select convert(varchar(100),[saledate],112) ,COUNT(distinct saleid), SUM(subsale), SUM(subsale)/COUNT(distinct saleid)
FROM sd201601
where saledate between '20160101 00:00:00' and '20160109 23:59:59'
group by convert(varchar(100),[saledate],112) order by convert(varchar(100),[saledate],112) DESC

SQL2:
select SUM(subsale), COUNT(distinct saleid)
FROM sd201601
where (saledate between '20160101 00:00:00' and '20160109 23:59:59') and (warehouseid between '010102' and '010103')
group by convert(varchar(100),[saledate],112) order by convert(varchar(100),[saledate],112) DESC

两个语句的数据来源是同一个表

最终结果要这样的效果

[qq_18556097]

SQL1: select convert(varchar(100),[saledate],112) as 日期 ,COUNT(distinct saleid) as 客流量, SUM(subsale) as 商场销额, SUM(subsale)/COUNT(distinct saleid) as 客单 FROM [historydb].[dbo].[sd201601] where saledate between '20160101 00:00:00' and '20160109 23:59:59' group by convert(varchar(100),[saledate],112) order by convert(varchar(100),[saledate],112) DESC SQL2: select SUM(subsale) as 超市销额, COUNT(distinct saleid) as 客流量 FROM [historydb].[dbo].[sd201601] where (saledate between '20160101 00:00:00' and '20160109 23:59:59') and (warehouseid between '010102' and '010103') group by convert(varchar(100),[saledate],112) order by convert(varchar(100),[saledate],112) DESC

[中国风]

  select convert(varchar(100),[saledate],112) ,COUNT(distinct saleid), SUM(subsale), SUM(subsale)/COUNT(distinct saleid)
  ,sum(case when warehouseid between '010102' and '010103' then subsale else 0 end)
  ,count(distinct case when warehouseid between '010102' and '010103'  then saleid end) 
  FROM sd201601
  where saledate between '20160101 00:00:00' and '20160109 23:59:59' 
  group by convert(varchar(100),[saledate],112)  order by convert(varchar(100),[saledate],112) DESC

[qq_18556097]

怎么排序 如果在每个select 后面加一个ORDER BY 就出现语法错误

[qq_18556097]

谢谢!版主一出手问题就没有.谢谢!

[qq_18556097]

select convert(varchar(100),[saledate],112) ,COUNT(distinct saleid), SUM(subsale), SUM(subsale)/COUNT(distinct saleid) ,sum(case when warehouseid between '010102' and '010103' then subsale else 0 end) ,count(distinct case when warehouseid between '010102' and '010103' then saleid end) FROM sd201602 where saledate between '20160101 00:00:00' and '20160109 23:59:59' group by convert(varchar(100),[saledate],112) union select convert(varchar(100),[saledate],112) ,COUNT(distinct saleid), SUM(subsale), SUM(subsale)/COUNT(distinct saleid) ,sum(case when warehouseid between '010102' and '010103' then subsale else 0 end) ,count(distinct case when warehouseid between '010102' and '010103' then saleid end) FROM sd201602 where saledate between '20160101 00:00:00' and '20160109 23:59:59' group by convert(varchar(100),[saledate],112)

[卖水果的net]

两个语句的数据来源是同一个表 将两个语句合并成一个，不可以吗？

[qq_18556097]

不同个表格 每月都会生成一张表格 例如2016 年1月份 表名sd201601 2016年2月份 表名sd201602

[qq_18556097]

上面的两个SQL语句应该是 select convert(varchar(100),[saledate],112) ,COUNT(distinct saleid), SUM(subsale), SUM(subsale)/COUNT(distinct saleid) ,sum(case when warehouseid between '010102' and '010103' then subsale else 0 end) ,count(distinct case when warehouseid between '010102' and '010103' then saleid end) FROM sd201601 where saledate between '20160101 00:00:00' and '20160109 23:59:59' group by convert(varchar(100),[saledate],112) union select convert(varchar(100),[saledate],112) ,COUNT(distinct saleid), SUM(subsale), SUM(subsale)/COUNT(distinct saleid) ,sum(case when warehouseid between '010102' and '010103' then subsale else 0 end) ,count(distinct case when warehouseid between '010102' and '010103' then saleid end) FROM sd201602 where saledate between '20160201 00:00:00' and '20160209 23:59:59' group by convert(varchar(100),[saledate],112)

[qq_18556097]

另外能不能打这个表里的 表名salecom201605 计算出来的毛利加到上面作为一个列


yyyymmdd 是日期
warehouseid 是库位 comtype 是类别 上面表是相同的
表sd201605.warehouseid =表 salecom201605.warehoseid
表sd201605.comtype = 表salecom201605.comtype

要求同一天的计算