-- User Betting table History Table
Create table t_Userinfo_his (
Rid int identity,
Userid int not null, -- User ID
Nick varchar (50) not null, -- user nickname
Termid int not null, -- competition ID
Nums int not null, -- bet number
Instone bigint not null, -- input amount
Outstone bigint not null, -- return amount
Addtime datetime not null, -- bet time
)
Go
-- Function: Count the most profitable users on a daily basis
-- Author: Tester
-- Creation date:
Create procedure p_daystar
@ Date datetime -- start from which day
As
Set nocount on
Set transaction isolation level read uncommitted
Set xact_abort on
-- Create a table variable to store the user ID, total profit of the user on the current day, and date
Declare @ tb table (userid int, outstone bigint, addtime char (10 ))
Insert @ tb select userid, sum (outstone) as outstone, convert (char (10), addtime, 20) as addtime from t_userinfo_his with (nolock)
Where convert (char (10), addtime, 20)> = @ date
Group by userid, convert (char (10), addtime, 20)
-- Select * from @ tb
Select addtime, max (outstone) as outstone, userid = (select userid from @ tb where addtime = a. addtime and outstone = max (a. outstone) from @ tb as
Group by addtime
Go