سوال از پایگاه داده فروش فرش

[mahllaa]

سلام دوستان گرامی

یه پایگاه داده دارم به شکل زیر

http://upload7.ir/imgs/2014-11/44390611300752431988_thumb.png (http://upload7.ir/viewer.php?file=44390611300752431988.png)

من این جدول ها رو با یه سری داده پر کردم

حالا 2تا سوال هست که باید بهشون جواب بدم

اما بلد نیستم

امیدوارم شما کمک بفرمایید


سوال اول: دستوراتی باید بنویسم که کد مشتری رو از ورودی بگیره و مجموع مبلغ فرش های خریداری شده رو حساب کنه اینو تا یه جاهایی نوشتم اما درست کار نمیکنه


create proc de
@code int
as
set @code=1
select c_price from curpet
join buy
on buy.c_code=curpet.c_code
join sell
on sell.c_code=curpet.c_code
join customer
on sell.cu_code=customer.cu_code
where c_price is not null
group by customer.cu_code

print sum(buy_num*c_price)

exec de

سوال دوم: کد فروشنده ای رو از ورودی دریافت کنه اگر تاریخ فاکتور وی مربوط به 3 ماه گذشته بود تعداد فرش های خریداری شده از وی را نمایش دهد در غیر این صورت فیلد(t_type) را null کند.

اینو که بلد نبودم

پیشاپیش سپاس بابت کمکهای شما

[ashkufaraz]

چرا buy.buy_num توی select ننوشتی؟ منظورت از درست کار نمی کنه چیه؟خطا میده ؟ یا خروجی اشتباهی میده ؟خروجی چی میده؟
اینم برای سوال دومت امتحان کن ببینم جواب می ده؟

create proc de2
as
select buy.buy_num from seller
join buy
on buy.seller_code=seller.seller_code
join factor
on factor.f_ID=buy.f_ID
where factor.f_date< DATEPART(yyyy, DATEADD(m, -3, getdate()))
group by seller.seller_code

print sum(buy_num)

exec de

[mahllaa]

چرا buy.buy_num توی select ننوشتی؟ منظورت از درست کار نمی کنه چیه؟خطا میده ؟ یا خروجی اشتباهی میده ؟خروجی چی میده؟
اینم برای سوال دومت امتحان کن ببینم جواب می ده؟
سلام دوست گرامی سپاس بابت جوابتون

خب بله error میده کدی که من نوشتم،اما به احتمال زیاد اشتباه هم هست اخه باید کد مشتری رو با تک تک کدها مقایسه کنه و اگر فرشی که خریداری شده مربوط به اون مشتری هست تعدادش رو درقیمت ضرب کنه و باهم جمع کنه تا تعداد کل محاسبه بشه و در نهایت چاپ کنه

این خطای کدی که شما گذاشتین هست

Msg 128, Level 15, State 1, Procedure de2, Line 11
The name "buy_num" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

.

اما این که کدی رو از ورودی نمیگیره

برای سوال دوم هم من میخوام کد فروشنده رو بگیره از طریق یه متغیر ورودی و با تک تک فروشنده های موجود مقایسه کنه

میدونم که باید از if استفاده کنم اما نمیدونم چطوری !

[ashkufaraz]

اسکریپت یتابیس رو قرار بده تا حداقل روی دیتای واقعا کد بنویسیم
این جوری نمیشه
فهمید چی به چیه!

[mahllaa]

ممنونم،بفرمایید


