m_omrani
سه شنبه 12 بهمن 1389, 23:50 عصر
موضوع جالبیه. برای کمی خستگی در کردن هم خوبه.
من یه دیتابیس نمونه برای این جداول درست کردم و بعد از تعریف رکوردهای پایه ای اولیه (نظیر شرکت ها، محصولات و مشتریان) یه اسکریپت برای درج تعداد زیادی سفارش به طور تصادفی نوشتم.
اسکریپت ایجاد دیتابیس آزمایشی
use master;
go
IF DB_ID (N'mytest') IS NOT NULL
drop database mytest;
GO
create database mytest;
go
create table mytest.dbo.Company
(
id int identity(1,1) not null,
title varchar(50) not null,
city varchar(50) null,
street varchar(50) null,
post varchar(200) null,
postcode varchar(100) null,
constraint Company_PK primary key clustered(id asc)
)
go
create table mytest.dbo.Product
(
id int identity(1,1) not null,
title varchar(50) not null,
company_id int not null,
buyprice real,
sellprice real,
constraint Product_PK primary key clustered(id asc),
constraint FK_Product_Company foreign key(company_id) references mytest.dbo.Company(id)
)
go
create table mytest.dbo.Customer
(
id int identity(1,1) not null,
code varchar(20) null,
firstname varchar(50) null,
lastname varchar(50) null,
constraint Costumer_PK primary key clustered(id asc)
)
go
create table mytest.dbo.invoice
(
id int identity(1,1) not null,
customer_id int not null,
code varchar(10) not null,
[date] datetime default getdate(),
constraint invoice_PK primary key clustered(id asc),
constraint FK_invoice_Customer foreign key(customer_id) references mytest.dbo.Customer(id)
)
go
create table mytest.dbo.invoicedetail
(
id int identity(1,1) not null,
invoice_id int not null,
product_id int not null,
quantity int,
constraint invoicedetail_PK primary key clustered(id asc),
constraint FK_invoicedetail_invoice foreign key(invoice_id) references mytest.dbo.invoice(id),
constraint FK_invoicedetail_Product foreign key(product_id) references mytest.dbo.Product(id)
)
create table mytest.dbo.Phonetype
(
id int identity(1,1) not null,
title varchar(50) not null,
constraint Phonetype_PK primary key clustered(id asc)
)
go
create table mytest.dbo.Phone
(
id int identity(1,1) not null,
Phonetype_id int not null,
company_id int not null,
number varchar(15) not null,
constraint Phone_PK primary key clustered(id asc),
constraint FK_Phone_Phonetype foreign key(Phonetype_id) references mytest.dbo.Phonetype(id),
constraint FK_Phone_Company foreign key(company_id) references mytest.dbo.Company(id)
)
go
اسکریپت درج رکوردهای پایه ای:
use mytest;
insert into mytest.dbo.Company(title)values('Microsoft')
insert into mytest.dbo.Company(title)values('Sony')
insert into mytest.dbo.Company(title)values('Apple')
insert into mytest.dbo.Company(title)values('Dell')
insert into mytest.dbo.Company(title)values('Nokia')
insert into mytest.dbo.Company(title)values('Reseller I')
insert into mytest.dbo.Company(title)values('Reseller II')
insert into mytest.dbo.Company(title)values('Reseller III')
declare @id int
declare @fk int
select @id = id from mytest.dbo.Company where title='Microsoft'
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('SQL Server 2005',@id,0,4000)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('SQL Server 2008',@id,0,5000)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Visual Studio 2005',@id,0,6000)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Visual Studio 2008',@id,0,8000)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Visual Studio 2010',@id,0,9000)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('XBox',@id,0,1000)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Windows XP',@id,0,2000)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Windows 7',@id,0,3000)
select @id = id from mytest.dbo.Company where title='Sony'
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Sony.Laptop I',@id,0,2500)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Sony.Laptop II',@id,0,2300)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Sony.Laptop III',@id,0,2800)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Sony.LCD I',@id,0,2500)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Sony.LCD II',@id,0,2200)
select @id = id from mytest.dbo.Company where title='Apple'
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('iPod',@id,0,800)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Mac I',@id,0,2000)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('iPad',@id,0,600)
select @id = id from mytest.dbo.Company where title='Dell'
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Dell.Laptop I',@id,0,1200)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Dell.Laptop II',@id,0,1300)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Dell.Laptop III',@id,0,1800)
select @id = id from mytest.dbo.Company where title='Nokia'
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('N95',@id,0,650)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('N96',@id,0,700)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('N97',@id,0,800)
select @id = id from mytest.dbo.Company where title='Reseller I'
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('SQL Server 2005',@id,4000,4200)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Visual Studio 2005',@id,6000,6200)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Windows XP',@id,2000,2100)
select @id = id from mytest.dbo.Company where title='Reseller II'
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Sony.Laptop I',@id,2500,2600)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('XBox',@id,1000,1100)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Dell.Laptop II',@id,1300,1450)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Dell.Laptop III',@id,1800,1950)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('iPod',@id,800,850)
select @id = id from mytest.dbo.Company where title='Reseller III'
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Sony.Laptop II',@id,2300,2450)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Dell.Laptop I',@id,1200,1300)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('iPod',@id,800,900)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('iPad',@id,600,650)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('N95',@id,650,680)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('N97',@id,800,850)
insert into mytest.dbo.Customer(code,firstname,lastname)values ('001','Hamed','Karimi')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('002','Ali','Saberi')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('003','Reza','Mahmoodi')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('004','Pedram','Shayan')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('005','Naser','Kiani')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('006','Behnoosh','Ahmadi')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('007','Kazem','Razavi')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('008','Hojat','Yazdani')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('009','Taher','Forooghi')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('010','Bita','Nazemi')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('011','John','White')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('012','Edward','Brown')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('013','Tom','Fox')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('014','Jerri','Perkins')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('015','Susan','Daiton')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('016','Katrin','Albert')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('017','Adam','Mckey')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('018','Nick','Luke')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('019','Deniz','Oldman')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('020','Ben','Howard')
اسکریپت درج تعدادی سفارش به صورت تصادفی:
use mytest;
declare @MAX_INV_COUNT int
declare @MAX_QUANTITY int
declare @customerCount int
declare @productCount int
declare @start_date datetime
declare @enddate datetime
declare @currentdate datetime
declare @customer_id int
declare @invoice_id int
declare @invoice_code varchar(10)
declare @invoice_count int
declare @product_id int
declare @quantity int
declare @row int
declare @counter int
set @MAX_INV_COUNT = 6
set @MAX_QUANTITY = 10
set @start_date = '2010-06-01'
set @enddate = '2011-01-01'
select @customerCount = COUNT(id) from mytest.dbo.Customer
select @productCount = COUNT(id) from mytest.dbo.Product
set @currentdate = @start_date
while @currentdate < @enddate
begin
-- step 1. choose a random customer
set @row = ROUND(rand() * (@customerCount - 1),0) + 1;
with cte as
(
select ROW_NUMBER() over(order by id) as Row,id from mytest.dbo.Customer
)
select @customer_id = cte.id from cte where cte.Row = @row
-- step 2. create an invoice
-- 2.1. choose a random invoice code which does not already exist in the invoice table
while 1 = 1
begin
set @invoice_code = 'INV' + right('00000' + cast(round(RAND() * 10000,0) as varchar(10)),5)
if not exists(select 1 from mytest.dbo.invoice where code = @invoice_code) break;
end
-- 2.2. create the invoice
insert into mytest.dbo.invoice(code,customer_id,[date])values(@invoice_code,@customer_id,@currentdate)
select @invoice_id = id from mytest.dbo.invoice where code = @invoice_code
-- step 3. order a random number of random products
-- 3.1. choose number of orders
set @invoice_count = ROUND(rand() * @MAX_INV_COUNT,0)
set @counter = 1
while 1 = 1
begin
-- 3.2. choose a random product
set @row = ROUND(rand() * (@productCount - 1),0) + 1;
with cte as
(
select ROW_NUMBER() over(order by id) as Row,id from mytest.dbo.Product
)
select @product_id = cte.id from cte where cte.Row = @row
-- 3.3. choose a random quantity
set @quantity = ROUND(rand() * @MAX_QUANTITY,0)
-- 3.4. create invoice detail
insert into mytest.dbo.invoicedetail(invoice_id,product_id,qua ntity)values(@invoice_id,@product_id,@quantity)
set @counter = @counter + 1
if @counter > @invoice_count break;
end
set @currentdate = DATEADD(DAY,1,@currentdate)
end
vBulletin® v4.2.5, Copyright ©2000-1403, Jelsoft Enterprises Ltd.