关于sql统计问题 --急 ---100分---

Question

表：t_wf_deploy
m_id                                 m_file
----------------------------------------------------------
40288496254cef4701254d1e04140007 wf/xml/需求管理.xml
40288496254d63d201254d8ba498004b wf/xml/周报管理流程.xml
4028849d254d2fc901254d4ab6570018 wf/xml/测试管理.xml
4028849d254d2fc901254d5eee8c0028 wf/xml/代码走查.xml
4028849d254e8a7601254f44174b0041 wf/xml/值日流程.xml表：t_wf_list
m_id                                 m_deployid
-----------------------------------------------------------------
4028849925c368220125c3add3400052 4028849d254d2fc901254d5eee8c0028
4028849925c368220125c3ae680e005e 4028849d254d2fc901254d5eee8c0028表：t_wf_node
m_id                                 m_wfid                              m_nodeid
------------------------------------------------------------------------------------
4028849925c368220125c3add3400053 4028849925c368220125c3add3400052 daimaZC_1259722551031_0
4028849925c368220125c3add35f005c 4028849925c368220125c3add3400052 daimaZC_1259722551031_1
4028849925c368220125c3ae680e005f 4028849925c368220125c3ae680e005e daimaZC_1259722551031_0
4028849925c368220125c3ae681d0068 4028849925c368220125c3ae680e005e daimaZC_1259722551031_1
4028849925d2bd080125d2e917d60006 4028849925c368220125c3add3400052 daimaZC_1259722551031_2表：t_wf_data
m_id     m_nodeid                             m_fields                   m_value
----------------------------------------------------------------------------------------
1 4028849925c368220125c3add3400053 daimaZC_1259722551031_0_10
2 4028849925c368220125c3add3400053 daimaZC_1259722551031_0_8 40288184254a1ac301254f9c16350040
3 4028849925c368220125c3add3400053 daimaZC_1259722551031_0_7 项目管理
4 4028849925c368220125c3add3400053 daimaZC_1259722551031_0_6 2
5 4028849925c368220125c3add3400053 daimaZC_1259722551031_0_5 2
6 4028849925c368220125c3add3400053 daimaZC_1259722551031_0_4 2009-12-25 10:32:06
7 4028849925c368220125c3add3400053 daimaZC_1259722551031_0_3 40288184254a1ac301254f70202e0029
8 4028849925c368220125c3add3400053 daimaZC_1259722551031_0_11 无备注
9 4028849925c368220125c3ae680e005f daimaZC_1259722551031_0_10
10 4028849925c368220125c3ae680e005f daimaZC_1259722551031_0_8 40288184254a1ac301254f9cb2560044
11 4028849925c368220125c3ae680e005f daimaZC_1259722551031_0_7 项目管理
12 4028849925c368220125c3ae680e005f daimaZC_1259722551031_0_6 2
13 4028849925c368220125c3ae680e005f daimaZC_1259722551031_0_5 1
14 4028849925c368220125c3ae680e005f daimaZC_1259722551031_0_4 2009-12-25 10:32:51
15 4028849925c368220125c3ae680e005f daimaZC_1259722551031_0_3 40288184254a1ac301254f70202e0029
16 4028849925c368220125c3ae680e005f daimaZC_1259722551031_0_11 还是无备注
17 4028849925c368220125c3add35f005c daimaZC_1259722551031_1_4 2009-12-28 09:31:30
18 4028849925c368220125c3add35f005c daimaZC_1259722551031_1_3 40288184254a1ac301254f9c16350040
19 4028849925c368220125c3add35f005c daimaZC_1259722551031_1_7 1
20 4028849925c368220125c3add35f005c daimaZC_1259722551031_1_6
21 4028849925c368220125c3add35f005c daimaZC_1259722551031_1_5 40288184254a1ac301254f6d40a5001c
22 4028849925d2bd080125d2e917d60006 daimaZC_1259722551031_2_6
23 4028849925d2bd080125d2e917d60006 daimaZC_1259722551031_2_5 完成
24 4028849925d2bd080125d2e917d60006 daimaZC_1259722551031_2_4 2009-12-28 09:33:17
25 4028849925d2bd080125d2e917d60006 daimaZC_1259722551031_2_3 40288184254a1ac301254f70202e0029
     表关系：t_wf_deploy表的m_id与t_wf_list表的m_deployid关联，t_wf_list表的m_id与t_wf_node表的m_wfid关联，t_wf_node表m_id与t_wf_data的m_nodeid关联，t_wf_node表中m_nodeid是xml的一个节点id，t_wf_data表的m_fields是某个节点下field的id，m_value是其（field）对应的值。需求：已知t_wf_deploy表m_id：4028849d254d2fc901254d5eee8c0028，统计t_wf_data表中m_fields下daimaZC_1259722551031_0_7对应值（m_value）为“项目管理”的daimaZC_1259722551031_0_5和daimaZC_1259722551031_0_6的值（m_value），并以 m_fields为daimaZC_1259722551031_0_3所对应的m_value值(40288184254a1ac301254f70202e0029)分组。希望得到结果
