树型用户结构的查询问题（高分请教）

[clapton]

UserTable GroupTable
----------- -------------
uid gid
gid pid

我有一个树型结构的用户关系，对应的数据库表结构如图所示，每一个User有一个gid指定其所属的Group，每一个Group有一个pid指定其上级Group

现在的问题是，能否只通过Sql语句获得某一个Group及其以下组的所有User的uid集合,并用于指定uid范围内的查询？如SELECT ... FROM ... WHERE ... IN (<这个uid集合>)?

[steven_cheng]

207(大江)说的：
我以前做无穷树的时候也是用父ID方式，我使用两层循环（vector的外层循环不断随着搜索而改变长度）或递归实现
==================================================================================

这个也就是我说的那种方法，两层循环。
如果用递归实现，你要考虑系统的资源消耗和效率。
我认为如果不动表结构，两层循环是最有效的解决办法。

[clapton]

pxboy涨了个三角吧今天？
uid我是在显示树形结构的同时，顺便得到的

[clapton]

两层循环可否生成树结构？好像不行吧？我即要生成树型结构，又需要获得全部子节点集合

[steven_cheng]

我觉得生成树和获得全部子节点完全是两件事儿。你为什么要捆在一起？
生成树一般都是生成整棵树，找到子节点不一定从根找起。所以我觉得还是分开好。

[pxboy]

你的数据结构似乎没设计好，
GroupTable里为什么不用010101这样的形式分层？这样就可以使用一条语句了
SELECT uid,gid FROM UserTable WHERE gid like '01' + '%' and uid IN (uid集合)
以你现在这样的结构，我想是不是只有先在程序里找到GroupTable里某个gid下的所有的子Group再去使用select查询了，你这关键是找子Group

[favourl]

select * from UserTable,GroupTable where UserTable.gid=GroupTable.gid and uid in(……)

[thefishwilldie]

不知道什么意思，SELECT ... FROM ... WHERE ... IN (<这个uid集合>)?
SELECT ... FROM ... WHERE pid=uid

应该可以的啊，

[clapton]

可能是我没表达清楚，我需要获得的uid集合是某个Group的所有子Group，和其子Group的子Group……所包括的所有uid的集合。
这个集合将被用做其他查询的查询条件。


或者……我自己也有点晕了……简化一下问题吧：求：Group树中某个node的所有子孙（就是只要获得某Group的所有各级子Group, <b>只用SQL语句可否实现？</b>）

>_<!

[clapton]

----
pxboy:
你这关键是找子Group
----
好像是这样，能否只用sql实现呢？实在不行只好写存储过程做递归了……

[pxboy]

要我做我就改表结构用010101这样的形式分层，好处多多，似乎没有一条SQL语句可以解决的方案，期待高手出现...

[雪里风火]

这种方式的话，查询找起来比较麻烦。

[steven_cheng]

