table:sys_menu
id,menuname,parentID查询后的列:id,menuname,parentID,parentName查询的sql语句:select a.id,a.menuname,a.menuparent,b.menuname parentname from (select * from sys_menu connect by prior id = menuparent start with menuparent is null order by id) a,sys_menu b
where a.menuparent = b.id or b.menuparent is null如过不加b.menuparent is null根数据就不能查询出来,加了就出现很多重复的数据. 请高手帮忙看看 我的SQL太烂了 呵呵 谢谢
id,menuname,parentID查询后的列:id,menuname,parentID,parentName查询的sql语句:select a.id,a.menuname,a.menuparent,b.menuname parentname from (select * from sys_menu connect by prior id = menuparent start with menuparent is null order by id) a,sys_menu b
where a.menuparent = b.id or b.menuparent is null如过不加b.menuparent is null根数据就不能查询出来,加了就出现很多重复的数据. 请高手帮忙看看 我的SQL太烂了 呵呵 谢谢
或者用group by
where a.menuparent = b.id or b.menuparent is null
用group by 的话,需要这样写了
select a.id,a.menuname,a.menuparent,b.menuname parentname from (select * from sys_menu connect by prior id = menuparent start with menuparent is null order by id) a,sys_menu b
where a.menuparent = b.id or b.menuparent is null
group by a.id,a.menuname,a.menuparent,b.menuname
--------------------------------------------------------------------------
select a.id,a.menuname,a.parentID,b.menuname parentname
from (select * from sys_menu connect by prior id = parentID start with parentID is null order by id) a,sys_menu b
where a.parentID = b.id or (b.parentID is null and a.parentID is null)
<HTML>
<HEAD>
<TITLE>PL/SQL Developer Export</TITLE>
</HEAD>
<BODY>
<TABLE BORDER="1">
<TR><TH>ID</TH><TH>MENUNAME</TH><TH>MENUPARENT</TH><TH>PARENTNAME</TH></TR>
<TR><TD>0001</TD><TD>系统功能维护</TD><TD> </TD><TD>测试菜单</TD></TR>
<TR><TD>0001</TD><TD>系统功能维护</TD><TD> </TD><TD>系统功能维护</TD></TR>
<TR><TD>0001</TD><TD>系统功能维护</TD><TD> </TD><TD>信息发布管理</TD></TR>
<TR><TD>000101</TD><TD>系统菜单管理</TD><TD>0001</TD><TD>系统功能维护</TD></TR>
<TR><TD>000102</TD><TD>系统用户管理</TD><TD>0001</TD><TD>系统功能维护</TD></TR>
<TR><TD>000103</TD><TD>组织机构管理</TD><TD>0001</TD><TD>系统功能维护</TD></TR>
<TR><TD>000104</TD><TD>权限管理</TD><TD>0001</TD><TD>系统功能维护</TD></TR>
<TR><TD>000105</TD><TD>数据字典管理</TD><TD>0001</TD><TD>系统功能维护</TD></TR>
<TR><TD>000106</TD><TD>日志管理</TD><TD>0001</TD><TD>系统功能维护</TD></TR>
<TR><TD>0002</TD><TD>信息发布管理</TD><TD> </TD><TD>测试菜单</TD></TR>
<TR><TD>0002</TD><TD>信息发布管理</TD><TD> </TD><TD>系统功能维护</TD></TR>
<TR><TD>0002</TD><TD>信息发布管理</TD><TD> </TD><TD>信息发布管理</TD></TR>
<TR><TD>0003</TD><TD>测试菜单</TD><TD> </TD><TD>测试菜单</TD></TR>
<TR><TD>0003</TD><TD>测试菜单</TD><TD> </TD><TD>系统功能维护</TD></TR>
<TR><TD>0003</TD><TD>测试菜单</TD><TD> </TD><TD>信息发布管理</TD></TR>
<TR><TD>000301</TD><TD>test2</TD><TD>0003</TD><TD>测试菜单</TD></TR>
<TR><TD>000302</TD><TD>test3</TD><TD>0003</TD><TD>测试菜单</TD></TR>
<TR><TD>000303</TD><TD>test4</TD><TD>000302</TD><TD>test3</TD></TR>
<TR><TD>000304</TD><TD>test5</TD><TD>000303</TD><TD>test4</TD></TR>
<TR><TD>000305</TD><TD>test6</TD><TD>000304</TD><TD>test5</TD></TR>
<TR><TD>000306</TD><TD>test7</TD><TD>000305</TD><TD>test6</TD></TR>
<TR><TD>000307</TD><TD>test8</TD><TD>000306</TD><TD>test7</TD></TR>
<TR><TD>000309</TD><TD>test9</TD><TD>000305</TD><TD>test6</TD></TR>
<TR><TD>000310</TD><TD>test10</TD><TD>000309</TD><TD>test9</TD></TR>
</TABLE>
</BODY>
</HTML>
ID MENUNAME MENUPARENT PARENTNAME
0001 系统功能维护 系统功能维护
000101 系统菜单管理 0001 系统功能维护
000102 系统用户管理 0001 系统功能维护
000103 组织机构管理 0001 系统功能维护
000104 权限管理 0001 系统功能维护
000105 数据字典管理 0001 系统功能维护
000106 日志管理 0001 系统功能维护
0002 信息发布管理 系统功能维护
0003 测试菜单 系统功能维护
0001 系统功能维护 测试菜单
0002 信息发布管理 测试菜单
0003 测试菜单 测试菜单
000301 test2 0003 测试菜单
000302 test3 0003 测试菜单
000303 test4 000302 test3
000304 test5 000303 test4
000305 test6 000304 test5
000306 test7 000305 test6
000309 test9 000305 test6
000307 test8 000306 test7
000310 test10 000309 test9
0001 系统功能维护 信息发布管理
0002 信息发布管理 信息发布管理
0003 测试菜单 信息发布管理
你測試數據呢(sys_menu原數據)
1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录
delete from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)3、查找表中多余的重复记录(多个字段)
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录
>select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
select views.*
from( select b.name,a.*,
row_number() over (partition by a.username order by a.changedate desc) rn
from maxuserstatus a,labor b where b.laborcode=a.username
) views
where views.rn=1
ID MENUNAME MENUPARENT PATH
000102 系统用户管理 0001 /system/SysUserManage.action
0001 系统功能维护
000101 系统菜单管理 0001 ../system/sysMenuManage.action
000103 组织机构管理 0001 SysGroupManage.action
000104 权限管理 0001
000105 数据字典管理 0001
000106 日志管理 0001 SysLogManage.action
0003 测试菜单
000301 test2 0003
000302 test3 0003
000303 test4 000302
000304 test5 000303
000305 test6 000304
000306 test7 000305
000307 test8 000306
000309 test9 000305
000310 test10 000309
0002 信息发布管理 加了group by还是不行
主要是根数据会重复
from (select * from sys_menu connect by prior id = parentID start with parentID is null order by id) a,sys_menu b
where a.parentID = b.id or (b.parentID is null and a.id = b.id)
order by a.id
俺来说说比解另类地解法吧
select a.id,a.menuname,a.menuparent,b.menuname parentname from
(select id,menuname,nvl(menuparent,id) menuparent from sys_menu) a,sys_menu b
where a.menuparent = b.id order by a.id,a.menuname作人,要变通嘛
select t.*, t.rowid
from table t
where t.rowid >= (select max(z.rowid)
from table z
where z.重复字段 = t.重复字段)
(select menuname from sys_menu b where b.id = a.menuparent) parentname
from sys_menu;