现在有个表A
-------------------------
classname score term
语文 20 03
数学 40 03
外语 10 03
物理 43 03
-------------------------
有可能查询不到结果,例如查询term=04的记录,也就是出现下面结构
---------------------------
classname score term
--------------------------- 现在我想在查询不到的时候补充一天记录,如下
---------------------------
classname score term
无 0 0
--------------------------- 请问该怎么写sql?
-------------------------
classname score term
语文 20 03
数学 40 03
外语 10 03
物理 43 03
-------------------------
有可能查询不到结果,例如查询term=04的记录,也就是出现下面结构
---------------------------
classname score term
--------------------------- 现在我想在查询不到的时候补充一天记录,如下
---------------------------
classname score term
无 0 0
--------------------------- 请问该怎么写sql?
INSERT INTO A VALUES('语文',20,'03');
INSERT INTO A VALUES('数学',40,'03');
INSERT INTO A VALUES('外语',10,'03');
INSERT INTO A VALUES('物理',43,'03');
COMMIT;SELECT DISTINCT
DECODE(T2.NUM,0,T2.classname,T1.classname) classname,
DECODE(T2.NUM,0,T2.score,T1.score) score,
DECODE(T2.NUM,0,T2.term,T1.term)term
FROM A T1,
(SELECT COUNT('X') NUM,'无' classname,0 score,'0' term FROM A WHERE TERM='04') T2
WHERE T1.term='04' OR NUM=0;CLASSNAME SCORE TERM
---------- ---------- ----------
无 0 0
我觉得最好在界面逻辑上解决。
假设表test有字段name1和 name2 声明个变量 xselect '0' name1, '0' name2 from test where name1 = X having count(*)=0
union all
select name1,name2 from test
where name1 = X