多表联合查询统计问题

Question

表1  ue
id    name    pr_id
1     aa      1
2     bb      2表2   pr
id   name
1     oo
2     gg表3   kt
id   ue_id    time
1     1       2012-07-11 13:11:33
2     1       2012-07-11 13:11:33
3     1       2012-07-12 13:11:33
4     2       2012-07-12 13:11:33
如何用一条SQL根据kt.time查询完成以下的结果结果1：昨天 
id    name    pr_name    kt_num
1     aa      oo          2
2     bb      gg          0结果2：今天 
id    name    pr_name    kt_num
1     aa      oo          1
2     bb      gg          1结果3：N天前至今天的

Answer 1

SELECT a.`id`,a.`name`,b.`name`,
(SELECT COUNT(*) FROM kt WHERE ue_id=a.`pr_id` AND DATE(TIME)=CURDATE() - INTERVAL 1 DAY) AS aa,
(SELECT COUNT(*) FROM kt WHERE ue_id=a.`pr_id` AND DATE(TIME)=CURDATE() ) AS aa1,
(SELECT COUNT(*) FROM kt WHERE ue_id=a.`pr_id`)  AS aa2 FROM ue a LEFT JOIN pr b ON a.`pr_id`=b.`id`

Answer 2

or
SELECT a.`id`,a.`name`,b.`name`,
 SUM(IF(DATE(TIME)=CURDATE() - INTERVAL 1 DAY,1,0)),
 SUM(IF(DATE(TIME)=CURDATE() ,1,0)),COUNT(*) FROM ue a LEFT JOIN pr b ON a.`pr_id`=b.`id` LEFT JOIN kt c ON c.ue_id=a.`pr_id` GROUP BY a.`id`,a.`name`,b.`name`;
 

Answer 3

每一条ue的数据都去执行了count，如果ue表或kt表数据比较多，那效率会不会很低？
有没有更好的方法啊？

Answer 4

SELECT a.`id`,a.`name`,b.`name`,
  SUM(IF(DATE(TIME)=CURDATE() - INTERVAL 1 DAY,1,0)),
  SUM(IF(DATE(TIME)=CURDATE() ,1,0)),
  SUM(IF(DATE(TIME)<=CURDATE() ,1,0))
  FROM ue a LEFT JOIN pr b ON a.`pr_id`=b.`id` LEFT JOIN kt c ON c.ue_id=a.`pr_id` GROUP BY a.`id`,a.`name`,b.`name`;
在ue `pr_id`
pr b.`id`
kt ue_id
上建立索引

Answer 5

ue `pr_id`
pr  b.`id`
kt  ue_id这里设置主键好还是用索引好？又或者两者随便那一种都一样

Answer 6

表3 kt
id ue_id  xv_id time
1    1      1   2012-07-11 13:11:33
2    1      2   2012-07-11 13:11:33
3    1      1   2012-07-12 13:11:33
4    2      1   2012-07-12 13:11:33
如果同条件下xv_id相同的话，只算1个，这个怎么弄呢？

Answer 7

select ue.id,us.name,pr.name, (select count(distinct xv_id) from kt where ue_id=ue.id)
form ue,pr
where ue.id=pr.id

Answer 8

详细说明3、4算1条?
select * from kt a where not exists(select 1 from kt where a.xv_id=xv_id and a.time=time
and a.id>id)

Answer 9

表3 kt
id ue_id xv_id time
1 1 1 2012-07-11 13:11:33
2 1 2 2012-07-11 13:11:33
3 1 1 2012-07-12 13:11:33
4 2 1 2012-07-12 13:11:33
如果同条件下xv_id相同的话，只算1个，这个怎么弄呢？3和4算1条

Answer 10

SELECT a.`id`,a.`name`,b.`name`,
   SUM(IF(DATE(TIME)=CURDATE() - INTERVAL 1 DAY,1,0)),
   SUM(IF(DATE(TIME)=CURDATE() ,1,0)),
   SUM(IF(DATE(TIME)<=CURDATE() ,1,0))
   FROM ue a LEFT JOIN pr b ON a.`pr_id`=b.`id` 
   LEFT JOIN (SELECT * FROM kt a WHERE NOT EXISTS(SELECT 1 FROM kt WHERE a.xv_id=xv_id AND a.time=TIME
AND a.id>id)) c ON c.ue_id=a.`pr_id` GROUP BY a.`id`,a.`name`,b.`name`;KT：ue_id、ID、xv_id上建立复合索引

Answer 11

错了   3和4算是2条用这个
SELECT a.`id`,a.`name`,b.`name`,
  SUM(IF(DATE(TIME)=CURDATE() ,1,0)) as kt_num  FROM ue a LEFT JOIN pr b ON a.`pr_id`=b.`id` LEFT JOIN kt c ON c.ue_id=a.`pr_id` GROUP BY a.`id`,a.`name`,b.`name`;
表3 kt
id ue_id xv_id time
1 1 1 2012-07-12 13:11:33
2 1 2 2012-07-12 13:11:33
3 1 1 2012-07-12 13:11:33
4 2 1 2012-07-12 13:11:33
得到的结果是 
id name pr_name kt_num
1 aa oo 3
2 bb gg 1实际是需要下面的结果
id name pr_name kt_num
1 aa oo 2
2 bb gg 1因为kt表中1和3只算1条

Answer 12

SELECT a.`id`,a.`name`,b.`name`,
   SUM(IF(DATE(TIME)=CURDATE() - INTERVAL 1 DAY,1,0)),
   SUM(IF(DATE(TIME)=CURDATE() ,1,0)),
   SUM(IF(DATE(TIME)<=CURDATE() ,1,0))
   FROM ue a LEFT JOIN pr b ON a.`pr_id`=b.`id` 
   LEFT JOIN (SELECT * FROM kt a WHERE NOT EXISTS(SELECT 1 FROM kt WHERE a.xv_id=xv_id AND a.ue_id=ue_id
AND a.id>id)) c ON c.ue_id=a.`pr_id` GROUP BY a.`id`,a.`name`,b.`name`;KT：ue_id、xv_id、id上建立复合索引

Answer 13

ALTER TABLE `kt`
ADD INDEX (`id`, `xv_id`, `ue_id`);是这样建立复合索引的？

Answer 14

CREATE INDEX kt1 ON kt (`id`, `xv_id`, `ue_id`)

Answer 15

不会，理论上只会更快。
create index xx on kt(ue_id,xv_id) 即可。