sql语句
-
SQL code
SELECT COUNT( CASE WHEN month(login.cLoginTime)="1" THEN `user`.strGuid END) 一月 ,COUNT( CASE WHEN month(login.cLoginTime)="2" THEN `user`.strGuid END) 二月 ,COUNT( CASE WHEN month(login.cLoginTime)="3" THEN `user`.strGuid END) 三月 FROM aocslogininfo login, aocsuserinfo `user`,aocssiteinfo site WHERE `user`.strGuid=login.strUserGuid AND site.strGuid=`user`.strGuidForSiteInfo /*AND DATE(login.cLoginTime )NOT IN (SELECT DATE(holiday) FROM holiday )*/ GROUP BY site.strDistrict ,count( case when month(login.cLoginTime)="12" then `user`.strGuid end) 十二月 FROM aocslogininfo login, aocsuserinfo `user`,aocssiteinfo site WHERE `user`.strGuid=login.strUserGuid AND site.strGuid=`user`.strGuidForSiteInfo /*AND DATE(login.cLoginTime )NOT IN (SELECT DATE(holiday) FROM holiday )*/ GROUP BY site.strDistrict
求解决 当某个人某天多次签到的时候 这个sql语句统计签到次数的时候 会把多次签到的次数也算进去 求解决 怎么才能不把多次签到的次数统计进去
建议提供测试用例之前,先自己动手测试一下,然后再贴出。
引用mysql> insert into `aocslogininfo`(`key`,`strGuid`,`strUserGuid`,`strUserName`,
`cLoginTime`,`eSyncType`,`iState`) values (1,’c111′,’b11′,’C111′,’2012-01-17 00:
12:00′,NULL,NULL),(2,’c112′,’b11′,’C112′,’2012-01-17 00:23:14′,NULL,NULL),(3,’c1
21′,’b12′,’C121′,’2012-02-17 00:00:01′,NULL,NULL),(4,’c122′,’b12′,’C122′,’2012-0
2-17 00:00:02′,NULL,NULL),(7,’c221′,’b22′,’C221′,’2012-02-17 00:00:05′,NULL,NULL
),(8,’c222′,’b22′,’C222′,’2012-02-17 00:00:06′,NULL,NULL),(9,’c321′,’b32′,’C321′
,’2012-02-17 00:00:09′,NULL,NULL),(10,’c322′,’b32′,’C322′,’2012-02-17 00:00:12′,
NULL,NULL),(11,’c211′,’b21′,’C211′,’2012-03-17 00:00:03′,NULL,NULL),(12,’c212′,’
b21′,’C212′,’2012-03-17 00:00:04′,NULL,NULL),(13,’c311′,’b31′,’C311′,’2012-04-17
00:00:07′,NULL,NULL),(14,’c312′,’b31′,’C312′,’2012-04-17 00:00:08′,NULL,NULL);
ERROR 1054 (42S22): Unknown column ‘eSyncType’ in ‘field list’
mysql>