USE [master]
GO
/****** Object: Database [Farsh] Script Date: 10/27/2014 20:21:41 ******/
CREATE DATABASE [Farsh] ON PRIMARY
( NAME = N'Farsh', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Farsh.mdf ' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'Farsh_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Farsh_log .ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [Farsh] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Farsh].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [Farsh] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [Farsh] SET ANSI_NULLS OFF
GO
ALTER DATABASE [Farsh] SET ANSI_PADDING OFF
GO
ALTER DATABASE [Farsh] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [Farsh] SET ARITHABORT OFF
GO
ALTER DATABASE [Farsh] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [Farsh] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [Farsh] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [Farsh] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [Farsh] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [Farsh] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [Farsh] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [Farsh] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [Farsh] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [Farsh] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [Farsh] SET DISABLE_BROKER
GO
ALTER DATABASE [Farsh] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [Farsh] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [Farsh] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [Farsh] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [Farsh] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [Farsh] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [Farsh] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [Farsh] SET READ_WRITE
GO
ALTER DATABASE [Farsh] SET RECOVERY FULL
GO
ALTER DATABASE [Farsh] SET MULTI_USER
GO
ALTER DATABASE [Farsh] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [Farsh] SET DB_CHAINING OFF
GO
EXEC sys.sp_db_vardecimal_storage_format N'Farsh', N'ON'
GO
USE [Farsh]
GO
/****** Object: Table [dbo].[factor] Script Date: 10/27/2014 20:21:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[factor](
[f_ID] [int] NOT NULL,
[f_date] [date] NULL,
[karmozd] [int] NULL,
[t_type] [varchar](20) NULL,
CONSTRAINT [PK_factor] PRIMARY KEY CLUSTERED
(
[f_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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[customer] Script Date: 10/27/2014 20:21:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[customer](
[cu_code] [int] NOT NULL,
[cu_name] [varchar](20) NULL,
[cu_addr] [varchar](30) NULL,
CONSTRAINT [PK_customer] PRIMARY KEY CLUSTERED
(
[cu_code] 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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[curpet] Script Date: 10/27/2014 20:21:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[curpet](
[c_code] [int] NOT NULL,
[c_color] [varchar](20) NULL,
[c_price] [int] NULL,
[c_size] [int] NULL,
[c_model] [varchar](20) NULL,
CONSTRAINT [PK_curpet] PRIMARY KEY CLUSTERED
(
[c_code] 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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[seller] Script Date: 10/27/2014 20:21:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[seller](
[seller_code] [int] NOT NULL,
[seller_name] [varchar](20) NULL,
[seller_addr] [varchar](30) NULL,
CONSTRAINT [PK_seller] PRIMARY KEY CLUSTERED
(
[seller_code] 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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[store] Script Date: 10/27/2014 20:21:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[store](
[c_code] [int] NULL,
[c_num] [int] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[sell] Script Date: 10/27/2014 20:21:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[sell](
[sell_ID] [int] NOT NULL,
[c_code] [int] NULL,
[cu_code] [int] NULL,
[f_ID] [int] NULL,
[sell_num] [int] NULL,
CONSTRAINT [PK_sell] PRIMARY KEY CLUSTERED
(
[sell_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
/****** Object: Table [dbo].[buy] Script Date: 10/27/2014 20:21:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[buy](
[buy_ID] [int] NOT NULL,
[f_ID] [int] NULL,
[c_code] [int] NULL,
[seller_code] [int] NULL,
[buy_num] [int] NULL,
CONSTRAINT [PK_buy] PRIMARY KEY CLUSTERED
(
[buy_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
/****** Object: ForeignKey [FK_store_curpet] Script Date: 10/27/2014 20:21:43 ******/
ALTER TABLE [dbo].[store] WITH CHECK ADD CONSTRAINT [FK_store_curpet] FOREIGN KEY([c_code])
REFERENCES [dbo].[curpet] ([c_code])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[store] CHECK CONSTRAINT [FK_store_curpet]
GO
/****** Object: ForeignKey [FK_sell_curpet] Script Date: 10/27/2014 20:21:43 ******/
ALTER TABLE [dbo].[sell] WITH CHECK ADD CONSTRAINT [FK_sell_curpet] FOREIGN KEY([c_code])
REFERENCES [dbo].[curpet] ([c_code])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[sell] CHECK CONSTRAINT [FK_sell_curpet]
GO
/****** Object: ForeignKey [FK_sell_customer] Script Date: 10/27/2014 20:21:43 ******/
ALTER TABLE [dbo].[sell] WITH CHECK ADD CONSTRAINT [FK_sell_customer] FOREIGN KEY([cu_code])
REFERENCES [dbo].[customer] ([cu_code])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[sell] CHECK CONSTRAINT [FK_sell_customer]
GO
/****** Object: ForeignKey [FK_sell_factor] Script Date: 10/27/2014 20:21:43 ******/
ALTER TABLE [dbo].[sell] WITH CHECK ADD CONSTRAINT [FK_sell_factor] FOREIGN KEY([f_ID])
REFERENCES [dbo].[factor] ([f_ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[sell] CHECK CONSTRAINT [FK_sell_factor]
GO
/****** Object: ForeignKey [FK_buy_curpet] Script Date: 10/27/2014 20:21:43 ******/
ALTER TABLE [dbo].[buy] WITH CHECK ADD CONSTRAINT [FK_buy_curpet] FOREIGN KEY([c_code])
REFERENCES [dbo].[curpet] ([c_code])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[buy] CHECK CONSTRAINT [FK_buy_curpet]
GO
/****** Object: ForeignKey [FK_buy_factor] Script Date: 10/27/2014 20:21:43 ******/
ALTER TABLE [dbo].[buy] WITH CHECK ADD CONSTRAINT [FK_buy_factor] FOREIGN KEY([f_ID])
REFERENCES [dbo].[factor] ([f_ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[buy] CHECK CONSTRAINT [FK_buy_factor]
GO
/****** Object: ForeignKey [FK_buy_seller] Script Date: 10/27/2014 20:21:43 ******/
ALTER TABLE [dbo].[buy] WITH CHECK ADD CONSTRAINT [FK_buy_seller] FOREIGN KEY([seller_code])
REFERENCES [dbo].[seller] ([seller_code])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[buy] CHECK CONSTRAINT [FK_buy_seller]
GO
--
insert into curpet values (1,'red',1000,6,'500s')
insert into curpet values (2,'green',2000,9,'600s')
insert into curpet values (3,'blue',3000,12,'700s')
--
insert into customer values (12,'ali','arak')
insert into customer values (13,'reza','arak')
insert into customer values (14,'hossein','arak')
--
insert into factor values (21,'12-1-2014',500,'cach')
insert into factor values (22,'12-2-2014',600,'peyment')
insert into factor values (23,'12-3-2014',700,'cach')
--
insert into seller values (31,'ahmad','arak')
insert into seller values (32,'nima','arak')
insert into seller values (33,'sina','arak')
--
insert into store values (1,100)
insert into store values (2,100)
insert into store values (3,100)
--
insert into sell values (41,1,12,21,10)
insert into sell values (42,2,13,22,20)
insert into sell values (43,3,14,23,30)
--
insert into buy values (51,21,1,31,61)
insert into buy values (52,22,2,32,62)
insert into buy values (53,23,3,33,63)

[ham3d1988]

سلام
سوال یک :

declare @CustomerCode int
set @CustomerCode=12


select a.cu_Code [کد مشترک],sum(IsNULL(a.price,0))[جمع فروش],sum(Karmozd) [جمع کارمزد] , count(*) [تعداد فروش]
from
(select *
,(select c_price from curpet c where c.c_code=s.c_code ) Price
,(select karmozd from Factor f where f.f_id=s.f_id ) Karmozd
from sell s


where cu_code=@CustomerCode
)a
group by a.cu_Code


سوال دو هم واضح نیست