解决方案 »
- 急有偿请人帮我恢复mysql数据库
- 【求教】mysql 联合查询语句求教
- 关于同一数据库两张表的同步问题。
- 请教运行一个程序时候 提示
- 关于MYSQL查询的一个问题!
- Linux9上phpMyAdmin连mysql:#2003 - Can't connect to MySQL server on '192.168.0.1' (111)
- mysql为何不能修改密码
- MySQL 如何在插入时得到该行的 id ?
- 救助,jsp 访问 MySQL, org.apache.jasper.JasperException
- 求大神帮看条分组排名语句。An error occured in multi-table update。没分了。。。
- 请教一个时间函数问题 谢谢
- 关于临时表的问题
第一个问题: 应该是没必要建立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语句,再执行的方法