如何计算和总和同桌成为父数据集 [英] How To Count and Sum in Same Table To Become Parent Dataset

问题描述

这是从<一个延伸href=\"http://stackoverflow.com/questions/35171909/how-to-query-top-three-reference-id-only-and-how-i-can-query-until-3-level-group\">$p$pvious问题就查询前三名参考ID。由于文化程度一栏变成动态的，我需要保持它，而在GridView的显示。

This is extended from previous question regarding Query Top Three Reference ID. Since "level" column become dynamic and I need to keep it while display on gridview.

我的计划建立2数据集（父母与子女）。

My plan to create 2 dataset (Parent and Child).

1）

关于儿童数据集已经有了答案如下图所示（感谢鲁道夫）： -

Regarding Child Dataset already have a answer (thanks to Rudolf) like below:-

表测试

ID  Name    RefID
A   AAAA    null
B   BBBB    A
C   CCCC    B
D   DDDD    C
E   EEEE    D
F   FFFF    E

SQL语句

select ID, 'Level(' + cast(level as varchar(255)) + ')' GroupLevel, RefID from (
      select t0.ID, 
        case level.level
          when 1 then t1.ID
          when 2 then t2.ID
          when 3 then t3.ID 
        end RefID,
        level.level
      from Test t0
        left outer join Test t1 on t1.RefID = t0.ID
        left outer join Test t2 on t2.RefID = t1.ID
        left outer join Test t3 on t3.RefID = t2.ID
        cross join (
          select 1 level union all
          select 2 union all
          select 3
        ) level
    ) t
    where t.RefID is not null

输出：

ID | GroupLevel | RefID |
---+------------+-------+
A  | Level(1)   | B     |
A  | Level(2)   | C     |
A  | Level(3)   | D     |
B  | Level(1)   | C     |
B  | Level(2)   | D     |
B  | Level(3)   | E     |
C  | Level(1)   | D     |
C  | Level(2)   | E     |
D  | Level(1)   | E     |

现在我需要像下面创建父数据集： -

Now I need to create Parent Dataset like below:-

与GridView的输出+和 -

Output in GridView with "+" and "-"

Introducer  Level      Total Id
A           Level (1)      2
-   Id  Name
    --  -----
    B   BBBB
    C   CCCC

A           Level(2)       1
-   Id  Name
    --  -----
    D   DDDD

在这之前，我需要创建COUNT（*）的SQL语句来获得上述合计基于ID的表。我尝试了很多的时间来把它放在鲁道夫语句，但可以得到正确的总评分： -

Before that, I need to create sql statement with Count(*) to get total id based table above. I try many time to place it on Rudolf statement but can get total correctly:-

select ID, 
'Level(' + cast(level as varchar(255)) + ')' GroupLevel, 
RefID,

*second try - result error
     count(*) as reccount* 

from (
      select t0.ID, 
        case level.level
          when 1 then t1.ID
          when 2 then t2.ID
          when 3 then t3.ID 
        end RefID,

*first try - result count double
           count(*) as reccount*

        level.level
      from Test t0
        left outer join Test t1 on t1.RefID = t0.ID
        left outer join Test t2 on t2.RefID = t1.ID
        left outer join Test t3 on t3.RefID = t2.ID
        cross join (
          select 1 level union all
          select 2 union all
          select 3
        ) level
    ) t
    where t.RefID is not null

我可以计算出在哪里把计数语句来获得总战绩基于级别组。

I can figure out where to put count statement to get total record based level group.

2）

我还需要从任何相关表中添加列，总和值。让说我有每个ID表子象下面这样： -

I also need to add column and sum value from any related table. Let say I have table child for each id like below:-

表儿童

ID  Date Join   Child
B   XX/XX/XXXX  2
C   XX/XX/XXXX  5

我的愿望输出应该是： -

My desire output should be:-

Introducer  Level      Total Id Total Child 
A           Level (1)   2        7  
-   Id  Name    Date Join   Child
    --  ----    ---------   -----
    B   BBBB    XX/XX/XX    2
    C   CCCC    XX/XX/XX    5
A           Level(2)    1        0  
-   Id  Name    Date Join   Child
    --  ----    ---------   ------
    D   DDDD    XX/XX/XX    Null

我非常AP preciate和感谢提前上为你的时间和阅读此线程，并希望有人越过这个问题有关于这个问题，以解决方案为请与我分享。

I am very appreciate and thanks on advance for yours time and read this thread and hope anybody cross this problem have a solutions regarding this questions please share with me.

谢谢你。

推荐答案

我找到了问题答案没有2

I found answer for question no 2

的我还需要从任何相关表中添加列，总和值让我们说我有每个ID表子象下面这样： - 的

表儿童

ID  Date Join   Child
B   XX/XX/XXXX  2
C   XX/XX/XXXX  5

在sql语句我把： -

On sql statement I put:-

.....
(Select
      1 As level
    union All
    Select
      2
    union All
    Select
      3) level) t Left Join
  child On t.RefID = child.memberid
......

在那里我得到我想要的输出象下面这样： -

where I get my desired output like below:-

Id  Name    Date Join   Child
--  ----    ---------   -----
B   BBBB    XX/XX/XX    2
C   CCCC    XX/XX/XX    5

使用的左加入我可以扫描并显示所有表REFID有关儿童要么存在于没有子集。

By using Left Join I can scan and show all Table Child related with RefID either exist on not as Child dataset.

我还需要证明孩子= 0 如果没有找到记录的表儿童而不是空或空值。

I still need to show child = 0 if record not found in Table Child instead of null or blank value.

我，如果你都可以改善这种解决方案非常AP preciate。也许有其他的sql语句更有效，比这更快。

I am very appreciate if yours all can improve this solutions. Maybe have others sql statement more effective and faster than this.

和，请帮我找出有关COUNT和SUM在父数据集。我仍然不知道如何解决这个问题呢。

And, please help me to figure out regarding COUNT and SUM on Parent dataset. I still no idea how to overcome this problem yet.

谢谢你。

这篇关于如何计算和总和同桌成为父数据集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！