DECLARE @stmt varchar(8000) SET @stmt='SELECT dmst.stno,dmst.stname' SELECT @stmt=@stmt+',sum(CASE WHEN dmstm.stmsubject='''+stmsubject+''' AND substring(dmstm.stmtype,1,2)='''+stmtype1+''' THEN ISNULL(dmstm.stmsm,0) ELSE 0 END) /sum(CASE WHEN dmstm.stmsubject='''+stmsubject+''' AND substring(dmstm.stmtype,1,2)='''+stmtype1+''' THEN 1 ELSE 0 END) '''+stmsubject+stmtype1+'''' FROM (SELECT DISTINCT stmsubject,substring(stmtype,1,2) as stmtype1 FROM dmstm where substring(dmstm.stmstno,2,4)='0201') --group by dmstm.stmsubject,dmstm.stmtype tSET @stmt=@stmt+' into ##aa FROM dmst INNER JOIN dmstm ON dmst.stno=dmstm.stmstno and substring(dmst.stno,2,4)=''0201'' WHERE dmstm.stmyear=''20042005'' AND dmstm.stmpd=''第一学期'' GROUP BY dmst.stno,dmst.stname ORDER BY dmst.stno'print(@stmt)EXECUTE(@stmt) select * from ##aa
SET @stmt=@stmt+' into #aa FROM dmst ---如果改成局部临时表 INNER JOIN dmstm ON dmst.stno=dmstm.stmstno and substring(dmst.stno,2,4)=''0201'' WHERE dmstm.stmyear=''20042005'' AND dmstm.stmpd=''第一学期'' GROUP BY dmst.stno,dmst.stname ORDER BY dmst.stno'print(@stmt)EXECUTE(@stmt;select * from #aa)
DECLARE @stmt varchar(8000) SET @stmt='SELECT dmst.stno,dmst.stname' SELECT @stmt=@stmt+',sum(CASE WHEN dmstm.stmsubject='''+stmsubject+''' AND substring(dmstm.stmtype,1,2)='''+stmtype1+''' THEN ISNULL(dmstm.stmsm,0) ELSE 0 END) /sum(CASE WHEN dmstm.stmsubject='''+stmsubject+''' AND substring(dmstm.stmtype,1,2)='''+stmtype1+''' THEN 1 ELSE 0 END) '''+stmsubject+stmtype1+'''' FROM (SELECT DISTINCT stmsubject,substring(stmtype,1,2) as stmtype1 FROM dmstm where substring(dmstm.stmstno,2,4)='0201') --group by dmstm.stmsubject,dmstm.stmtype tSET @stmt=@stmt+' into ##T FROM dmst --生成到一个临时表中 INNER JOIN dmstm ON dmst.stno=dmstm.stmstno and substring(dmst.stno,2,4)=''0201'' WHERE dmstm.stmyear=''20042005'' AND dmstm.stmpd=''第一学期'' GROUP BY dmst.stno,dmst.stname ORDER BY dmst.stno'print(@stmt)EXECUTE(@stmt)select * from ##t --显示结果 drop table ##t
SET @stmt='SELECT dmst.stno,dmst.stname'
SELECT @stmt=@stmt+',sum(CASE
WHEN dmstm.stmsubject='''+stmsubject+'''
AND substring(dmstm.stmtype,1,2)='''+stmtype1+'''
THEN ISNULL(dmstm.stmsm,0)
ELSE 0
END)
/sum(CASE
WHEN dmstm.stmsubject='''+stmsubject+'''
AND substring(dmstm.stmtype,1,2)='''+stmtype1+'''
THEN 1
ELSE 0
END)
'''+stmsubject+stmtype1+''''
FROM (SELECT DISTINCT stmsubject,substring(stmtype,1,2) as stmtype1
FROM dmstm where substring(dmstm.stmstno,2,4)='0201')
--group by dmstm.stmsubject,dmstm.stmtype
tSET @stmt=@stmt+' into ##aa FROM dmst
INNER JOIN dmstm
ON dmst.stno=dmstm.stmstno
and substring(dmst.stno,2,4)=''0201''
WHERE dmstm.stmyear=''20042005'' AND
dmstm.stmpd=''第一学期''
GROUP BY dmst.stno,dmst.stname
ORDER BY dmst.stno'print(@stmt)EXECUTE(@stmt)
select * from ##aa
INNER JOIN dmstm
ON dmst.stno=dmstm.stmstno
and substring(dmst.stno,2,4)=''0201''
WHERE dmstm.stmyear=''20042005'' AND
dmstm.stmpd=''第一学期''
GROUP BY dmst.stno,dmst.stname
ORDER BY dmst.stno'print(@stmt)EXECUTE(@stmt;select * from #aa)
SET @stmt='SELECT dmst.stno,dmst.stname'
SELECT @stmt=@stmt+',sum(CASE
WHEN dmstm.stmsubject='''+stmsubject+'''
AND substring(dmstm.stmtype,1,2)='''+stmtype1+'''
THEN ISNULL(dmstm.stmsm,0)
ELSE 0
END)
/sum(CASE
WHEN dmstm.stmsubject='''+stmsubject+'''
AND substring(dmstm.stmtype,1,2)='''+stmtype1+'''
THEN 1
ELSE 0
END)
'''+stmsubject+stmtype1+''''
FROM (SELECT DISTINCT stmsubject,substring(stmtype,1,2) as stmtype1
FROM dmstm where substring(dmstm.stmstno,2,4)='0201')
--group by dmstm.stmsubject,dmstm.stmtype
tSET @stmt=@stmt+' into ##T FROM dmst --生成到一个临时表中
INNER JOIN dmstm
ON dmst.stno=dmstm.stmstno
and substring(dmst.stno,2,4)=''0201''
WHERE dmstm.stmyear=''20042005'' AND
dmstm.stmpd=''第一学期''
GROUP BY dmst.stno,dmst.stname
ORDER BY dmst.stno'print(@stmt)EXECUTE(@stmt)select * from ##t --显示结果
drop table ##t