mysql查询语句理解

最新推荐文章于 2024-09-24 10:50:41 发布

weixin_30642561

最新推荐文章于 2024-09-24 10:50:41 发布

阅读量69

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/jiqing9006/p/4962328.html

版权

看一个查询语句

 SELECT a.user,a.full_name,count(b.order_id) as subcount,sum(if(b.verifysta='Y',1,0)) as passcount FROM (SELECT b.user,b.full_name,b.user_group From login_log a  LEFT JOIN vicidial_users b on a.user = b.user where a.logtime>UNIX_TIMESTAMP('2015-11-13') and a.logtime<UNIX_TIMESTAMP('2015-11-13 23:59:59')  GROUP BY a.user ORDER BY b.user asc) a LEFT JOIN (SELECT user,order_id,verifysta from vicidial_order where time>UNIX_TIMESTAMP('2015-11-13') and time<UNIX_TIMESTAMP('2015-11-13 23:59:59') and user_group = 'TeamTwo') b
on a.user = b.user where a.user_group='TeamTwo' GROUP BY a.user;

他是由多个查询表组成的。

其实查询出来的数据，本身就是一个新表，可以作为新表继续使用。

SELECT b.user,b.full_name,b.user_group From login_log a  LEFT JOIN vicidial_users b on a.user = b.user where a.logtime>UNIX_TIMESTAMP('2015-11-13') and a.logtime<UNIX_TIMESTAMP('2015-11-13 23:59:59')  GROUP BY a.user ORDER BY b.user asc;

这个表查询出当天登录的用户名，全名，以及用户所属组。

这就是一张新的表，表中有4条数据，三个字段。

第二张表，也是查询出来的表。

SELECT user,order_id,verifysta from vicidial_order where time>UNIX_TIMESTAMP('2015-11-13') and time<UNIX_TIMESTAMP('2015-11-13 23:59:59') and user_group = 'TeamTwo';