--create database myTest
USE myTest
--CREATE TABLE
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'Country') AND type = N'U')
CREATE TABLE Country(
CountryID INT IDENTITY(1,1) PRIMARY KEY,
CountryName NVARCHAR(255) NOT NULL
)
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'Product') AND type = N'U')
CREATE TABLE Product (
ProjuctID int IDENTITY(1,1) PRIMARY KEY,
ProjuctName NVARCHAR(255) NOT NULL,
ProjuctModel NVARCHAR(255) NULL
)
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'Country_Projuct') AND type = N'U')
CREATE TABLE Country_Projuct(
ProjuctID int foreign key references Product(ProjuctID),
CountryID int foreign key references Country(CountryID)
PRIMARY KEY (ProjuctID,CountryID)
)
--INSERT DATA
INSERT INTO [dbo].[Country] (COUNTRYNAME)
SELECT 'CHINA' UNION ALL
SELECT 'USA' UNION ALL
SELECT 'CHINA TAIWAN' UNION ALL
SELECT 'CHINA HONGKONG' UNION ALL
SELECT 'JANPAN' UNION ALL
SELECT 'Finland'
INSERT INTO Product(PROJUCTNAME,PROJUCTMODEL)
SELECT 'NOKIA','N70' UNION ALL
SELECT 'NOKIA','N71' UNION ALL
SELECT 'NOKIA','N72' UNION ALL
SELECT 'NOKIA','N73' UNION ALL
SELECT 'NOKIA','N74' UNION ALL
SELECT 'Lenovo','P1' UNION ALL
SELECT 'Lenovo','P2' UNION ALL
SELECT 'Lenovo','P3' UNION ALL
SELECT 'Lenovo','P4' UNION ALL
SELECT 'MICROSOFT','WIN7' UNION ALL
SELECT 'MICROSOFT','XP' UNION ALL
SELECT 'MICROSOFT','2003' UNION ALL
SELECT 'Asus','p12345' UNION ALL
SELECT 'Asus','p22345' UNION ALL
SELECT 'Asus','p32345' UNION ALL
SELECT 'Asus','p42345'
INSERT INTO Country_Projuct
SELECT 1,6 UNION ALL
SELECT 2,6 UNION ALL
SELECT 3,6 UNION ALL
SELECT 4,6 UNION ALL
SELECT 5,6 UNION ALL
SELECT 6,1 UNION ALL
SELECT 7,1 UNION ALL
SELECT 8,1 UNION ALL
SELECT 9,1 UNION ALL
SELECT 10,2 UNION ALL
SELECT 11,2 UNION ALL
SELECT 12,2 UNION ALL
SELECT 13,3 UNION ALL
SELECT 14,3 UNION ALL
SELECT 15,3 UNION ALL
SELECT 16,3
--BEGION CURSOR
DECLARE @cid int
DECLARE @cName nvarchar(200)
DECLARE CountryCursor cursor
for select CountryID,countryname from country
open CountryCursor
fetch next from CountryCursor into @cid,@cName
while @@FETCH_STATUS = 0
begin
print 'Country:'+@cName+' Product:'
print '-----------------------------------'
declare @pname nvarchar(200)
declare @pmodel nvarchar(200)
declare @rowCount int = 0 -- count total
--second cursor
declare projuctCursor cursor for
select Product.ProjuctName,ProjuctModel from Product,Country_Projuct where
Country_Projuct.CountryID = @cid
and Country_Projuct.ProjuctID = Product.ProjuctID
open projuctCursor
fetch next from projuctCursor into @pname,@pmodel
while @@FETCH_STATUS = 0
begin
set @rowCount = @rowCount + 1;
print @pname+' '+@pmodel;
fetch next from projuctCursor into @pname,@pmodel
end
if @rowCount = 0
print 'no data'
print '-----------------------------------'
close projuctCursor
deallocate projuctCursor
fetch next from CountryCursor into @cid,@cName
end
close CountryCursor
deallocate CountryCursor
--DELETE TABLE
IF EXISTS(SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[COUNTRY_PROJUCT]') AND type = N'U')
DROP TABLE COUNTRY_PROJUCT
IF EXISTS(SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Product]') AND type = N'U')
DROP TABLE Product
IF EXISTS(SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Country]') AND type = N'U')
DROP TABLE Country
--drop database myTest
Result:
(6 row(s) affected) (16 row(s) affected) (16 row(s) affected) Country:CHINA Product: ----------------------------------- Lenovo P1 Lenovo P2 Lenovo P3 Lenovo P4 ----------------------------------- Country:USA Product: ----------------------------------- MICROSOFT WIN7 MICROSOFT XP MICROSOFT 2003 ----------------------------------- Country:CHINA TAIWAN Product: ----------------------------------- Asus p12345 Asus p22345 Asus p32345 Asus p42345 ----------------------------------- Country:CHINA HONGKONG Product: ----------------------------------- no data ----------------------------------- Country:JANPAN Product: ----------------------------------- no data ----------------------------------- Country:Finland Product: ----------------------------------- NOKIA N70 NOKIA N71 NOKIA N72 NOKIA N73 NOKIA N74 -----------------------------------