`order_count`:分组后再连接SELECT A.id, A.title, COUNT( B.click_id ) AS viewer_count, SUM( B.click_count ) AS click_count, max(c.aa) FROM `team` A, `team_click` B, (select team_id, SUM(quantity ) as aa from `order_count` group by team_id) C WHERE A.id = B.team_id AND A.id = C.team_id GROUP BY A.id, A.title ORDER BY SUM( B.click_count ) DESC
修改成LEFT JOIN SELECT A.id, A.title, COUNT( B.click_id ) AS viewer_count, SUM( B.click_count ) AS click_count, max(c.aa) FROM `team` A left join `team_click` B on A.id = B.team_id left join (select team_id, SUM(quantity ) as aa from `order_count` group by team_id) C on A.id = C.team_id GROUP BY A.id, A.title ORDER BY SUM( B.click_count ) DESC
我又加了一些条件 用了LEFT JOIN后报错了SELECT A.id, A.title, COUNT( B.click_id ) AS viewer_count, SUM( B.click_count ) AS click_count, max(C.aa) FROM `team` A, LEFT JOIN `team_click` B ON A,id =B.team_id AND A.id = C.team_id AND (B.click_time > '1339689600')AND (B.click_time < '1339776000')AND (A.begin_time < '1339730371')AND (A.end_time > '1339730371') LEFT JOIN (SELECT team_id, SUM(quantity) as aa from `order_count` WHERE create_time > 1339689600 AND create_time < 1339776000 group by team_id) C ON A.id = B.team_id GROUP BY A.id, A.title ORDER BY SUM( B.click_count ) DESC MySQL server version for the right syntax to use near 'LEFT JOIN `team_click` B ON A,id =B.team_id AND A.id = C.team_id AND (B.click_ti' at line 2
SELECT A.id, A.title, COUNT( B.click_id ) AS viewer_count, SUM( B.click_count ) AS click_count, max(C.aa) FROM `team` A, LEFT JOIN `team_click` B ON A.id =B.team_id LEFT JOIN (SELECT team_id, SUM(quantity) as aa from `order_count` group by team_id) C ON A.id = C.team_id GROUP BY A.id, A.title ORDER BY SUM( B.click_count ) DESC
建议先学习一下SQL的基本语法, SELECT A.id, A.title, COUNT( B.click_id ) AS viewer_count, SUM( B.click_count ) AS click_count, MAX(C.aa) FROM `team` A LEFT JOIN `team_click` B ON A,id =B.team_id AND (B.click_time > '1339689600') AND (B.click_time < '1339776000') AND (A.begin_time < '1339730371') AND (A.end_time > '1339730371') LEFT JOIN (SELECT team_id, SUM(quantity) AS aa FROM `order_count` WHERE create_time > 1339689600 AND create_time < 1339776000 GROUP BY team_id) C ON A.id = C.team_id GROUP BY A.id, A.title ORDER BY SUM( B.click_count ) DESC
SELECT A.id, A.title, COUNT( B.click_id ) AS viewer_count, SUM( B.click_count ) AS click_count, max(C.aa) FROM `team` A LEFT JOIN `team_click` B ON A.id =B.team_id LEFT JOIN (SELECT team_id, SUM(quantity) as aa from `order_count` group by team_id) C ON A.id = C.team_id GROUP BY A.id, A.title ORDER BY SUM( B.click_count ) DESC 这次不报错了 但是一运行 mysql直接死了 必须重启才行
explain SELECT A.id, A.title, COUNT( B.click_id ) AS viewer_count, SUM( B.click_count ) AS click_count, max(C.aa) FROM `team` A LEFT JOIN `team_click` B ON A.id =B.team_id LEFT JOIN (SELECT team_id, SUM(quantity) as aa from `order_count` group by team_id) C ON A.id = C.team_id GROUP BY A.id, A.title ORDER BY SUM( B.click_count ) DESC 在`team_id`上建立索引 SELECT A.id, A.title, COUNT( B.click_id ) AS viewer_count, SUM( B.click_count ) AS click_count, MAX(C.aa) FROM `team` A LEFT JOIN `team_click` B ON A.id =B.team_id AND (B.click_time > '1339689600') AND (B.click_time < '1339776000') AND (A.begin_time < '1339730371') AND (A.end_time > '1339730371') LEFT JOIN (SELECT team_id, SUM(quantity) AS aa FROM `order_count` WHERE create_time > 1339689600 AND create_time < 1339776000 GROUP BY team_id) C ON A.id = C.team_id GROUP BY A.id, A.title ORDER BY SUM( B.click_count ) DESC
用了后直接无响应了 必须重启mysql 谢谢你一直帮我解决问题 你说这一次无响应又是怎么回事呢?
explain SELECT A.id, A.title, COUNT( B.click_id ) AS viewer_count, SUM( B.click_count ) AS click_count, max(C.aa) FROM `team` A LEFT JOIN `team_click` B ON A.id =B.team_id LEFT JOIN (SELECT team_id, SUM(quantity) as aa from `order_count` group by team_id) C ON A.id = C.team_id GROUP BY A.id, A.title ORDER BY SUM( B.click_count ) DESC 贴结果,估计是运行速度慢了,你没有建立所需的索引
索引建立了之后 我再运行 SELECT A.id, A.title, COUNT( B.click_id ) AS viewer_count, SUM( B.click_count ) AS click_count, max(C.aa) FROM `team` A LEFT JOIN `team_click` B ON A.id =B.team_id LEFT JOIN (SELECT team_id, SUM(quantity) as aa from `order_count` group by team_id) C ON A.id = C.team_id GROUP BY A.id, A.title ORDER BY SUM( B.click_count ) DESC 还是没响应 我是用phpmyadmin运行的
SELECT A.id, A.title, COUNT( B.click_id ) AS viewer_count, SUM( B.click_count ) AS click_count FROM `team` A LEFT JOIN `team_click` B ON A.id =B.team_id GROUP BY A.id, A.title ORDER BY SUM( B.click_count ) DESC 速度如何SELECT A.id, A.title, COUNT( B.click_id ) AS viewer_count, SUM( B.click_count ) AS click_count, max(C.aa) FROM `team` A LEFT JOIN `team_click` B ON A.id =B.team_id LEFT JOIN (SELECT team_id, SUM(quantity) as aa from `order_count` group by team_id) C ON A.id = C.team_id GROUP BY A.id, A.title 速度如何
explain SELECT A.id, A.title, COUNT( B.click_id ) AS viewer_count, SUM( B.click_count ) AS click_count, max(C.aa) FROM `team` A LEFT JOIN `team_click` B ON A.id =B.team_id LEFT JOIN (SELECT team_id, SUM(quantity) as aa from `order_count` group by team_id) C ON A.id = C.team_id GROUP BY A.id, A.title ORDER BY SUM( B.click_count ) DESC SELECT A.id, A.title, COUNT( B.click_id ) AS viewer_count, SUM( B.click_count ) AS click_count FROM `team` A LEFT JOIN `team_click` B ON A.id =B.team_id GROUP BY A.id, A.title 速度如何表记录有多少
SELECT team_id, SUM(quantity) as aa from `order_count` group by team_id 速度如何 在team_id上建立索引 没有
SELECT A.id, A.title,max(C.aa) FROM `team` A LEFT JOIN (SELECT team_id, SUM(quantity) as aa from `order_count` group by team_id) C ON A.id = C.team_id GROUP BY A.id, A.title 速度如何`team` `team_click`表记录有多少
18.53秒 team数据1w6 条 team_click 6w条
order_count表被我改为order表 表结构一样的
SELECT A.id, A.title, COUNT( B.click_id ) AS viewer_count, SUM( B.click_count ) AS click_count, max(C.aa) FROM `team` A LEFT JOIN `team_click` B ON A.id =B.team_id LEFT JOIN (SELECT team_id, SUM(quantity) as aa from `order_count` group by team_id) C ON A.id = C.team_id GROUP BY A.id, A.title ORDER BY SUM( B.click_count ) DESC create index aa on `order_count`(team_id) create index aa1 on `team_click`(team_id)
我是先 create index aa on `order`(team_id) create index aa1 on `team_click`(team_id)然后再 SELECT A.id, A.title, COUNT( B.click_id ) AS viewer_count, SUM( B.click_count ) AS click_count, max(C.aa) FROM `team` A LEFT JOIN `team_click` B ON A.id =B.team_id LEFT JOIN (SELECT team_id, SUM(quantity) as aa from `order` group by team_id) C ON A.id = C.team_id GROUP BY A.id, A.title ORDER BY SUM( B.click_count ) DESC结果还是特慢 无响应
我是先 create index aa on `order`(team_id) create index aa1 on `team_click`(team_id)然后再 SELECT A.id, A.title, COUNT( B.click_id ) AS viewer_count, SUM( B.click_count ) AS click_count, max(C.aa) FROM `team` A LEFT JOIN `team_click` B ON A.id =B.team_id LEFT JOIN (SELECT team_id, SUM(quantity) as aa from `order` group by team_id) C ON A.id = C.team_id GROUP BY A.id, A.title ORDER BY SUM( B.click_count ) DESC结果还是特慢 无响应
使用了三个LEFT JOIN 速度然后还使用了GROUP和ORDER,不会太快了。贴出你相关表的 show index from .... 以供分析。
FROM `team` A, `team_click` B,
(select team_id, SUM(quantity ) as aa from `order_count` group by team_id) C
WHERE A.id = B.team_id
AND A.id = C.team_id
GROUP BY A.id, A.title
ORDER BY SUM( B.click_count ) DESC
(126084, 30947, 'pay', 'normal', 1, 46.00),
(125922, 36811, 'unpay', 'normal', 1, 0.00),
(125923, 24465, 'pay', 'normal', 1, 0.00),
(125924, 37585, 'pay', 'normal', 1, 59.00),
(125925, 30947, 'pay', 'normal', 1, 15.00),
(125926, 37295, 'pay', 'normal', 1, 27.00),
(125927, 36032, 'pay', 'normal', 1, 0.00),
(125928, 36027, 'pay', 'normal', 1, 27.00),
(125929, 24465, 'pay', 'normal', 1, 0.00),
(125930, 35544, 'unpay', 'normal', 1, 0.00);INSERT INTO `team_click` (`click_id`, `team_id`, `user_ip`, `click_count`, `click_time`) VALUES
(197, 30947, '113.139.209.203', 2, 1339647145),
(196, 30947, '220.181.108.148', 5, 1339647145),
(195, 24465, '184.154.48.82', 3, 1339647144),
(194, 24465, '207.46.13.94', 1, 1339647144),
(193, 24465, '65.52.109.194', 1, 1339647143),
(192, 37032, '220.181.108.172', 1, 1339647143),
(191, 37255, '116.228.87.214', 3, 1339647143),
(190, 24464, '184.154.48.82', 1, 1339647142),
(189, 23302, '65.52.110.17', 1, 1339647141),
(188, 37934, '220.184.202.34', 1, 1339647141);
SELECT A.id, A.title, COUNT( B.click_id ) AS viewer_count, SUM( B.click_count ) AS click_count, max(c.aa)
FROM `team` A left join `team_click` B on A.id = B.team_id
left join (select team_id, SUM(quantity ) as aa from `order_count` group by team_id) C
on A.id = C.team_id
GROUP BY A.id, A.title
ORDER BY SUM( B.click_count ) DESC
FROM `team` A, LEFT JOIN `team_click` B ON A,id =B.team_id AND A.id = C.team_id AND (B.click_time > '1339689600')AND (B.click_time < '1339776000')AND (A.begin_time < '1339730371')AND (A.end_time > '1339730371')
LEFT JOIN (SELECT team_id, SUM(quantity) as aa from `order_count` WHERE create_time > 1339689600 AND create_time < 1339776000 group by team_id) C
ON A.id = B.team_id
GROUP BY A.id, A.title ORDER BY SUM( B.click_count ) DESC MySQL server version for the right syntax to use near 'LEFT JOIN `team_click` B ON A,id =B.team_id AND A.id = C.team_id AND (B.click_ti' at line 2
SELECT A.id, A.title, COUNT( B.click_id ) AS viewer_count, SUM( B.click_count ) AS click_count, max(C.aa)
FROM `team` A, LEFT JOIN `team_click` B ON A.id =B.team_id
LEFT JOIN (SELECT team_id, SUM(quantity) as aa from `order_count` group by team_id) C
ON A.id = C.team_id
GROUP BY A.id, A.title ORDER BY SUM( B.click_count ) DESC
SELECT A.id, A.title, COUNT( B.click_id ) AS viewer_count, SUM( B.click_count ) AS click_count, MAX(C.aa)
FROM `team` A LEFT JOIN `team_click` B ON A,id =B.team_id
AND (B.click_time > '1339689600') AND (B.click_time < '1339776000') AND (A.begin_time < '1339730371') AND (A.end_time > '1339730371')
LEFT JOIN (SELECT team_id, SUM(quantity) AS aa FROM `order_count` WHERE create_time > 1339689600 AND create_time < 1339776000 GROUP BY team_id) C
ON A.id = C.team_id
GROUP BY A.id, A.title ORDER BY SUM( B.click_count ) DESC
SELECT A.id, A.title, COUNT( B.click_id ) AS viewer_count, SUM( B.click_count ) AS click_count, max(C.aa)
FROM `team` A LEFT JOIN `team_click` B ON A.id =B.team_id
LEFT JOIN (SELECT team_id, SUM(quantity) as aa from `order_count` group by team_id) C
ON A.id = C.team_id
GROUP BY A.id, A.title ORDER BY SUM( B.click_count ) DESC 这次不报错了 但是一运行 mysql直接死了 必须重启才行
FROM `team` A LEFT JOIN `team_click` B ON A.id =B.team_id
LEFT JOIN (SELECT team_id, SUM(quantity) as aa from `order_count` group by team_id) C
ON A.id = C.team_id
GROUP BY A.id, A.title ORDER BY SUM( B.click_count ) DESC 在`team_id`上建立索引
SELECT A.id, A.title, COUNT( B.click_id ) AS viewer_count, SUM( B.click_count ) AS click_count, MAX(C.aa)
FROM `team` A LEFT JOIN `team_click` B ON A.id =B.team_id
AND (B.click_time > '1339689600') AND (B.click_time < '1339776000') AND (A.begin_time < '1339730371') AND (A.end_time > '1339730371')
LEFT JOIN (SELECT team_id, SUM(quantity) AS aa FROM `order_count` WHERE create_time > 1339689600 AND create_time < 1339776000 GROUP BY team_id) C
ON A.id = C.team_id
GROUP BY A.id, A.title ORDER BY SUM( B.click_count ) DESC
FROM `team` A LEFT JOIN `team_click` B ON A.id =B.team_id
LEFT JOIN (SELECT team_id, SUM(quantity) as aa from `order_count` group by team_id) C
ON A.id = C.team_id
GROUP BY A.id, A.title ORDER BY SUM( B.click_count ) DESC 贴结果,估计是运行速度慢了,你没有建立所需的索引
索引建立了之后 我再运行
SELECT A.id, A.title, COUNT( B.click_id ) AS viewer_count, SUM( B.click_count ) AS click_count, max(C.aa)
FROM `team` A LEFT JOIN `team_click` B ON A.id =B.team_id
LEFT JOIN (SELECT team_id, SUM(quantity) as aa from `order_count` group by team_id) C
ON A.id = C.team_id
GROUP BY A.id, A.title ORDER BY SUM( B.click_count ) DESC
还是没响应 我是用phpmyadmin运行的
FROM `team` A LEFT JOIN `team_click` B ON A.id =B.team_id
GROUP BY A.id, A.title ORDER BY SUM( B.click_count ) DESC
速度如何SELECT A.id, A.title, COUNT( B.click_id ) AS viewer_count, SUM( B.click_count ) AS click_count, max(C.aa)
FROM `team` A LEFT JOIN `team_click` B ON A.id =B.team_id
LEFT JOIN (SELECT team_id, SUM(quantity) as aa from `order_count` group by team_id) C
ON A.id = C.team_id
GROUP BY A.id, A.title
速度如何
FROM `team` A LEFT JOIN `team_click` B ON A.id =B.team_id
LEFT JOIN (SELECT team_id, SUM(quantity) as aa from `order_count` group by team_id) C
ON A.id = C.team_id
GROUP BY A.id, A.title ORDER BY SUM( B.click_count ) DESC SELECT A.id, A.title, COUNT( B.click_id ) AS viewer_count, SUM( B.click_count ) AS click_count
FROM `team` A LEFT JOIN `team_click` B ON A.id =B.team_id
GROUP BY A.id, A.title
速度如何表记录有多少
速度如何
在team_id上建立索引 没有
FROM `team` A LEFT JOIN (SELECT team_id, SUM(quantity) as aa from `order_count` group by team_id) C
ON A.id = C.team_id
GROUP BY A.id, A.title
速度如何`team` `team_click`表记录有多少
order_count表被我改为order表 表结构一样的
FROM `team` A LEFT JOIN `team_click` B ON A.id =B.team_id
LEFT JOIN (SELECT team_id, SUM(quantity) as aa from `order_count` group by team_id) C
ON A.id = C.team_id
GROUP BY A.id, A.title ORDER BY SUM( B.click_count ) DESC create index aa on `order_count`(team_id)
create index aa1 on `team_click`(team_id)
create index aa on `order`(team_id)
create index aa1 on `team_click`(team_id)然后再 SELECT A.id, A.title, COUNT( B.click_id ) AS viewer_count, SUM( B.click_count ) AS click_count, max(C.aa)
FROM `team` A LEFT JOIN `team_click` B ON A.id =B.team_id
LEFT JOIN (SELECT team_id, SUM(quantity) as aa from `order` group by team_id) C
ON A.id = C.team_id
GROUP BY A.id, A.title ORDER BY SUM( B.click_count ) DESC结果还是特慢 无响应
create index aa on `order`(team_id)
create index aa1 on `team_click`(team_id)然后再 SELECT A.id, A.title, COUNT( B.click_id ) AS viewer_count, SUM( B.click_count ) AS click_count, max(C.aa)
FROM `team` A LEFT JOIN `team_click` B ON A.id =B.team_id
LEFT JOIN (SELECT team_id, SUM(quantity) as aa from `order` group by team_id) C
ON A.id = C.team_id
GROUP BY A.id, A.title ORDER BY SUM( B.click_count ) DESC结果还是特慢 无响应