如何查找用户在月份和年份获得的点数 [英] how to find number of points that user gets in month and year

问题描述

HI，

这是我的表，我可以得到这个结果。

每个用户都有积分，我必须按当前得分数显示用户积分月，今年



谢谢。



userId积分日期

1 5 8/25/2013

1 3 8/16/2013

1 2 8/01/2013

1 2 9/25 / 2013

1 1 9/08/2013

1 3 8/25/2012

1 4 8/25/2011

1 3 8/25/2012

1 5 9/27/2013

1 3 8/25/2011

1 1 8/25/2011

1 5 8/25/2012



结果



userid --TotalNumberofpointsinCurrrentMonth-- TotalNumberofpointsinCurrrentyear

1 ------- ------- 10 -------------- --------------- --------------- 18

解决方案

嗨。 ..



试试这个

;  WITH  ResultCTE  As （
 
  SELECT  UserId， NULL  monthPoints，SUM（Points）YearPoints  FROM  YourTableName 
  WHERE 年份（[日期]）=年（GETDATE（））
  GROUP   BY  UserId 
 
  UNION   ALL  
 
  SELECT  UserId，SUM（Points）monthPoints， NULL  YearPoints  FROM  YourTableName 
  WHERE 年（[日期]）=年（GETDATE（）） AND  MONTH（[日期]）=月（GETDATE（））
  GROUP   BY  UserId 
）
 
  SELECT  UserId，SUM（monthPoints）monthPoints，SUM（YearPoints ）yearPoints 
  FROM  ResultCTE  GROUP   BY  UserId 

谢谢

快乐编码.. :)

< blockquote>嗨会员，



请尝试这个查询Sql数据库

 选择 SUM（Person.points）  TotalNumberofpointsinCurrrentMonth  from  Person  where  MONTH（Person。 date ）= MONTH（GETDATE（）） group  按 userId 
 
 选择 SUM（Person.points） as  TotalNu mberofpointsinCurrrentyear 来自人其中年（人。日期）=年（GETDATE（）） group   by  userId 

这是另一个查询..

select（来自Person的SELECT SUM（Person.points），其中MONTH（Person.date）= MONTH（GETDATE（）））为TotalNumberofpointsinCurrrentMonth ，

SUM（Person.points）作为TotalNumberofpointsinCurrrentyear来自Person

group by userId



你可以添加到哪里指定年份外部查询的子句....

HI,
It is my table can i get result for this .
Each user have points,i have to show user points by number of points got in current month,current year

Thanks.

userId points date
1 5 8/25/2013
1 3 8/16/2013
1 2 8/01/2013
1 2 9/25/2013
1 1 9/08/2013
1 3 8/25/2012
1 4 8/25/2011
1 3 8/25/2012
1 5 9/27/2013
1 3 8/25/2011
1 1 8/25/2011
1 5 8/25/2012

result as

userid --TotalNumberofpointsinCurrrentMonth-- TotalNumberofpointsinCurrrentyear
1 ------- ------- 10 ----------------------------- --------------- 18

解决方案

Hi...

try this

;WITH ResultCTE As (

SELECT UserId,NULL monthPoints,SUM(Points) YearPoints  FROM YourTableName
WHERE YEAR([Date]) = YEAR(GETDATE())
GROUP BY UserId

UNION ALL 

SELECT UserId,SUM(Points) monthPoints,NULL YearPoints  FROM YourTableName
WHERE YEAR([Date]) = YEAR(GETDATE()) AND MONTH([Date]) = MONTH(GETDATE())
GROUP BY UserId
)

SELECT UserId,SUM(monthPoints) monthPoints,SUM(YearPoints) yearPoints 
FROM ResultCTE GROUP BY UserId

Thanks
Happy Coding.. :)

Hi Member,

Please try this query for Sql Database

select SUM(Person.points) as TotalNumberofpointsinCurrrentMonth from Person where MONTH(Person.date)= MONTH(GETDATE()) group by userId

select SUM(Person.points) as TotalNumberofpointsinCurrrentyear from Person where YEAR(Person.date)= YEAR(GETDATE()) group by userId

here is another query..
select (SELECT SUM(Person.points) from Person where MONTH(Person.date)= MONTH(GETDATE())) as TotalNumberofpointsinCurrrentMonth,
SUM(Person.points) as TotalNumberofpointsinCurrrentyear from Person
group by userId

you can add where clause for outer query for specified year ....

这篇关于如何查找用户在月份和年份获得的点数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！