SQL Server中的层次结构查询 [英] Hierarchy query in Sql sever

问题描述

我正在处理名为FUT_USR_DETAILS的表
该表具有child_id，Parent_id，Org_id
列 这些列具有父子关系.组织ID是超级父代.
假设组织ID 1是祖父母，父母ID 1是父母(第一次组织ID在创建父ID时也将是parent_id)，而子ID是child1
如果子级id1创建用户，则它成为父级，并且其条目进入父级id"列，但对于该条目，其组织ID"列将具有与子级id1为子级时相同的值. 对于所有子代，孙代，grand_grandchildren等，组织ID将具有相同的值.

我想编写一个查询以获取特定用户的层次结构计数级别.
从祖父母(组织ID)开始，该组织ID有多少个世代级别.
我写了一个查询来查找此计数.但我需要在查询中明确指定每个RG ID.此查询一次不适用于整个表.查询是

I m working on table called FUT_USR_DETAILS
this table have columns child_id,Parent_id,Org_id
these columns have parent child relationship. org id is super parent.
suppose org id 1 is grand parent, parentid 1 is parent(for first time org id will be parent_id also as it creates parent id) and child id is child1
if child id1 creates user it becomes parent and it''s entry goes in parentid column but for this entry it''s org id column will have same value as in case when child id1 was child.
for all children, grandchildren,grand_grandchildren,so on, the org id will have same value.

i want to write a query to get level of count of hierarchy of particular user.
that is starting from grandparent(org id) how many generation levels are present for that org id .
i wrote a query to find this count. but i need to explicitly specify each rg id in query. this query does not work for entire table at time. query is

SELECT count(*)
FROM EXTDBA.fut_user_details
WHERE UD_ORG_ID ='F00761'  //here i m specifying org id explicitly 
AND UD_child_ID  IN
  (SELECT UD_PARENTID FROM EXTDBA.fut_user_details WHERE UD_ORG_ID ='F00761'
  )
AND UD_child_ID!=UD_ORG_ID;

如果我想以编程方式做到这一点，我会这样做.
从表中选择不同的组织ID.放在数组中
数组中每个项目的anf，即每个组织ID，我将运行llop
喜欢

programmatically if i want to do this i''ll do it like this.
select distinct org id from table. take it in array
anf for each item in array ie for each org id i''ll run for llop
like

for(i=0;i<arrorgid.count;i++)>
{
SELECT count(*)
FROM EXTDBA.fut_user_details
WHERE UD_ORG_ID =arrOrgId[i]  //here i m specifying org id explicitly 
AND UD_child_ID  IN
  (SELECT UD_PARENTID FROM EXTDBA.fut_user_details WHERE UD_ORG_ID =arrOrgId[i]
  )
AND UD_child_ID!=UD_ORG_ID;

}

，但我被要求通过直接查询数据库来做到这一点.
有人可以告诉我怎么做吗

but i m asked to do it by quering database directly.
can someone tell me how to do so

推荐答案

有几种方法可以做到这一点:

0)以逗号分隔的字符串将ID传递给存储过程，然后在该存储的proc中，将ID字符串解析为临时表，然后使用临时表和实际数据表进行查询.

1)执行一系列查询(每个ID一次)，然后在调用存储的proc
的代码中将合并的数据集合并在一起
2)创建一个存储过程，该过程接受有限数量的ID参数(我怀疑是整数)，并使用这些ID执行查询，并根据需要多次调用此存储过程，将返回的数据集合并到代码中. >
就个人而言，我会选择第一个选项.

There are a few ways you can do this:

0) Pass the ID''s to a stored procedure in a comma-delimited string, and in that stored proc, parse the string of IDs into a temporary table, and then perform a query using the temp table together with your actual data table.

1) Perform a series of queries, one for each ID, and merge the retruned datasets together in the code that called the stored proc

2) Create a stored proc that accepts a limted number of ID parameters (integers I suspect), and performs a query using those IDs, and call this stored proc as many times as necessary, merging the returned datasets in your code.

Personally, I''d go with the first option.

我不明白orgid是第一级的父级.您正在将用户与组织混为一谈.
让orgid定义组织单位或部门等，并使用普通的id/parentid模式，其中顶层具有< null>.作为父母.

无论如何，请尝试以下查询:

I don''t get the point with the orgid being the parentid for the first level. You''re mixing up users with organisations.
Let the orgid define the organisational unit or department and so on, and use a normal id/parentid schema where the toplevel has <null> as the parentid.

Anyway, try this query:

SELECT  UD_child_ID,UD_PARENTID,LEVEL
FROM    EXTDBA.fut_user_details
CONNECT BY PRIOR UD_child_ID = UD_PARENTID
START WITH UD_PARENTID = 'F00761'

这篇关于SQL Server中的层次结构查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！