数据挖掘Demo数据
--create table Oders (OdersID int identity(1,1) primary key, UsersID int)
--create table OderIterms( OdersID int,ProductID int)
--create table Product(ProductID int identity(1,1) primary key , ProductName varchar(50) )
--create table Users(UsersID int identity(1,1) primary key,gender bit,age int )
--declare @i int
--set @i=0
----
--while @i< 10000
--begin
--insert Users select case when rand()>0.5 then 1 else 0 end as gender,CAST( rand()*100 as int) as age
--set @i=@i+1
--end
----
----
--set @i=0
--while @i<10*10000
--begin
--insert Product select '产品'+ CAST( rand()*100 as varchar) as ProductName
--set @i=@i+1
--end
----
----
--set @i=0
--while @i<100*10000
--begin
--insert Oders select top 1 UsersID as UsersID from Users order by NEWID()
--set @i=@i+1
--end
--
declare @i int
set @i=0
declare @maxUser int
select @maxUser= MAX(UsersID) from Users
select CAST( RAND()*@maxUser as int)
declare @minUser int
select @minUser=Min(UsersID) from Users
declare @maxOders int
select @maxOders= MAX(OdersID) from Oders
declare @minOders int
select @minOders= Min(OdersID) from Oders
while @i<1000*10000
begin
insert OderIterms select
@minUser+ CAST(RAND()*@maxUser as int) as OdersID ,
@minOders + CAST(RAND()*@maxOders as int) as OdersID
set @i=@i+1
end
--select COUNT(*) from dbo.OderIterms
--delete from dbo.Users
--delete from dbo.OderIterms
--delete from dbo.Oders
--delete from dbo.Product
--select COUNT(*) from Product
--十万商品
declare @PID int
set @PID=12345
select top 10 A.productID,COUNT(A.productID) 数量
from dbo.OderIterms A
inner join dbo.Oders B
on A.productID!=@PID
and B.UsersID in
(select C.Usersid from dbo.Oders C inner join OderIterms D
on C.OdersID=D.OdersID where D.productID=@PID)
group by A.productID
order by 数量 desc
select A.productID productID ,B.UsersID UsersID into #tb from
OderIterms A inner join Oders B
on A.OdersID=B.OdersID
select top 1 A.productID,B.productID,count(*) from #tb A inner join #tb B
on A.UsersID=B.UsersID and A.productID>B.productID
group by A.productID,B.productID
order by count(*)
