Mysql 多表查询的一个问题，困扰了几天了

wwlaiyyf 2012-06-15 10:25:02
商品表


CREATE TABLE IF NOT EXISTS `team` (

  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

  `user_id` int(10) unsigned NOT NULL DEFAULT '0',

  `title` varchar(128) DEFAULT NULL,

  `team_price` double(10,2) NOT NULL DEFAULT '0.00',

  `market_price` double(10,2) NOT NULL DEFAULT '0.00',

  `cost_price` double(10,2) NOT NULL DEFAULT '0.00',

  PRIMARY KEY (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8;


商品点击量表


CREATE TABLE IF NOT EXISTS `team_click` (

  `click_id` bigint(30) NOT NULL AUTO_INCREMENT,

  `team_id` int(10) NOT NULL,

  `user_ip` varchar(25) CHARACTER SET utf8 NOT NULL,

  `click_count` int(20) NOT NULL DEFAULT '1',

  `click_time` int(10) unsigned NOT NULL,

  PRIMARY KEY (`click_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8;


订单表


CREATE TABLE IF NOT EXISTS `order_count` (

  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

  `team_id` int(10) unsigned NOT NULL DEFAULT '0',

  `state` enum('unpay','pay') NOT NULL DEFAULT 'unpay',

  `rstate` enum('normal','askrefund','berefund','norefund') NOT NULL DEFAULT 'normal',

  `quantity` int(10) unsigned NOT NULL DEFAULT '1',

  `money` double(10,2) NOT NULL DEFAULT '0.00',

  PRIMARY KEY (`id`),

) ENGINE=MyISAM  DEFAULT CHARSET=utf8;


商品点击量表 和 订单表 都有很多相同的team_id
现在我想查询出 


SELECT team.id, team.title, 

COUNT( team_click.click_id ) AS viewer_count, 

SUM( team_click.click_count ) AS click_count, 

SUM( order_count.quantity )


我是这样做的：


SELECT A.id, A.title, COUNT( B.click_id ) AS viewer_count, SUM( B.click_count ) AS click_count, SUM( C.quantity ) 

FROM  `team` A,  `team_click` B,  `order_count` 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

但是得到的结果
COUNT( team_click.click_id )
SUM( team_click.click_count )
SUM( order_count.quantity )
几个值全都非常大 如果我去掉`order_count` C 和SUM( C.quantity ) 查询出来的数据就正常 如何把order_count表也关联进去呢？ 谢谢大家帮我解答
...全文
354 26 打赏 收藏 转发到动态 举报
写回复
用AI写文章
26 条回复
切换为时间正序
请发表友善的回复…
发表回复
wwlaiyyf 2012-06-15
  • 打赏
  • 举报
 回复
[Quote=引用 15 楼 的回复:]

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……
[/Quote]
order_count 30多万条
WWWWA 2012-06-15
  • 打赏
  • 举报
 回复
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
速度如何

表记录有多少
wwlaiyyf 2012-06-15
  • 打赏
  • 举报
 回复
[Quote=引用 13 楼 的回复:]

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.cli……
[/Quote]
均无法响应 必须重启一下
WWWWA 2012-06-15
  • 打赏
  • 举报
 回复
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
速度如何
wwlaiyyf 2012-06-15
  • 打赏
  • 举报
 回复
[Quote=引用 11 楼 的回复:]

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……
[/Quote]

索引建立了之后 我再运行
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运行的
WWWWA 2012-06-15
  • 打赏
  • 举报
 回复
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

贴结果，估计是运行速度慢了，你没有建立所需的索引
wwlaiyyf 2012-06-15
  • 打赏
  • 举报
 回复
[Quote=引用 7 楼 的回复:]

建议先学习一下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_……
[/Quote]
用了后直接无响应了 必须重启mysql 谢谢你一直帮我解决问题 你说这一次无响应又是怎么回事呢？
WWWWA 2012-06-15
  • 打赏
  • 举报
 回复
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
wwlaiyyf 2012-06-15
  • 打赏
  • 举报
 回复
[Quote=引用 4 楼 的回复:]

修改成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 (selec……
[/Quote]


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直接死了 必须重启才行
WWWWA 2012-06-15
  • 打赏
  • 举报
 回复
建议先学习一下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
wwlaiyyf 2012-06-15
  • 打赏
  • 举报
 回复
[Quote=引用 4 楼 的回复:]

修改成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 (selec……
[/Quote]


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
wwlaiyyf 2012-06-15
  • 打赏
  • 举报
 回复
[Quote=引用 4 楼 的回复:]

修改成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 (selec……
[/Quote]
我又加了一些条件 用了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
WWWWA 2012-06-15
  • 打赏
  • 举报
 回复
修改成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
wwlaiyyf 2012-06-15
  • 打赏
  • 举报
 回复
[Quote=引用 1 楼 的回复:]

`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……
[/Quote]
但是这样有个问题啊 查询出来的数据 只有order_count中有数据的行数 我如果想要得到 不考虑C.team_id有没有的情况 该如何做呢？
wwlaiyyf 2012-06-15
  • 打赏
  • 举报
 回复
[Quote=引用 1 楼 的回复:]

`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……
[/Quote]
好的我试试 另外这是表的数据


INSERT INTO `order_count` (`id`, `team_id`, `state`, `rstate`, `quantity`, `money`) VALUES

(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);
WWWWA 2012-06-15
  • 打赏
  • 举报
 回复
`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
ACMAIN_CHM 2012-06-15
  • 打赏
  • 举报
 回复
使用了三个LEFT JOIN 速度然后还使用了GROUP和ORDER，不会太快了。

贴出你相关表的 show index from .... 以供分析。
wwlaiyyf 2012-06-15
  • 打赏
  • 举报
 回复
[Quote=引用 23 楼 的回复:]

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……
[/Quote]
我是先
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

结果还是特慢 无响应
wwlaiyyf 2012-06-15
  • 打赏
  • 举报
 回复
我是先
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

结果还是特慢 无响应
WWWWA 2012-06-15
  • 打赏
  • 举报
 回复
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)
加载更多回复（6）
MySQL 查询与高级查询（多表、嵌套和正则表达式）
Re： MySQL 查询与高级查询（多表、嵌套和正则表达式） （查询的能力，显示DBA的功力）============================================= 数据库管理系统的个最重要的功能就是数据查询，数据查询不应只是简单查询数据库中存储的数据，还应该根据需要对数据进行筛选，以及确定数据以什么样的格式显示。 MySQL提供了功能强大、灵活的语句来实现这些操作，本章将介绍如何使用 SELECT 语句查询数据表中的一列或多列数据、使用集合函数显示查询结果、连接查询、子查询以及使用正则表达式进行查询等。------------------------------------  
解决mysql卡死的问题彻底优化mysql性能
同时在线访问量继续增大 对于1G内存的服务器明显感觉到吃力 严重时甚至每天都会死机 或者时不时的服务器卡一下 这个问题曾经困扰了我半个多月 MySQL使用是很具伸缩性的算法，因此你通常能用很少的内存运行或给MySQL更多的被存以得到更好的性能。 安装好mysql后，配制文件应该在/usr/local/mysql/share/mysql目录中，配制文件有几个，有my-huge.cnf my-medi...
mysql 查询重复记录 取第一条_取出重复记录的第一条
这几天在查询购买记录，其中一个需求就是查出来客户首次购买的产品时间和产品名称。说白了，就是在一段时间内，去取出来用户的第一次购买记录，再清楚一些就是在一堆重复的记录中取出第一条购买记录。这个问题捉只让我困扰了一段时间，自己最存储过程还是不能驾轻就熟，所以还是得按照传统的方法来进行查询了。要查询的记录如下：而我们要取出来的记录如下：事实上，这样的记录如果取出来第一条记录还是比较好取的，因为这里面有购...
mysql表查看方法_查看mysql表的方法
同时在线访问量继续增大 对于1G内存的服务器明显感觉到吃力严重时甚至每天都会死机 或者时不时的服务器卡一下 这个问题曾经困扰了我半个多月MySQL使用是很具伸缩性的算法，因此你通常能用很少的内存运行或给MySQL更多的被存以得到更好的性能。安装好mysql后，配制文件应该在/usr/local/mysql/share/mysql目录中，配制文件有几个，有my- huge.c...
mysql 中中间表是什么意思_为什么会有这么多中间表？
中间表的由来中间表是数据库中专门存放中间计算结果的数据表。报表系统中的中间表是普遍存在的。那么，这些中间表是如何出现的？为什么中间表会越来越多？中间表会给项目组带来什么样的困扰，如何解决这些困扰？这里我们就尝试探讨一下这个问题。中间表出现的典型场景主要有三个：一步算不出来。数据库中的原始数据表要经过复杂计算，才能在报表上展现出来。一个 SQL 很难实现这样的复杂计算。要连续多个 SQL 实现，前面...
MySQL

56,682

社区成员

56,709

社区内容

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章