--create database myTestUSE myTest--CREATE TABLEIF 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 DATAINSERT INTO [dbo].[Country] (COUNTRYNAME)SELECT 'CHINA' UNION ALLSELECT 'USA' UNION ALLSELECT 'CHINA TAIWAN' UNION ALLSELECT 'CHINA HONGKONG' UNION ALLSELECT 'JANPAN' UNION ALLSELECT 'Finland'INSERT INTO Product(PROJUCTNAME,PROJUCTMODEL)SELECT 'NOKIA','N70' UNION ALLSELECT 'NOKIA','N71' UNION ALLSELECT 'NOKIA','N72' UNION ALLSELECT 'NOKIA','N73' UNION ALLSELECT 'NOKIA','N74' UNION ALLSELECT 'Lenovo','P1' UNION ALLSELECT 'Lenovo','P2' UNION ALLSELECT 'Lenovo','P3' UNION ALLSELECT 'Lenovo','P4' UNION ALLSELECT 'MICROSOFT','WIN7' UNION ALLSELECT 'MICROSOFT','XP' UNION ALLSELECT 'MICROSOFT','2003' UNION ALLSELECT 'Asus','p12345' UNION ALLSELECT 'Asus','p22345' UNION ALLSELECT 'Asus','p32345' UNION ALLSELECT 'Asus','p42345'INSERT INTO Country_ProjuctSELECT 1,6 UNION ALLSELECT 2,6 UNION ALLSELECT 3,6 UNION ALLSELECT 4,6 UNION ALLSELECT 5,6 UNION ALLSELECT 6,1 UNION ALLSELECT 7,1 UNION ALLSELECT 8,1 UNION ALLSELECT 9,1 UNION ALLSELECT 10,2 UNION ALLSELECT 11,2 UNION ALLSELECT 12,2 UNION ALLSELECT 13,3 UNION ALLSELECT 14,3 UNION ALLSELECT 15,3 UNION ALLSELECT 16,3 --BEGION CURSORDECLARE @cid intDECLARE @cName nvarchar(200)DECLARE CountryCursor cursor for select CountryID,countryname from countryopen CountryCursorfetch next from CountryCursor into @cid,@cNamewhile @@FETCH_STATUS = 0beginprint 'Country:'+@cName+' Product:'print '-----------------------------------'declare @pname nvarchar(200)declare @pmodel nvarchar(200)declare @rowCount int = 0 -- count total--second cursordeclare projuctCursor cursor for select Product.ProjuctName,ProjuctModel from Product,Country_Projuct whereCountry_Projuct.CountryID = @cidand Country_Projuct.ProjuctID = Product.ProjuctID open projuctCursor fetch next from projuctCursor into @pname,@pmodel while @@FETCH_STATUS = 0begin set @rowCount = @rowCount + 1;print @pname+' '+@pmodel;fetch next from projuctCursor into @pname,@pmodelend if @rowCount = 0print 'no data'print '-----------------------------------' close projuctCursor deallocate projuctCursorfetch next from CountryCursor into @cid,@cNameend close CountryCursordeallocate CountryCursor--DELETE TABLEIF 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 P1Lenovo P2Lenovo P3Lenovo P4-----------------------------------Country:USA Product:-----------------------------------MICROSOFT WIN7MICROSOFT XPMICROSOFT 2003-----------------------------------Country:CHINA TAIWAN Product:-----------------------------------Asus p12345Asus p22345Asus p32345Asus p42345-----------------------------------Country:CHINA HONGKONG Product:-----------------------------------no data-----------------------------------Country:JANPAN Product:-----------------------------------no data-----------------------------------Country:Finland Product:-----------------------------------NOKIA N70NOKIA N71NOKIA N72NOKIA N73NOKIA N74-----------------------------------