یک مثال میزنم خودتون مطابق دیتابیس خودتون کوئری را بنویسید
جداول زیر را در نظر بگیرید
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()
کمک در طراحی یه سیستم آنلاین قرعه کشی افراد از داخل بانک اطلاعاتی در Asp.net
پاسخ با نقل قول
نوشته شده توسط Tekyegah
