上周末写出来了 不过如果数据量大的话相信是很慢的,所以我把它写到view里面去了。
谢谢大家CREATE OR REPLACE VIEW AGENTEXAM_MARK_ENTERFOR ( ID,
NAME, SEX, BIRTHDAY, NATION,
IDCARD, STUDYLEVEL, SPECIALTY, FOREIGNLANGUAGE,
ADDRESS, WORKUNIT, UNITTYPE, AREA,
TEL, LEARNINGRESUME, WORKRESUME, AUDITOPINION,
EXAMCARDNO, PATENTAGENTEXAMROOMID, ENTERFEE, SORTNO,
REMARK, ORGANTYPEID, CREATEUSERID, CREATEDATE,
PATENTAGENTEXAMREQUESTID, PHOTOPATH, STATUS, PHONE,
NOWADDRESS, EXAM0001_KSKSKM, EXAM0002_KSKSKM, EXAM0003_KSKSKM,
EXAM0004_KSKSKM ) AS SELECT a.*,b.EXAM0001_KSKSKM,b.EXAM0002_KSKSKM,b.EXAM0003_KSKSKM,b.EXAM0004_KSKSKM
FROM PATENTAGENTEXAM_ENTERFOR a, (
SELECT PATENTAGENTEXAMENTERFORID,SUM(A1) EXAM0001_KSKSKM,SUM(A2) EXAM0002_KSKSKM,SUM(A3) EXAM0003_KSKSKM,SUM(A4) EXAM0004_KSKSKM,
+SUM(A1)+SUM(A2)+SUM(A3)+SUM(A4) SUMMARK FROM
(SELECT PATENTAGENTEXAMENTERFORID,
(DECODE(EXAMITEM,'0001_KSKSKM',SUM(PAPERMARK))) A1,
(DECODE(EXAMITEM,'0002_KSKSKM',SUM(PAPERMARK))) A2,
(DECODE(EXAMITEM,'0003_KSKSKM',SUM(PAPERMARK))) A3,
(DECODE(EXAMITEM,'0004_KSKSKM',SUM(PAPERMARK))) A4
FROM PATENTAGENTEXAM_MARK GROUP BY PATENTAGENTEXAMENTERFORID,EXAMITEM)
GROUP BY PATENTAGENTEXAMENTERFORID) b WHERE a.id=b.PATENTAGENTEXAMENTERFORID
谢谢大家CREATE OR REPLACE VIEW AGENTEXAM_MARK_ENTERFOR ( ID,
NAME, SEX, BIRTHDAY, NATION,
IDCARD, STUDYLEVEL, SPECIALTY, FOREIGNLANGUAGE,
ADDRESS, WORKUNIT, UNITTYPE, AREA,
TEL, LEARNINGRESUME, WORKRESUME, AUDITOPINION,
EXAMCARDNO, PATENTAGENTEXAMROOMID, ENTERFEE, SORTNO,
REMARK, ORGANTYPEID, CREATEUSERID, CREATEDATE,
PATENTAGENTEXAMREQUESTID, PHOTOPATH, STATUS, PHONE,
NOWADDRESS, EXAM0001_KSKSKM, EXAM0002_KSKSKM, EXAM0003_KSKSKM,
EXAM0004_KSKSKM ) AS SELECT a.*,b.EXAM0001_KSKSKM,b.EXAM0002_KSKSKM,b.EXAM0003_KSKSKM,b.EXAM0004_KSKSKM
FROM PATENTAGENTEXAM_ENTERFOR a, (
SELECT PATENTAGENTEXAMENTERFORID,SUM(A1) EXAM0001_KSKSKM,SUM(A2) EXAM0002_KSKSKM,SUM(A3) EXAM0003_KSKSKM,SUM(A4) EXAM0004_KSKSKM,
+SUM(A1)+SUM(A2)+SUM(A3)+SUM(A4) SUMMARK FROM
(SELECT PATENTAGENTEXAMENTERFORID,
(DECODE(EXAMITEM,'0001_KSKSKM',SUM(PAPERMARK))) A1,
(DECODE(EXAMITEM,'0002_KSKSKM',SUM(PAPERMARK))) A2,
(DECODE(EXAMITEM,'0003_KSKSKM',SUM(PAPERMARK))) A3,
(DECODE(EXAMITEM,'0004_KSKSKM',SUM(PAPERMARK))) A4
FROM PATENTAGENTEXAM_MARK GROUP BY PATENTAGENTEXAMENTERFORID,EXAMITEM)
GROUP BY PATENTAGENTEXAMENTERFORID) b WHERE a.id=b.PATENTAGENTEXAMENTERFORID
解决方案 »
- Js
- java.lang.NoClassDefFoundError: javax/sql/rowset/Joinable
- JSP param页面传值问题
- 谁有MzTreeView 1。0给我传一个,非常感谢。我的MAIL:[email protected](官方链接已经失效了)
- jsp的 一个很弱 的 问题
- log4j.properties如何将输出的文件路径配置为相对路径?
- 如何删除同一表里的重复项和null
- 请问一个彻底解决文本框防止乱输入"''<td>之类的问题!!!感谢!
- 影响网站速度的因素,大家列举一下!
- javaweb为什么JSP页面能打开action页面打不开...
- jsp初学,望高手给点学习建议!
- 请教最流行最使用的jsp编辑器?jsp新手
--drop table 表a
create table 表a(学生id int,科目 varchar(20),分数 int)
insert into 表a
select 1,'语文',80 union all
select 1,'数学',70 union all
select 1,'英语',90 union all
select 2,'语文',80 union all
select 2,'数学',70 union all
select 2,'英语',90 union all
select 2,'政治',70 union all
select 3,'英语',90---查询语句-------------------------------------------------declare @sql varchar(8000)
set @sql = 'select 学生id'
select @sql = @sql + ',sum(case 科目 when '''+科目+''' then 分数 end) ['+科目+']'
from (select distinct 科目 from 表a) as a
select @sql = @sql+' from 表a group by 学生id'exec(@sql)
go--删除环境
--drop table 表a