第一个问题: 应该是没必要建立function,直接连接是可以得到需要的报销信息,最好在db_reiminfo上的personNo上建立索引第二个问题: 不需要建立临时表,直接insert into table select 应该会好点
请问如果不建立function,怎么能得到每项报销信息呢?这条语句要怎么写?
select a.id,a.personno,a.name,a.dep, sum(case when locate(r_type,'t2+t3') > 0 then amount else 0 end) dache, sum(case when locate(r_type,'t1+t3') > 0 then amount else 0 end) zhusu, sum(case when locate(r_type,'t3') > 0 then amount else 0 end) canyin from db_personinfo a left join db_reiminfo c on a.personNo = c.personNo group by personno
mysql> select * from db_personalinfo;+------+----------+--------+------+ | id | personno | name | dep | +------+----------+--------+------+ | 1 | 1000 | 张三 | d1 | | 2 | 1001 | 李四 | d2 | | 3 | 1002 | 王五 | d3 | +------+----------+--------+------+ 3 rows in set (0.00 sec)mysql> select * from db_reiminfo;+------+----------+--------+--------+---------+ | id | personno | r_type | amount | yearmon | +------+----------+--------+--------+---------+ | 1 | 1000 | t1 | 550 | 201401 | | 2 | 1000 | t2 | 200 | 201401 | | 3 | 1000 | t3 | 110 | 201401 | | 4 | 1001 | t2 | 320 | 201401 | | 5 | 1001 | t3 | 210 | 201401 | | 6 | 1003 | t2 | 100 | 201401 | | 7 | 1004 | t1 | 180 | 201401 | +------+----------+--------+--------+---------+ 7 rows in set (0.00 sec)mysql> select * from db_reimtype; +------+-----------+-----------+ | id | reim_name | reim_expr | +------+-----------+-----------+ | 1 | 打车 | t2+t3 | | 2 | 住宿 | t1+t3 | | 3 | 餐饮 | t3 | +------+-----------+-----------+ 3 rows in set (0.00 sec) --------------------------------以下是查询语句,供参考------------------------- mysql> SELECT a.id,a.personno,a.name,a.dep -> ,SUM(CASE WHEN c.reim_name='打车' THEN amount ELSE 0 END) AS '打车' -> ,SUM(CASE WHEN c.reim_name='住宿' THEN amount ELSE 0 END) AS '住宿' -> ,SUM(CASE WHEN c.reim_name='餐饮' THEN amount ELSE 0 END) AS '餐饮' -> FROM db_personalinfo a JOIN db_reiminfo b -> ON a.personno=b.personno -> JOIN db_reimtype c -> ON INSTR(c.reim_expr,b.r_type) -> GROUP BY a.id,a.personno,a.name,a.dep; +------+----------+--------+------+--------+--------+--------+ | id | personno | name | dep | 打车 | 住宿 | 餐饮 | +------+----------+--------+------+--------+--------+--------+ | 1 | 1000 | 张三 | d1 | 310 | 660 | 110 | | 2 | 1001 | 李四 | d2 | 530 | 210 | 210 | +------+----------+--------+------+--------+--------+--------+ 2 rows in set (0.00 sec)
http://blog.csdn.net/acmain_chm/article/details/4283943 MySQL交叉表 在某些数据库中有交叉表,但在MySQL中却没有这个功能,但网上看到有不少朋友想找出一个解决方法,特发贴集思广义。http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html?96198现整理解法如下:数据样本: create table tx( id int primary key, c1 c...
第一个问题: 应该是没必要建立function,直接连接是可以得到需要的报销信息,最好在db_reiminfo上的personNo上建立索引第二个问题: 不需要建立临时表,直接insert into table select 应该会好点
请问如果不建立function,怎么能得到每项报销信息呢?这条语句要怎么写?
sum(case when locate(r_type,'t2+t3') > 0 then amount else 0 end) dache,
sum(case when locate(r_type,'t1+t3') > 0 then amount else 0 end) zhusu,
sum(case when locate(r_type,'t3') > 0 then amount else 0 end) canyin
from db_personinfo a left join db_reiminfo c on a.personNo = c.personNo group by personno
| id | personno | name | dep |
+------+----------+--------+------+
| 1 | 1000 | 张三 | d1 |
| 2 | 1001 | 李四 | d2 |
| 3 | 1002 | 王五 | d3 |
+------+----------+--------+------+
3 rows in set (0.00 sec)mysql> select * from db_reiminfo;+------+----------+--------+--------+---------+
| id | personno | r_type | amount | yearmon |
+------+----------+--------+--------+---------+
| 1 | 1000 | t1 | 550 | 201401 |
| 2 | 1000 | t2 | 200 | 201401 |
| 3 | 1000 | t3 | 110 | 201401 |
| 4 | 1001 | t2 | 320 | 201401 |
| 5 | 1001 | t3 | 210 | 201401 |
| 6 | 1003 | t2 | 100 | 201401 |
| 7 | 1004 | t1 | 180 | 201401 |
+------+----------+--------+--------+---------+
7 rows in set (0.00 sec)mysql> select * from db_reimtype;
+------+-----------+-----------+
| id | reim_name | reim_expr |
+------+-----------+-----------+
| 1 | 打车 | t2+t3 |
| 2 | 住宿 | t1+t3 |
| 3 | 餐饮 | t3 |
+------+-----------+-----------+
3 rows in set (0.00 sec)
--------------------------------以下是查询语句,供参考-------------------------
mysql> SELECT a.id,a.personno,a.name,a.dep
-> ,SUM(CASE WHEN c.reim_name='打车' THEN amount ELSE 0 END) AS '打车'
-> ,SUM(CASE WHEN c.reim_name='住宿' THEN amount ELSE 0 END) AS '住宿'
-> ,SUM(CASE WHEN c.reim_name='餐饮' THEN amount ELSE 0 END) AS '餐饮'
-> FROM db_personalinfo a JOIN db_reiminfo b
-> ON a.personno=b.personno
-> JOIN db_reimtype c
-> ON INSTR(c.reim_expr,b.r_type)
-> GROUP BY a.id,a.personno,a.name,a.dep;
+------+----------+--------+------+--------+--------+--------+
| id | personno | name | dep | 打车 | 住宿 | 餐饮 |
+------+----------+--------+------+--------+--------+--------+
| 1 | 1000 | 张三 | d1 | 310 | 660 | 110 |
| 2 | 1001 | 李四 | d2 | 530 | 210 | 210 |
+------+----------+--------+------+--------+--------+--------+
2 rows in set (0.00 sec)
MySQL交叉表
在某些数据库中有交叉表,但在MySQL中却没有这个功能,但网上看到有不少朋友想找出一个解决方法,特发贴集思广义。http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html?96198现整理解法如下:数据样本: create table tx( id int primary key, c1 c...
您这里的动态执行怎么理解?
字符串累加生成SQL语句,再执行的方法