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

[NocturneLLJ]

有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太渣.可有理解我意思的神吗...

[道玄希言]

楼上大神是不是忘记写排序部分了。。。

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

[Tiger_Zhao]

的确忘了，应该按 val 倒序

[NocturneLLJ]

引用 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

太有用了.万分感谢~~~

[shadowpj]

。。我用的是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

[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

[NocturneLLJ]

引用 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]

分组字段
日统计 CONVERT(varchar(10),cdate,120)
周统计 DATEPART(week,cdate)
月统计 CONVERT(varchar(7),cdate,120)

[NocturneLLJ]

引用 7 楼 Tiger_Zhao 的回复:

分组字段 日统计 CONVERT(varchar(10),cdate,120) 周统计 DATEPART(week,cdate) 月统计 CONVERT(varchar(7),cdate,120)

谢谢你了.捣鼓一早上.该要的查询结果都出来了.结果也匹配.谢谢啦~