有两个表:
一个是登录用户表:z_users
+-------------+------------+----------+------------+
| z_id | z_logum | z_pw | z_username |
+------------ +------------+----------+------------+
| 21 | xiaoxia | ******* | 刘宗利 |
| 22 | zhangshan | ******* | 夏新愿 |
| 23 | wanger | ******* | 岳晶晶 |
| 24 | zhanglong | ******* | 赵学广 |
| 25 | zhaoziyang | ******* | 于欣 |
| 26 | liudehua | ******* | 贾荣暖 |
+-------------+------------+----------+------------+另一个是:z_hzmanage内容表。
+------------+------------+----------+----------+-----------+
| z_id | z_yydate | z_dzdate |z_username| z_class |
+------------+------------+----------+----------+-----------+
| 1 | 2013-09-03 | 0 | 刘宗利 | 21 |
| 2 | 2013-09-03 | 0 | 夏新愿 | 22 |
| 3 | 2013-09-02 | 0 | 岳晶晶 | 23 |
| 4 | 2013-09-03 | 0 | 赵学广 | 24 |
| 5 | 2013-09-03 | 0 | 于欣 | 25 |
| 6 | 2013-09-03 | 0 | 贾荣暖 | 26 |
+------------+-------------+----------+----------+-----------+下面我统计内容的‘预约时间’与‘到诊时间’得出下面查询
select z_username,
sum(if(z_yydate=curdate()-interval 1 day,1,0)) as `昨日预约`,
sum(if(z_dzdate=curdate()-interval 1 day,1,0)) as `昨日到诊`,
sum(if(z_yydate>curdate()-interval day(curdate()) day,1,0)) as `本月预约`,
sum(if(z_dzdate=curdate()-interval day(curdate()) day,1,0)) as `本月到诊`
from z_hzmanage
group by z_username显示结果:
+------------+----------+----------+----------+----------+
| z_username | 昨日预约 | 昨日到诊 | 本月预约 | 本月到诊 |
+------------+----------+----------+----------+----------+
| 刘宗利 | 0 | 0 | 5 | 0 |
| 夏新愿 | 0 | 0 | 1 | 0 |
| 岳晶晶 | 1 | 0 | 3 | 0 |
| 赵学广 | 0 | 0 | 0 | 0 |
| 于欣 | 0 | 0 | 1 | 0 |
| 贾荣暖 | 0 | 0 | 6 | 0 |
+------------+----------+----------+----------+----------+
可是我现在查询统计的是z_hzmanage表的z_username所有姓名,我现
在需要的是z_users表与z_hzmanage表关联起来求出z_users表中的所
有z_username姓名,这样当我在页面修改用户名称的时候,就依用户
表中的姓名(z_username)显示,而不是内容表中的用户姓名(z_username)显示。求版主和各位朋友帮帮忙啊!在这里感激不尽感激不尽! QQ联系:694192202
一个是登录用户表:z_users
+-------------+------------+----------+------------+
| z_id | z_logum | z_pw | z_username |
+------------ +------------+----------+------------+
| 21 | xiaoxia | ******* | 刘宗利 |
| 22 | zhangshan | ******* | 夏新愿 |
| 23 | wanger | ******* | 岳晶晶 |
| 24 | zhanglong | ******* | 赵学广 |
| 25 | zhaoziyang | ******* | 于欣 |
| 26 | liudehua | ******* | 贾荣暖 |
+-------------+------------+----------+------------+另一个是:z_hzmanage内容表。
+------------+------------+----------+----------+-----------+
| z_id | z_yydate | z_dzdate |z_username| z_class |
+------------+------------+----------+----------+-----------+
| 1 | 2013-09-03 | 0 | 刘宗利 | 21 |
| 2 | 2013-09-03 | 0 | 夏新愿 | 22 |
| 3 | 2013-09-02 | 0 | 岳晶晶 | 23 |
| 4 | 2013-09-03 | 0 | 赵学广 | 24 |
| 5 | 2013-09-03 | 0 | 于欣 | 25 |
| 6 | 2013-09-03 | 0 | 贾荣暖 | 26 |
+------------+-------------+----------+----------+-----------+下面我统计内容的‘预约时间’与‘到诊时间’得出下面查询
select z_username,
sum(if(z_yydate=curdate()-interval 1 day,1,0)) as `昨日预约`,
sum(if(z_dzdate=curdate()-interval 1 day,1,0)) as `昨日到诊`,
sum(if(z_yydate>curdate()-interval day(curdate()) day,1,0)) as `本月预约`,
sum(if(z_dzdate=curdate()-interval day(curdate()) day,1,0)) as `本月到诊`
from z_hzmanage
group by z_username显示结果:
+------------+----------+----------+----------+----------+
| z_username | 昨日预约 | 昨日到诊 | 本月预约 | 本月到诊 |
+------------+----------+----------+----------+----------+
| 刘宗利 | 0 | 0 | 5 | 0 |
| 夏新愿 | 0 | 0 | 1 | 0 |
| 岳晶晶 | 1 | 0 | 3 | 0 |
| 赵学广 | 0 | 0 | 0 | 0 |
| 于欣 | 0 | 0 | 1 | 0 |
| 贾荣暖 | 0 | 0 | 6 | 0 |
+------------+----------+----------+----------+----------+
可是我现在查询统计的是z_hzmanage表的z_username所有姓名,我现
在需要的是z_users表与z_hzmanage表关联起来求出z_users表中的所
有z_username姓名,这样当我在页面修改用户名称的时候,就依用户
表中的姓名(z_username)显示,而不是内容表中的用户姓名(z_username)显示。求版主和各位朋友帮帮忙啊!在这里感激不尽感激不尽! QQ联系:694192202
解决方案 »
- 求助:orcale库迁到mysql后性能低下问题~~~~
- mysql怎么更改表名和数据库名?
- mysql查询问题
- mysql 插入数据 error 类型: 1452 在线等待回答
- 如何在Windows上安装两个Mysql服务
- 小弟在网上下了个blog程序,mysql建库时报错:Warning: Cannot modify header information - headers already sent by ....
- 问个关于mysql replication的问题
- Mysql中如何装载XML格式的文件的内容
- 如何动态查询缴费记录?高手指点指点
- 求助,存储过程只循环了一次
- ODBC连接MySQL,服务启动前加载30W数据,请大神指教,看是否能在优化一下性能,代码如下:
- ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 438, event
sum(if(z_yydate=curdate()-interval 1 day,1,0)) as `昨日预约`,
sum(if(z_dzdate=curdate()-interval 1 day,1,0)) as `昨日到诊`,
sum(if(z_yydate>curdate()-interval day(curdate()) day,1,0)) as `本月预约`,
sum(if(z_dzdate=curdate()-interval day(curdate()) day,1,0)) as `本月到诊`
from z_hzmanage
group by z_class