daimaZC_1259722551031_0_5  daimaZC_1259722551031_0_6
-----------------------------------------------------
1                          2
2                          2
望高手指点，分不够我再开贴给， 有不明白的请联系我，我的qq：309632173 

Answer 1

有点棘手，一般的sql我就自己k了，这个是流程开发器配置出来的，用的是xml文件
<process fieldHeight="20" id="daimaZC_1259722551031" name="代码走查" type="multi-page" version="1"> 
  <node className="" fieldHeight="0" id="daimaZC_1259722551031_1" index="" name="走查结果提交" type="common" x="249" y="44"> 
    <forward> 
      <nodeStrategy fields="" strategy="daimaZC_1259722551031_2" type="constant"/>  
      <roleStrategy fields="daimaZC_1259722551031_0_8" strategy="" type="simple"/> 
    </forward>  
    <layout type="grid"> 
      <column size="2"/>  
      <row size="3"/> 
    </layout>  
    <field alignHorizontal="left" alignVertical="middle" column="1" height="0" id="daimaZC_1259722551031_1_6" input="upload" labelWidth="0" name="上传附件" row="2" valueFrom="标签" valueFromType=""> 
      <trigger/> 
    </field> 

    <field alignHorizontal="left" alignVertical="middle" column="1" height="0" id="daimaZC_1259722551031_1_3" input="text" labelWidth="0" name="走查人" row="1" valueFrom="标签" valueFromType="" > 
      <trigger/> 
    </field>      <field alignHorizontal="left" alignVertical="middle" column="2" height="0" id="daimaZC_1259722551031_1_4" input="datenow" labelWidth="0" name="走查日期" row="1" valueFrom="" valueFromType=""> 
      <trigger/> 
    </field>  
    <field alignHorizontal="left" alignVertical="middle" column="1" height="0" id="daimaZC_1259722551031_1_7" input="textarea" labelWidth="0" name="结果说明" row="3" valueFrom="请输入" valueFromType=""  maxLength="100"> 
      <trigger/> 
    </field>  
    <field alignHorizontal="left" alignVertical="middle" column="2" height="0" id="daimaZC_1259722551031_1_5" input="userchoice" labelWidth="0" name="选择修改人" row="2" valueFrom="单击选择" valueFromType=""> 
      <trigger/> 
    </field> 
  </node>  
  <node formNode="" id="daimaZC_1259722551031_3" name="结 束" type="end" x="145" y="253"> 
    <forward> 
      <nodeStrategy strategy=""/> 
    </forward> 
  </node>  
  <node className="" fieldHeight="0" id="daimaZC_1259722551031_0" index="" name="发起代码走查" type="start" x="65" y="22"> 
    <forward> 
      <nodeStrategy fields="" strategy="daimaZC_1259722551031_1" type="constant"/>  
      <roleStrategy fields="daimaZC_1259722551031_0_3" strategy="" type="simple"/> 
    </forward>  
    <layout type="grid"> 
      <column size="2"/>  
      <row size="5"/> 
    </layout>  
    <field alignHorizontal="left" alignVertical="middle" column="2" height="0" id="daimaZC_1259722551031_0_4" input="datenow" labelWidth="0" name="发起日期" row="1" valueFrom="" valueFromType=""> 
      <trigger/> 
    </field>  
    <field alignHorizontal="left" alignVertical="middle" column="1" height="0" id="daimaZC_1259722551031_0_3" input="text" labelWidth="0" name="发起人" row="1" valueFrom="标签" valueFromType=""> 
      <trigger/> 
    </field>  
    <field alignHorizontal="left" alignVertical="middle" column="2" height="0" id="daimaZC_1259722551031_0_8" input="userchoice" labelWidth="0" name="负责人" row="3" valueFrom="单击选择" valueFromType=""> 
      <trigger/> 
    </field>  
    <field alignHorizontal="left" alignVertical="middle" column="1" height="0" id="daimaZC_1259722551031_0_11" input="textarea" labelWidth="0" name="备注" row="5" valueFrom="请输入" valueFromType=""> 
      <trigger/> 
    </field>  
    <field alignHorizontal="left" alignVertical="middle" column="1" height="0" id="daimaZC_1259722551031_0_5" input="select" labelWidth="0" name="项目名" row="2" valueFrom="[1,'SMAX']:[2,'SMX']" valueFromType="const"> 
      <trigger/> 
    </field>  
    <field alignHorizontal="left" alignVertical="middle" column="1" height="0" id="daimaZC_1259722551031_0_10" input="upload" labelWidth="0" name="附件上传" row="4" valueFrom="标签" valueFromType=""> 
      <trigger/> 
    </field>  
    <field alignHorizontal="left" alignVertical="middle" column="1" height="0" id="daimaZC_1259722551031_0_7" input="input" labelWidth="0" name="模块名" row="3" valueFrom="" valueFromType=""> 
      <trigger/> 
    </field>  
    <field alignHorizontal="left" alignVertical="middle" column="2" height="0" id="daimaZC_1259722551031_0_6" input="select" labelWidth="0" name="版本号" row="2" valueFrom="[1,'A版本']:[2,'B版本']" valueFromType="const"> 
      <trigger/> 
    </field> 
  </node>  
  <node className="" fieldHeight="0" id="daimaZC_1259722551031_2" index="" name="修改结果" type="common" x="286" y="212"> 
    <forward> 
      <nodeStrategy fields="" strategy="daimaZC_1259722551031_3" type="constant"/>  
      <roleStrategy fields="daimaZC_1259722551031_0_3" strategy="" type="simple"/> 
    </forward>  
    <layout type="grid"> 
      <column size="2"/>  
      <row size="3"/> 
    </layout>  
    <field alignHorizontal="left" alignVertical="middle" column="1" height="0" id="daimaZC_1259722551031_2_3" input="text" labelWidth="0" name="修改人" row="1" valueFrom="标签" valueFromType=""> 
      <trigger/> 
    </field>  
    <field alignHorizontal="left" alignVertical="middle" column="2" height="0" id="daimaZC_1259722551031_2_4" input="datenow" labelWidth="0" name="修改时间" row="1" valueFrom="" valueFromType=""> 
      <trigger/> 
    </field>  
    <field alignHorizontal="left" alignVertical="middle" column="1" height="0" id="daimaZC_1259722551031_2_6" input="upload" labelWidth="0" name="上传附件" row="2" valueFrom="请输入" valueFromType=""> 
      <trigger/> 
    </field>  
    <field alignHorizontal="left" alignVertical="middle" column="1" height="0" id="daimaZC_1259722551031_2_5" input="textarea" labelWidth="0" name="修改说明" row="3" valueFrom="请输入" valueFromType=""  maxLength="100">  
      <trigger/> 
    </field> 
  </node> 
