我有表和数据。
我想要的是,生成一个sorted
到那里的所有学生的报告names
。
--student table
create table student(
sid int,
sname text
);
--student details
insert into student(sid,sname)
values(101,'John'),
(102,'barbie'),
(103,'britney'),
(104,'jackson'),
(105,'abraham')
;
--questions table all the questions for the test
create table questions(
questionid serial,
question text
);
--i have the questions in my table
insert into questions(question)
values('How much is 1+1'),('What is the value of PI'),('Whose dimensions are all equal');
--the test table it contains the details of the test attebdee by every student..
create table test(
sno serial,
sid int,
questionid int,
answer text,
marks int
);
--insert into test table the answers and the marks ..should be updated here..
insert into test(sid,questionid,answer,marks)
values(101,1,'2',10),
(102,2,' 3 ',0),
(103,3,' ring ',0),
(104,1,' 1 ',0),
(105,1,' 1 ',0),
(101,2,'3.7',0),
(101,3,' square',10);
我的要求:
我生成的txt / doc / pdf / html文件应位于以下视图中
可能是这样的:
copy(
with cte as (
select
s.sid, s.sname,
q.question, t.answer, t.marks,
row_number() over(partition by s.sid order by t.sno) as row_num
from student as s
left outer join test as t on t.sid = s.sid
left outer join questions as q on q.questionid = t.questionid
)
select
case when c.row_num = 1 then c.sid else null end as sid,
case when c.row_num = 1 then c.sname else null end as sname,
c.question, c.answer, c.marks
from cte as c
order by c.sname asc, c.row_num asc
) to 'e:\sample.csv' delimiter ',' csv header;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句