SQL渣渣来求算法.一个查询排行.

NocturneLLJ 2015-10-26 11:14:47
有t1和t2两表.表结构都一样.字段userid和val都是int型.
每个userid在两表中都有多条记录或者无记录.
现在要做个排行.
userid在t1中的val的总和乘以0.5加上userid在t2中的val的总和乘以0.4..
倒序排列.
大概意思是SUM(t1.val)*0.5 + SUM(t2.val)*0.4 group by userid

SQL太渣.可有理解我意思的神吗...
...全文
208 8 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
NocturneLLJ 2015-11-06
  • 打赏
  • 举报
回复
引用 7 楼 Tiger_Zhao 的回复:
分组字段 日统计 CONVERT(varchar(10),cdate,120) 周统计 DATEPART(week,cdate) 月统计 CONVERT(varchar(7),cdate,120)
谢谢你了.捣鼓一早上.该要的查询结果都出来了.结果也匹配.谢谢啦~
Tiger_Zhao 2015-11-06
  • 打赏
  • 举报
回复
分组字段
日统计 CONVERT(varchar(10),cdate,120)
周统计 DATEPART(week,cdate)
月统计 CONVERT(varchar(7),cdate,120)
NocturneLLJ 2015-11-05
  • 打赏
  • 举报
回复
引用 1 楼 Tiger_Zhao 的回复:
    SELECT ISNULL(a1.userid,a2.userid) userid,
           ISNULL(a1.val,0.0)*0.5+ISNULL(a2.val,0.0)*0.4 val
      FROM (SELECT userid, SUM(val*1.0) val FROM t1 GROUP BY userid) a1
 FULL JOIN (SELECT userid, SUM(val*1.0) val FROM t2 GROUP BY userid) a2
        ON a1.userid = a2.userid
大神.再追问一下.如果两个表都加入一列cdate, datetime类型,那么怎么统计? 我现在要做日统计,周统计和月统计...突然不知道怎么写了...
Tiger_Zhao 2015-10-26
  • 打赏
  • 举报
回复
    SELECT ISNULL(a1.userid,a2.userid) userid,
ISNULL(a1.val,0.0)*0.5+ISNULL(a2.val,0.0)*0.4 val
FROM (SELECT userid, SUM(val*1.0) val FROM t1 GROUP BY userid) a1
FULL JOIN (SELECT userid, SUM(val*1.0) val FROM t2 GROUP BY userid) a2
ON a1.userid = a2.userid
shadowpj 2015-10-26
  • 打赏
  • 举报
回复
。。我用的是SQL2000 select userid,isnull(sum(Val*cs),0) as val from (select *,0.5 as cs from t1 union all select *,0.4 as cs from t2) tmptable group by userid order by val desc
NocturneLLJ 2015-10-26
  • 打赏
  • 举报
回复
引用 1 楼 Tiger_Zhao 的回复:
    SELECT ISNULL(a1.userid,a2.userid) userid,
           ISNULL(a1.val,0.0)*0.5+ISNULL(a2.val,0.0)*0.4 val
      FROM (SELECT userid, SUM(val*1.0) val FROM t1 GROUP BY userid) a1
 FULL JOIN (SELECT userid, SUM(val*1.0) val FROM t2 GROUP BY userid) a2
        ON a1.userid = a2.userid
太有用了.万分感谢~~~
Tiger_Zhao 2015-10-26
  • 打赏
  • 举报
回复
的确忘了,应该按 val 倒序
道玄希言 2015-10-26
  • 打赏
  • 举报
回复
楼上大神是不是忘记写排序部分了。。。

SELECT ISNULL(a1.val,0.0)*0.5+ISNULL(a2.val,0.0)*0.4 val, 
        ISNULL(a1.userid, a2.userid) userid          
  FROM 
    (SELECT userid, SUM(val*1.0) val FROM t1 GROUP BY userid) a1
  FULL JOIN 
    (SELECT userid, SUM(val*1.0) val FROM t2 GROUP BY userid) a2
  ON a1.userid = a2.userid
order by 1 desc

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