求教一个sql 语句拼接问题

我说我行就是行 2014-11-29 12:59:49



第一条语句

 select ISNULL(sum(FaceValue),0) AS faceSum ,GetPeople from [T_DaiLiLog] 

 where datediff(day,[CreateTime],getdate())=0 and Type=0 group by GetPeople

 第二条语句

  select ISNULL(sum(FaceValue),0) AS userSum ,GetPeople  from [T_DaiLiLog] 

 where datediff(day,[CreateTime],getdate())=0 and Type=1 group by GetPeople





这两条sql语句查询出来的结果为（N条数据）



第一条结果

faceSum      GetPeople

200              李四

250              张三

...

第二条结果

userSum      GetPeople

300	               李四

500         	张三

....

我现在想要的结果为

faceSum           userSum           GetPeople

200	                   300	              李四

250                    500         	       张三

...



或者查询出来的是这样

第一条结果

faceSum      GetPeople

200              李四

250              张三

第二条结果

userSum      GetPeople

300	               李四

我现在想要的结果为

faceSum           userSum           GetPeople

200	                   300	              李四

250                    0        	       张三

...

求教各位大神，写了半天没写出来，，

...全文

329 5 打赏收藏转发到动态举报

写回复

用AI写文章

5 条回复

切换为时间正序

请发表友善的回复…

发表回复

沉默又寡言 2014-12-01

打赏
举报

select faceSum,userSum,GetPeople from ( select ISNULL(sum(FaceValue),0) AS faceSum ,GetPeople from [T_DaiLiLog] where datediff(day,[CreateTime],getdate())=0 and Type=0 group by GetPeople union all select ISNULL(sum(FaceValue),0) AS userSum ,GetPeople from [T_DaiLiLog] where datediff(day,[CreateTime],getdate())=0 and Type=1 group by GetPeople )t

Sasayau 2014-12-01

打赏
举报

UNION ALL 参考：http://www.phpstudy.net/e/sql/sql_union.html

reenjie 2014-12-01

打赏
举报

試一下下面的sql。


 select sum(case when [Type]=0 then FaceValue else 0 end) AS faceSum,
        sum(case when [Type]=1 then FaceValue else 0 end) AS userSum, 
        GetPeople  from [T_DaiLiLog] 
 where datediff(day,[CreateTime],getdate())=0 group by GetPeople

还在加载中灬 2014-12-01

打赏
举报

在你的结果上全连接就出来了

SELECT T1.faceSum,T2.userSum,ISNULL(T1.GetPeople,T2.GetPeople)AS GetPeople
FROM(
	select ISNULL(sum(FaceValue),0) AS faceSum ,GetPeople from [T_DaiLiLog] 
	where datediff(day,[CreateTime],getdate())=0 and Type=0 group by GetPeople
)T1 FULL JOIN(
	select ISNULL(sum(FaceValue),0) AS userSum ,GetPeople  from [T_DaiLiLog] 
	where datediff(day,[CreateTime],getdate())=0 and Type=1 group by GetPeople
)T2 ON T1.GetPeople=T2.GetPeople

但是我觉得，应该可以直接行转列，因为效果是一样的，而且更简洁如2楼，调整一下查询条件，查询时最好不要把列带入计算

select sum(case when [Type]=0 then FaceValue else 0 end) AS faceSum,
        sum(case when [Type]=1 then FaceValue else 0 end) AS userSum, 
        GetPeople  from [T_DaiLiLog] 
 where [CreateTime]>=CONVERT(VARCHAR(10),GETDATE(),120) group by GetPeople

Neo_whl 2014-11-29

打赏
举报


--试试这样？
with t1(faceSum,GetPeople)
as
(
 select ISNULL(sum(FaceValue),0) AS faceSum ,GetPeople from [T_DaiLiLog] 
 where datediff(day,[CreateTime],getdate())=0 and Type=0 group by GetPeople
)
,
t2(userSum,GetPeople)
as
(
  select ISNULL(sum(FaceValue),0) AS userSum ,GetPeople  from [T_DaiLiLog] 
 where datediff(day,[CreateTime],getdate())=0 and Type=1 group by GetPeople
)
select faceSum,userSum,t1.GetPeople from t1 join t2 on t1.GetPeople=t2.GetPeople