呵呵，这玩意儿怎么越看越眼熟：）
可以用两步完成：
1、找到所有子组（包括n级子组）。
2、找出这些组的所有成员。（select .. from ..where in(<所有组和子组>）
第一步可以这样实现：
取出所有GroupTable对象，放入数组（a1）
做一个动态增长数组(a2，如ArrayList)，把要查找的组放入
int i = 0;
for(;i<a2.length;i++)
{
for(int j=0;j<a1.length;i++)
{
如果是a2当前对象（组）的子组（直接子组，也就是a1的pid=a2的gid）
把它放入a2中;

}
}
a2就是当前组的所有子组，然后你就可以拼一个字符串了。

[pxboy]

兄弟，子组还有子组的

[steven_cheng]

是啊。所以我用一个二重循环，先找到直接子组，然后找到子组的子组，继续下去，不管有多少层，经过这个二重循环就一定能全部找到。
注意，外层循环是一个变长的。

[shipp]

递归查询
群组下子组对应的连接表：TBL_GROUPSUPER
群组下个人对应的连接表：TBL_PERSONGROUP

/**
* 得到所有的叶子(个人ID)
* @param groupid 群组ID注意：groupid前面必须有G
* @return 所有的叶子(个人ID)
*/
static public Vector getSonGroup(String groupid)
{
if (groupid == null || groupid.trim().charAt(0) != 'G')
{
return null;
}
Vector retvector = new Vector();
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
if (ButtonType.DEBUGSWITCH) //test
{
System.out.println("groupid：" + groupid);
}
String strSql = getSQL(groupid.substring(1, groupid.length()));
Vector tmpvector = new Vector();
DBConnectionManager connMgr = DBConnectionManager.getInstance();
try
{
conn = connMgr.getConnection();
//Class.forName( "com.microsoft.jdbc.sqlserver.SQLServerDriver" );
//conn = DriverManager.getConnection("jdbc:microsoft:sqlserver://192.168.0.37;User=sa;Password=;DatabaseName=PortalDB_new;SelectMethod=cursor","sa","" );
if (conn == null) //如果得不到数据库连接
{
return null;
}
stat = conn.createStatement();
rs = stat.executeQuery(strSql);
while (rs.next())
{ //得到标志+ID 如:G001;P001等
tmpvector.add(rs.getString("FLAG") + rs.getString("SON"));
}
if (tmpvector == null)
{
return null;
}
else
{
retvector = getAllSon(tmpvector); //得到所有的叶子(个人ID)
}
}
catch (Exception ex)
{
if (ButtonType.DEBUGSWITCH) //test
{
ex.printStackTrace();
}
retvector = null;
String strErrorMsg = ex.getMessage();
if (strErrorMsg.indexOf(TypeConstants.DB_RESET) > 0)
{
connMgr.destroyAll();
// el.writeOperLog("重新加载数据库连接池！");
}

}
finally
{
try
{
if (rs != null)
{
rs.close();
rs = null;
}
if (stat != null)
{
stat.close();
stat = null;
}
}
catch (SQLException sqle)
{
if (ButtonType.DEBUGSWITCH) //test
{
sqle.printStackTrace();
}

retvector = null;
}
if (conn != null)
{
connMgr.freeConnection(conn);
}
}
return retvector;
}

[shipp]

/**
* 递归函数得到叶子(个人ID)
* @param vector 包含子组的数组
* @return 解析了下层组的数组
*/
static private Vector getAllSon(Vector vector)
{
if (vector == null)
{
return null;
}
Vector retvector = new Vector();

int iCount = vector.size();
String sTmpID = "";
for (int i = 0; i < iCount; i++)
{
sTmpID = (String) vector.elementAt(i);
if (sTmpID.charAt(0) == 'P') //如果是个人,添加到返回数组中
{
retvector.add(sTmpID);
}
else if (sTmpID.charAt(0) == 'G') //如果是群组
{
sTmpID = sTmpID.substring(1, sTmpID.length()); //得到群组ID

Connection conn2 = null;
Statement stat = null;
ResultSet rs = null;
Vector tmpvector = new Vector();
String sTmpSon = "";
int iTmpCount = 0;
boolean bool = false; //是否还有子组的标志

//得到查询得sql语句
String strSql = getSQL(sTmpID);
DBConnectionManager connMgr = DBConnectionManager.getInstance();
try
{
conn2 = connMgr.getConnection();
//Class.forName( "com.microsoft.jdbc.sqlserver.SQLServerDriver" );
//conn = DriverManager.getConnection("jdbc:microsoft:sqlserver://192.168.0.37;User=sa;Password=;DatabaseName=PortalDB_new;SelectMethod=cursor","sa","" );

if (conn2 == null)
{
return null;
}
stat = conn2.createStatement();
rs = stat.executeQuery(strSql);
while (rs.next())
{ //加入所有的
sTmpSon = rs.getString("FLAG") + rs.getString("SON");
if (sTmpSon.charAt(0) == 'P') //个人,添加到返回数组中
{
retvector.add(sTmpSon);
}
else if (sTmpSon.charAt(0) == 'G') //如果还有儿子组,添加到临时数组(存储组成员"G")
{
bool = true;
tmpvector.add(sTmpSon);
}
}
if (bool) //如果还有儿子组,则继续循环查询
{
Vector tmpvector2 = null;
//根据存储组"G"临时数组,得到所有的叶子(个人ID数组)
tmpvector2 = getAllSon(tmpvector);

//将得到的叶子("P"个人ID加入到返回数组中)
if (tmpvector2 != null)
{
int itmpsize = tmpvector2.size();
for (int j = 0; j < itmpsize; j++)
{
retvector.add( (String) tmpvector2.elementAt(j));
}
}
}
//bool = false;//重置标志
}
catch (Exception ex)
{
if (ButtonType.DEBUGSWITCH) //test
{
ex.printStackTrace();
}
retvector = null;
String strErrorMsg = ex.getMessage();
if (strErrorMsg.indexOf(TypeConstants.DB_RESET) > 0)
{
connMgr.destroyAll();
// el.writeOperLog("重新加载数据库连接池！");
}

}
finally
{
try
{
if (rs != null)
{
rs.close();
rs = null;
}
if (stat != null)
{
stat.close();
stat = null;
}
}
catch (SQLException sqle)
{
if (ButtonType.DEBUGSWITCH) //test
{
sqle.printStackTrace();
}

retvector = null;
}
if (conn2 != null)
{
connMgr.freeConnection(conn2);
}
}
}
}
return retvector;
}

/**
* 得到查询的sql语句
* @param groupid 群组ID(没有前缀标志"G")
* @return
*/
static private String getSQL(String groupid)
{
int igroupid = 0;
try
{
igroupid = Integer.parseInt(groupid);
}
catch (Exception ex)
{
igroupid = 0;
}

StringBuffer strSql = new StringBuffer();
strSql.append("SELECT SUPERGROUPID AS FATHER,GROUPID AS SON,FLAG FROM");
strSql.append(" ( ");
strSql.append(" SELECT SUPERGROUPID,GROUPID,'G' AS FLAG FROM TBL_GROUPSUPER ");
strSql.append(" UNION ALL ");
strSql.append(" SELECT GROUPID,ID,'P' AS FLAG FROM TBL_PERSONGROUP");
strSql.append(" ) AS A ");
strSql.append(" WHERE SUPERGROUPID='" + igroupid + "'");
return strSql.toString();
}
}

[pxboy]

现在没有办法一条SQL语句解决的方案了，为什么不用[##][##][##]这样的形式分层？这样就可以使用一条语句了
SELECT uid,gid FROM UserTable WHERE gid like '01' + '%' and uid IN (uid集合)
这样好处真的是很多，原来我也喜欢用父ID的方式，其实感觉很麻烦

[207]

pxboy说说你的[##][##][##]这样的形式分层具体怎样实现，请指教一下

[clapton]

－－－－－－－－－－－
我觉得生成树和获得全部子节点完全是两件事儿。你为什么要捆在一起？
生成树一般都是生成整棵树，找到子节点不一定从根找起。所以我觉得还是分开好。
－－－－－－－－－－－

确实是两件事，但是我生成树的时候一定会得到所有子节点，反过来获得全部子节点却不能得到树结构。
我只是在生成树的时候“顺便”获得全部子节点

[clapton]

另外，想明确地问一下
SELECT FROM WHERE uid IN ('user1','user2','user3'.......即获得的全部子uid的集合拼出的字符串)
括号里的东西太长了是否会有问题？