此贴是继
http://topic.csdn.net/u/20091228/15/d335bdd2-4520-4f4b-a860-02c84f3c3c30.html?1258245724
之后的新需求,表如下:表: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_3对应值(m_value)为“40288184254a1ac301254f70202e0029”
的daimaZC_1259722551031_0_5和daimaZC_1259722551031_0_6对应值(m_value)的和,并以 m_fields为daimaZC_1259722551031_0_8所对应的m_value值分组显示。 希望得到结果
daimaZC_1259722551031_0_5 daimaZC_1259722551031_0_6
-----------------------------------------------------
2 2
1 2
望高手指点,解决后分不够说声,我再开贴给,有不明白的请联系我,我的qq:309632173
http://topic.csdn.net/u/20091228/15/d335bdd2-4520-4f4b-a860-02c84f3c3c30.html?1258245724
之后的新需求,表如下:表: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_3对应值(m_value)为“40288184254a1ac301254f70202e0029”
的daimaZC_1259722551031_0_5和daimaZC_1259722551031_0_6对应值(m_value)的和,并以 m_fields为daimaZC_1259722551031_0_8所对应的m_value值分组显示。 希望得到结果
daimaZC_1259722551031_0_5 daimaZC_1259722551031_0_6
-----------------------------------------------------
2 2
1 2
望高手指点,解决后分不够说声,我再开贴给,有不明白的请联系我,我的qq:309632173
解决方案 »
- 从MySQL中导出数据出错
- 关于数据库插入的问题 求助~~
- 【求助】mysql 分组后,如何在每个组中显示序号?
- mysql 查询问题
- mysql大数据量下如何快速修改表结构?
- mysql insert 多表数据
- postgresql两个null字段比较
- 请问 PostgreSQL 8.3 和 MySQL 5.1 相比哪个更合适用作数据量和访问量(频繁连接数据库,同时连接数多)都比较大的网站的后台数据库????
- mycat连接自动关闭close connection,reason:stream closed
- 新手求解 为啥派生起名报错
- 头一回写概要设计,写的是一个电子商务平台的概要设计,请大家指正 (.DOC 共 14页)!
- 事务问题
mysql> select (select m_value from t_wf_data where m_fields='daimaZC_1259722551031_0_8' and m_nodeid=t.m_nodeid) as p,
-> sum((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,
-> sum((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'
-> )
-> group by (select m_value from t_wf_data where m_fields='daimaZC_1259722551031_0_8' and m_nodeid=t.m_nodeid);
+----------------------------------+---------------------------+---------------------------+
| p | daimaZC_1259722551031_0_5 | daimaZC_1259722551031_0_6 |
+----------------------------------+---------------------------+---------------------------+
| 40288184254a1ac301254f9c16350040 | 2 | 2 |
| 40288184254a1ac301254f9cb2560044 | 1 | 2 |
+----------------------------------+---------------------------+---------------------------+
2 rows in set (0.00 sec)mysql>mysql> select
-> sum((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,
-> sum((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'
-> )
-> group by (select m_value from t_wf_data where m_fields='daimaZC_1259722551031_0_8' and m_nodeid=t.m_nodeid);
+---------------------------+---------------------------+
| daimaZC_1259722551031_0_5 | daimaZC_1259722551031_0_6 |
+---------------------------+---------------------------+
| 2 | 2 |
| 1 | 2 |
+---------------------------+---------------------------+
2 rows in set (0.00 sec)mysql>
group by 是按天分组的 大数据量时 这样写也行吗?
且daimaZC_1259722551031_0_3对应值(m_value)为“40288184254a1ac301254f70202e0029”
和结果是很像了,但是还差一个条件统计t_wf_data表中m_fields下daimaZC_1259722551031_0_7对应值(m_value)为“项目管理”
且daimaZC_1259722551031_0_3对应值(m_value)为“40288184254a1ac301254f70202e0029”
这里是2个条件,二楼的只用了一个条件,在上一贴中是只提了一个条件。
-> sum((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,
-> sum((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'
-> )
-> and m_nodeid in (
-> select m_nodeid
-> from t_wf_data
-> where m_fields='daimaZC_1259722551031_0_3'
-> and m_value='40288184254a1ac301254f70202e0029'
-> )
-> group by (select m_value from t_wf_data where m_fields='daimaZC_1259722551031_0_8' and m_nodeid=t.m_nodeid);
+---------------------------+---------------------------+
| daimaZC_1259722551031_0_5 | daimaZC_1259722551031_0_6 |
+---------------------------+---------------------------+
| 2 | 2 |
| 1 | 2 |
+---------------------------+---------------------------+
2 rows in set (0.05 sec)mysql>
-> and m_nodeid in (
-> select m_nodeid
-> from t_wf_data
-> where m_fields='daimaZC_1259722551031_0_3'
-> and m_value='40288184254a1ac301254f70202e0029'
-> )这样控制 m_nodeid?
还有我要以多个字段分组group by (select m_value from t_wf_data where m_fields='daimaZC_1259722551031_0_8' and m_nodeid=t.m_nodeid),(select ......)
是这样?