</process>
或许更能理解

Answer 2

mysql> select * from t_wf_deploy;
+----------------------------------+-------------------------+
| m_id                             | m_file                  |
+----------------------------------+-------------------------+
| 40288496254cef4701254d1e04140007 | wf/xml/需求管理.xml     |
| 40288496254d63d201254d8ba498004b | wf/xml/周报管理流程.xml |
| 4028849d254d2fc901254d4ab6570018 | wf/xml/测试管理.xml     |
| 4028849d254d2fc901254d5eee8c0028 | wf/xml/代码走查.xml     |
| 4028849d254e8a7601254f44174b0041 | wf/xml/值日流程.xml     |
+----------------------------------+-------------------------+
5 rows in set (0.00 sec)mysql> select * from t_wf_list;
+----------------------------------+----------------------------------+
| m_id                             | m_deployid                       |
+----------------------------------+----------------------------------+
| 4028849925c368220125c3add3400052 | 4028849d254d2fc901254d5eee8c0028 |
| 4028849925c368220125c3ae680e005e | 4028849d254d2fc901254d5eee8c0028 |
+----------------------------------+----------------------------------+
2 rows in set (0.00 sec)mysql> select * from t_wf_node;
+----------------------------------+----------------------------------+-------------------------+
| m_id                             | m_wfid                           | m_nodeid                |
+----------------------------------+----------------------------------+-------------------------+
| 4028849925c368220125c3add3400053 | 4028849925c368220125c3add3400052 | daimaZC_1259722551031_0 |
| 4028849925c368220125c3add35f005c | 4028849925c368220125c3add3400052 | daimaZC_1259722551031_1 |
| 4028849925c368220125c3ae680e005f | 4028849925c368220125c3ae680e005e | daimaZC_1259722551031_0 |
| 4028849925c368220125c3ae681d0068 | 4028849925c368220125c3ae680e005e | daimaZC_1259722551031_1 |
| 4028849925d2bd080125d2e917d60006 | 4028849925c368220125c3add3400052 | daimaZC_1259722551031_2 |
+----------------------------------+----------------------------------+-------------------------+
5 rows in set (0.00 sec)mysql> select * from t_wf_data;
+------+----------------------------------+----------------------------+----------------------------------+
| m_id | m_nodeid                         | m_fields                   | m_value                          |
+------+----------------------------------+----------------------------+----------------------------------+
|    1 | 4028849925c368220125c3add3400053 | daimaZC_1259722551031_0_10 | NULL                          |
|    2 | 4028849925c368220125c3add3400053 | daimaZC_1259722551031_0_8  | 40288184254a1ac301254f9c16350040 |
|    3 | 4028849925c368220125c3add3400053 | daimaZC_1259722551031_0_7  | 项目管理                         |
|    4 | 4028849925c368220125c3add3400053 | daimaZC_1259722551031_0_6  | 2                          |
|    5 | 4028849925c368220125c3add3400053 | daimaZC_1259722551031_0_5  | 2                          |
|    6 | 4028849925c368220125c3add3400053 | daimaZC_1259722551031_0_4  | 2009-12-25 10:32:06              |
|    7 | 4028849925c368220125c3add3400053 | daimaZC_1259722551031_0_3  | 40288184254a1ac301254f70202e0029 |
|    8 | 4028849925c368220125c3add3400053 | daimaZC_1259722551031_0_11 | 无备注                          |
|    9 | 4028849925c368220125c3ae680e005f | daimaZC_1259722551031_0_10 | NULL                          |
|   10 | 4028849925c368220125c3ae680e005f | daimaZC_1259722551031_0_8  | 40288184254a1ac301254f9cb2560044 |
|   11 | 4028849925c368220125c3ae680e005f | daimaZC_1259722551031_0_7  | 项目管理                         |
|   12 | 4028849925c368220125c3ae680e005f | daimaZC_1259722551031_0_6  | 2                          |
|   13 | 4028849925c368220125c3ae680e005f | daimaZC_1259722551031_0_5  | 1                          |
|   14 | 4028849925c368220125c3ae680e005f | daimaZC_1259722551031_0_4  | 2009-12-25 10:32:51              |
|   15 | 4028849925c368220125c3ae680e005f | daimaZC_1259722551031_0_3  | 40288184254a1ac301254f70202e0029 |
|   16 | 4028849925c368220125c3ae680e005f | daimaZC_1259722551031_0_11 | 还是无备注                       |
|   17 | 4028849925c368220125c3add35f005c | daimaZC_1259722551031_1_4  | 2009-12-28 09:31:30              |
|   18 | 4028849925c368220125c3add35f005c | daimaZC_1259722551031_1_3  | 40288184254a1ac301254f9c16350040 |
|   19 | 4028849925c368220125c3add35f005c | daimaZC_1259722551031_1_7  | 1                          |
|   20 | 4028849925c368220125c3add35f005c | daimaZC_1259722551031_1_6  | NULL                          |
|   21 | 4028849925c368220125c3add35f005c | daimaZC_1259722551031_1_5  | 40288184254a1ac301254f6d40a5001c |
|   22 | 4028849925d2bd080125d2e917d60006 | daimaZC_1259722551031_2_6  | NULL                          |
|   23 | 4028849925d2bd080125d2e917d60006 | daimaZC_1259722551031_2_5  | 完成                          |
|   24 | 4028849925d2bd080125d2e917d60006 | daimaZC_1259722551031_2_4  | 2009-12-28 09:33:17              |
|   25 | 4028849925d2bd080125d2e917d60006 | daimaZC_1259722551031_2_3  | 40288184254a1ac301254f70202e0029 |
+------+----------------------------------+----------------------------+----------------------------------+
25 rows in set (0.00 sec)mysql> select
    ->  (select m_value from t_wf_data where m_fields='daimaZC_1259722551031_0_5' and m_nodeid=t.m_nodeid) as daimaZC_1259722551031_0_5,
    ->  (select m_value from t_wf_data where m_fields='daimaZC_1259722551031_0_6' and m_nodeid=t.m_nodeid) as daimaZC_1259722551031_0_6
    -> from t_wf_data t
    -> where m_fields='daimaZC_1259722551031_0_7' and m_value='项目管理'
    -> and  m_nodeid in (
    ->  select t_wf_node.m_id
    ->  from t_wf_list inner join t_wf_node on t_wf_list.m_id=t_wf_node.m_wfid
    ->  where t_wf_list.m_deployid='4028849d254d2fc901254d5eee8c0028'
    -> );
+---------------------------+---------------------------+
| daimaZC_1259722551031_0_5 | daimaZC_1259722551031_0_6 |
+---------------------------+---------------------------+
| 2                         | 2                         |
| 1                         | 2                         |
+---------------------------+---------------------------+
2 rows in set (0.02 sec)mysql>

Answer 3

sorry 需求中并以 m_fields为daimaZC_1259722551031_0_3所对应的m_value值(40288184254a1ac301254f70202e0029)分组。 
这句话说的不是很准确，要得到结果应该暂时不考虑这句，