نقل قول: کمک در طراحی یه سیستم آنلاین قرعه کشی افراد از داخل بانک اطلاعاتی در Asp.net
یک مثال میزنم خودتون مطابق دیتابیس خودتون کوئری را بنویسید
جداول زیر را در نظر بگیرید
CREATE TABLE [dbo].[Reshteha](
[ReshteId] [int] IDENTITY(1,1) NOT NULL,
[ReshteName] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Reshteha] PRIMARY KEY CLUSTERED
(
[ReshteId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[People](
[id] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[ReshteId] [int] NOT NULL,
CONSTRAINT [PK_People] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[EarnedScores](
[id] [int] IDENTITY(1,1) NOT NULL,
[PersonId] [int] NOT NULL,
[score] [int] NOT NULL,
[RegDateTime] [datetime2](7) NULL,
[fee] [decimal](18, 0) NULL,
CONSTRAINT [PK_EarnedScores] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
کوئری زیر با تعییر عدد در top تعداد مورد نیاز و همچنین در having People.ReshteId=1 میتونید تعیین کنید در چه رشته ای قرعه کشی بشه
select
top 2
People.id as PersonId,
People.FirstName,
People.LastName,
People.ReshteId,
ISNULL(SUM(EarnedScores.score), 0) as TotalScore,
ISNULL(SUM(EarnedScores.fee), 0) as TotalEarned
from People left join EarnedScores on People.id = EarnedScores.PersonId
group by People.id, People.FirstName, People.LastName, People.ReshteId
having People.ReshteId=1
order by TotalEarned, TotalScore, NEWID()
