將多結果組成一張大表並使用迴圈方式，產生多筆結果
USE PA
DECLARE @com_date1 varchar(8)
DECLARE @com_date2 varchar(8)
DECLARE @COP_NO varchar(4)
DECLARE @PROJM_NO varchar(6)
DECLARE @tstStr as varchar(2000)
DECLARE @tstField as varchar(10)
DECLARE @tstCnt as int
set @com_date1 ='20151001'
set @com_date2 ='20151031'
set @COP_NO = 'FH01'
--set @PROJM_NO ='13A003'
select @tstStr = '11A010,11A005,11A006,11A007,11A009,12A005,13A001,13A002,13A003,13A004,13A005,14A006' --將這些code寫到Store Procedure中 ,這可以是AP傳入的參數
while (@tstStr <> '')
begin
SELECT @tstCnt = PATINDEX('%,%', @tstStr)
if @tstCnt > 0
begin
select @tstField = left(@tstStr,@tstCnt-1)
-- select @tstField --將資料抓出
select @tstStr = right(@tstStr,len(@tstStr)-@tstCnt)
end
else
begin
select @tstField = @tstStr
-- select @tstField --將資料抓出
select @tstStr = ''
end
--select @tstField
select @PROJM_NO = @tstField
select @tstField = ''
SELECT @PROJM_NO 專案代號 ,COUNT(*) as '資料總筆數',
(SELECT COUNT(*) '姓名+EMAIL'
FROM PBUYM a1,SA.DBO.Pjmsa a2
WHERE a1.COP_NO IN ('FH01','FH04','FH05') AND a1.PROJM_NO NOT IN ('FWE','IN800','99A001')
and a1.COP_NO=a2.COP_NO and a1.PROJM_NO=a2.PROJM_NO
and a1.com_date between @com_date1 and @com_date2
--and a1.BIRTHDAY is not null
--and a1.E_MAIL is not null
and a1.COP_NO=@COP_NO
and a1.PROJM_NO=@PROJM_NO
and a1.SUBFI_NAME is not null and (a1.E_MAIL is not null and a1.E_MAIL like '%@%')) as 姓名_EMAIL,
(SELECT COUNT(*) '姓名+地址'
FROM PBUYM a1,SA.DBO.Pjmsa a2
WHERE a1.COP_NO IN ('FH01','FH04','FH05') AND a1.PROJM_NO NOT IN ('FWE','IN800','99A001')
and a1.COP_NO=a2.COP_NO and a1.PROJM_NO=a2.PROJM_NO
and a1.com_date between @com_date1 and @com_date2
--and a1.BIRTHDAY is not null
--and a1.E_MAIL is not null
and a1.COP_NO=@COP_NO
and a1.PROJM_NO=@PROJM_NO
and a1.SUBFI_NAME is not null and len(a1.COM_ADDR) >= 12) as 姓名_地址,
(SELECT COUNT(*) '姓名+電話'
FROM PBUYM a1,SA.DBO.Pjmsa a2
WHERE a1.COP_NO IN ('FH01','FH04','FH05') AND a1.PROJM_NO NOT IN ('FWE','IN800','99A001')
and a1.COP_NO=a2.COP_NO and a1.PROJM_NO=a2.PROJM_NO
and a1.com_date between @com_date1 and @com_date2
--and a1.BIRTHDAY is not null
--and a1.E_MAIL is not null
and a1.COP_NO=@COP_NO
and a1.PROJM_NO=@PROJM_NO
and a1.SUBFI_NAME is not null and LEFT(a1.TEL3,2)='09') as 姓名_電話,
(SELECT COUNT(*) '姓名+電話+生日'
FROM PBUYM a1,SA.DBO.Pjmsa a2
WHERE a1.COP_NO IN ('FH01','FH04','FH05') AND a1.PROJM_NO NOT IN ('FWE','IN800','99A001')
and a1.COP_NO=a2.COP_NO and a1.PROJM_NO=a2.PROJM_NO
and a1.com_date between @com_date1 and @com_date2
--and a1.BIRTHDAY is not null
--and a1.E_MAIL is not null
and a1.COP_NO=@COP_NO
and a1.PROJM_NO=@PROJM_NO
and a1.SUBFI_NAME is not null and LEFT(a1.TEL3,2)='09' AND a1.BIRTHDAY is not null) as 姓名_電話_生日,
(SELECT COUNT(*) '姓名+地址+生日'
FROM PBUYM a1,SA.DBO.Pjmsa a2
WHERE a1.COP_NO IN ('FH01','FH04','FH05') AND a1.PROJM_NO NOT IN ('FWE','IN800','99A001')
and a1.COP_NO=a2.COP_NO and a1.PROJM_NO=a2.PROJM_NO
and a1.com_date between @com_date1 and @com_date2
--and a1.BIRTHDAY is not null
--and a1.E_MAIL is not null
and a1.COP_NO=@COP_NO
and a1.PROJM_NO=@PROJM_NO
and a1.SUBFI_NAME is not null and (a1.E_MAIL is not null and a1.E_MAIL like '%@%') -- 姓名+EMAIL
and a1.SUBFI_NAME is not null and len(a1.COM_ADDR) >= 12 and a1.BIRTHDAY is not null) as 姓名_地址_生日,
(SELECT COUNT(*) '姓名+地址+電話+生日+EMAIL'
FROM PBUYM a1,SA.DBO.Pjmsa a2
WHERE a1.COP_NO IN ('FH01','FH04','FH05') AND a1.PROJM_NO NOT IN ('FWE','IN800','99A001')
and a1.COP_NO=a2.COP_NO and a1.PROJM_NO=a2.PROJM_NO
and a1.com_date between @com_date1 and @com_date2
--and a1.BIRTHDAY is not null
--and a1.E_MAIL is not null
and a1.COP_NO=@COP_NO
and a1.PROJM_NO=@PROJM_NO
and a1.SUBFI_NAME is not null and len(a1.COM_ADDR) >= 12 and a1.BIRTHDAY is not null and LEFT(a1.TEL3,2)='09' and (a1.E_MAIL is not null and a1.E_MAIL like '%@%')) 姓名_電話_地址_生日_E_MAIL
FROM PBUYM a1,SA.DBO.Pjmsa a2
WHERE a1.COP_NO IN ('FH01','FH04','FH05') AND a1.PROJM_NO NOT IN ('FWE','IN800','99A001')
and a1.COP_NO=a2.COP_NO and a1.PROJM_NO=a2.PROJM_NO
and a1.com_date between @com_date1 and @com_date2
and a1.COP_NO=@COP_NO
and a1.PROJM_NO=@PROJM_NO
end
產生結果：
留言