select تو در تو

سلام

فرض کنید دوجدول زیر را داریم و داده های زیر را هم وارد کرده ایم

CREATE TABLE [dbo].[Buyer](
	[BuyerId] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL
)
CREATE TABLE [dbo].[Factor](
	[FactorId] [int] IDENTITY(1,1) NOT NULL,
	[BuyerId] [int] NOT NULL,
	[Date] [date] NOT NULL,
	[Price] [int] NULL)
INSERT INTO [dbo].[Buyer]([Name])VALUES(N'ابوالفضل');
INSERT INTO [dbo].[Buyer]([Name])VALUES(N'محمدمهدي');
INSERT INTO [dbo].[Buyer]([Name])VALUES(N'محمدصادق');
INSERT INTO [dbo].[Factor]([BuyerId],[Date],[Price])VALUES(1,'2017-12-20',100);
INSERT INTO [dbo].[Factor]([BuyerId],[Date],[Price])VALUES(1,'2017-12-20',250);
INSERT INTO [dbo].[Factor]([BuyerId],[Date],[Price])VALUES(1,'2017-12-20',300);
INSERT INTO [dbo].[Factor]([BuyerId],[Date],[Price])VALUES(1,'2017-12-18',200);
INSERT INTO [dbo].[Factor]([BuyerId],[Date],[Price])VALUES(1,'2017-12-19',300);
INSERT INTO [dbo].[Factor]([BuyerId],[Date],[Price])VALUES(1,'2017-12-19',350);
INSERT INTO [dbo].[Factor]([BuyerId],[Date],[Price])VALUES(2,'2017-12-20',50);
INSERT INTO [dbo].[Factor]([BuyerId],[Date],[Price])VALUES(2,'2017-12-20',70);
INSERT INTO [dbo].[Factor]([BuyerId],[Date],[Price])VALUES(2,'2017-12-20',400);
INSERT INTO [dbo].[Factor]([BuyerId],[Date],[Price])VALUES(2,'2017-12-18',100);
INSERT INTO [dbo].[Factor]([BuyerId],[Date],[Price])VALUES(2,'2017-12-19',150);
INSERT INTO [dbo].[Factor]([BuyerId],[Date],[Price])VALUES(2,'2017-12-19',250);
INSERT INTO [dbo].[Factor]([BuyerId],[Date],[Price])VALUES(3,'2017-12-20',10);
INSERT INTO [dbo].[Factor]([BuyerId],[Date],[Price])VALUES(3,'2017-12-20',700);
INSERT INTO [dbo].[Factor]([BuyerId],[Date],[Price])VALUES(3,'2017-12-20',400);
INSERT INTO [dbo].[Factor]([BuyerId],[Date],[Price])VALUES(3,'2017-12-18',200);
INSERT INTO [dbo].[Factor]([BuyerId],[Date],[Price])VALUES(3,'2017-12-19',300);
INSERT INTO [dbo].[Factor]([BuyerId],[Date],[Price])VALUES(3,'2017-12-19',350);
GO

حال براساس تاریخ مورد نظر و اینکه مجموع خرید بالای 600باشد را نمایش بدهد

از کد زیر استفاده میکنیم

select 
b.Name ,SUM(f.Price) as TotalPrice
from [dbo].[Factor] f
inner join [dbo].[Buyer] b on f.BuyerId=b.BuyerId
--where f.Date='2017-12-20'
where f.Date between '2017-12-19' and  '2017-12-20'
group by b.Name
having SUM(f.Price)>=600
order by SUM(f.Price) desc