sql查询多表结果作为新表并在新表中进行新的查询

很拗口的标题,仅仅作为自己备忘用!

主要用到将sql查询的结果作为一个新表, 在新表中再进行sql查询.使用了join语句.

DECLARE @time_begin datetime 
DECLARE @time_end datetime 
DECLARE @buytime_begin datetime 
DECLARE @buytime_end datetime 

SET  @time_begin='2016/2/1'      -- 可修改变量   (   @time_begin<=shopdetial时间区间< @time_end  
SET  @time_end='2016/3/1'        --可修改变量   (

SET  @buytime_begin='2015/12/1'   --可修改变量   (  @buytime_begin<=buydetial时间区间< @buytime_end  
SET  @buytime_end='2016/3/1'     --可修改变量   (



--SELECT @time_begin,@time_end


SELECT customer.cardno AS '会员号码',customer.tel AS '电话',Temp.t1 AS '区间累计物品',ISNULL(temp2.m1,0) AS '区间累计消费' 

FROM 

(SELECT cardno AS l1,sum(amount) AS t1 FROM shopdetial WHERE busydate >=@time_begin AND busydate <@time_end GROUP BY cardno ) Temp 
--(SELECT TOP 200 cardno AS l1,sum(amount) AS t1 FROM shopdetial WHERE busydate >=@time_begin AND busydate <@time_end GROUP BY cardno ORDER BY t1 desc) Temp 
LEFT JOIN

(SELECT  cardno AS l1,sum(money) AS m1 FROM buydetial WHERE busydate >=@buytime_begin  AND busydate <@buytime_end AND cardno<>'' AND seller_no<>'LIPIN2' GROUP BY cardno) temp2 

ON  Temp.l1=temp2.l1 

LEFT JOIN customer 

ON Temp.l1=customer.cardno 
 
ORDER BY t1 desc

 

基于互联网精神,在注明出处的前提下本站文章可自由转载!

本文链接:https://ranjuan.cn/sql查询多表结果作为新表并在新表中进行新的查询/

赞赏

微信赞赏支付宝赞赏

centos7-install-nginx-apache-php
idea-java-springboot-jar-war-package
asp导出数据库表为excel