-- 主檔(同一客戶會有多筆資料)
Create   Table  tbMain (
    aID  int   identity ( 1 , 1 ),
    pID  varchar ( 10 )  not   null ,     -- 客戶ID
    pName  nvarchar ( 20 )  not   null ,     -- 客戶姓名
    pDoing  varchar ( 20 )  not   null
);
-- 剔除客戶檔(僅存放客戶姓名)
Create   Table  tbForeclose (
    pName  nvarchar ( 20 )  not   null      -- 剔除客戶姓名
);
-- 客戶資料明細檔
Create   Table  tbDetail (
    pID  varchar ( 10 )  primary   key ,     -- 客戶ID(KEY)
    pSex  char ( 1 )  not   null          -- 客戶其他詳細資料
);

示例數據
SET  NOCOUNT  ON
Declare   @iRowCount   int
Declare   @pID   varchar ( 10 ), @pName   nvarchar ( 20 ), @pDoing   varchar ( 20 ), @pSex   char ( 1 )
Declare   @i   int , @j   int , @k   int
-- 生成tbMain記錄條數
Set   @iRowCount = 1000
Select   @i = 1
While   @i   <   @iRowCount
Begin
     -- 生成客戶ID
     Set   @j   =   1
     While   @j   >   0
     Begin
         Set   @pID   =   Left ( NewID (), 10 )
         Select   @j   =   Count ( * )  From  tbDetail  Where  pID = @pID
     End
     -- 生成客戶姓名
     Set   @k   =   2 + Round ( 3 * Rand (), 0 )
     Select   @j   =   1 , @pName = ''
     While   @j   <=   @k
     Begin
         Set   @pName   =   @pName   +   NChar ( 31000   +   Round ( 3000 * Rand (), 0 ))
         Set   @j   =   @j   +   1
     End
     -- 生成客戶性別
     If   @k   %   2   =   0
         Set   @pSex   =   ' T '
     Else
         Set   @pSex   =   ' F '
     -- 添加資料
     -- 主檔添加筆數(1-5筆)
     Set   @k   =   1   +   Round ( 5 * Rand (), 0 )
     Set   @j   =   1
     While   @j   <=   @k
     Begin
         Set   @i   =   @i   +   1
         -- 生成Doing欄位
         Set   @pDoing   =   Cast ( @j   As   Varchar ( 2 ))  +   ''' th Doing '
         Insert   Into  tbMain  Values  ( @pID , @pName , @pDoing )
         Set   @j   =   @j   +   1
     End
     -- 客戶資料明細檔
     Insert   Into  tbDetail  Values  ( @pID , @pSex )
End
-- 隨機向剔除客戶檔添加1/10的客戶姓名
Set   @k   =   Round ( @iRowCount   /   10 , 0 )
SET   ROWCOUNT    @k
Insert   Into  tbForeclose  Select  pName  From  tbMain  Group   By  pID,pName  Order   By   NewID ()
SET   ROWCOUNT   0
SET  NOCOUNT  OFF

Select
         *
     From
        tbDetail
     Inner   Join  (
         Select
                pID,pName
             From
                tbMain
             Where
                 Not   Exists  ( Select   1   From  tbForeclose  Where  tbForeclose.pName = tbMain.pName)
             Group   By
                pID,pName
             Having
                 Count ( * ) = 1
    ) tbMain1  On  tbMain1.pID = tbDetail.pID