你的数据库是10G么? 10G有WMSYS.WM_CONCAT() select a1 WMSYS.WM_CONCAT(a2) from t group by a1
不过这个有逗号间隔的 你把逗号 replace下就可以了
第一题: with temp as( select '张三' name,'语文' kecheng,81 fenshu from dual union all select '张三' name,'数学' kecheng,75 fenshu from dual union all select '李四' name,'语文' kecheng,76 fenshu from dual union all select '李四' name,'数学' kecheng,90 fenshu from dual union all select '王五' name,'语文' kecheng,81 fenshu from dual union all select '王五' name,'数学' kecheng,100 fenshu from dual union all select '王五' name,'英语' kecheng,90 fenshu from dual )select name from temp having min(fenshu) > 80 group by name with temp as( select 1 a1,'a' a2 from dual union all select 1 a1,'b' a2 from dual union all select 2 a1,'x' a2 from dual union all select 2 a1,'y' a2 from dual union all select 2 a1,'z' a2 from dual ) select a1,replace(wm_concat(a2),',') a3 from temp group by a1
这么复杂,我还以为用all之后会简单点select distinct name from table where name not in(select distinct name from table where fenshu<60)
SELECT NAME FROM t WHERE t.name NOT IN (SELECT NAME FROM t WHERE t.fenshu <=80) 是这个意思么?
同意此内容 效率高些 不过有地方的改下 select name from temp group by name having min(fenshu) > 80
10G有WMSYS.WM_CONCAT()
select a1 WMSYS.WM_CONCAT(a2) from t group by a1
with temp as(
select '张三' name,'语文' kecheng,81 fenshu from dual
union all
select '张三' name,'数学' kecheng,75 fenshu from dual
union all
select '李四' name,'语文' kecheng,76 fenshu from dual
union all
select '李四' name,'数学' kecheng,90 fenshu from dual
union all
select '王五' name,'语文' kecheng,81 fenshu from dual
union all
select '王五' name,'数学' kecheng,100 fenshu from dual
union all
select '王五' name,'英语' kecheng,90 fenshu from dual
)select name from temp having min(fenshu) > 80 group by name with temp as(
select 1 a1,'a' a2 from dual
union all
select 1 a1,'b' a2 from dual
union all
select 2 a1,'x' a2 from dual
union all
select 2 a1,'y' a2 from dual
union all
select 2 a1,'z' a2 from dual
)
select a1,replace(wm_concat(a2),',') a3 from temp group by a1
这么复杂,我还以为用all之后会简单点select distinct name from table where name not in(select distinct name from table where fenshu<60)
同意此内容 效率高些
不过有地方的改下
select name from temp group by name having min(fenshu) > 80