GROUP BY lname ORDER BY显示错误的结果 [英] GROUP BY lname ORDER BY showing wrong results
本文介绍了GROUP BY lname ORDER BY显示错误的结果的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我写过的这个查询,在销售竞赛中列出前25名,显示错误结果。
用户没有显示应该相当远在名单上。任何想法可能是什么问题?
SELECT u.fname,u.lname,SUM(p.point)as points
FROM comp_sale s,comp_product p,comp_user u
WHERE s.prod_id = p.product_id
AND s.sale_id = u.wp_id
GROUP BY lname
ORDER BY points DESC限制25
表comp_user:
<$ p $ NULL $ auto_increment $ b $ fname varchar(255)NO NULL
lname varchar(255)NO NULL
storename varchar(255)NO NULL
city varchar(255)NO NULL
phone varchar(255)NO NULL
wp_id int(11)NO NULL
type varchar(255)NO NULL
表格comp_sale
prod_id int(11)NO NULL
sale_id int(11)NO NULL
serial varchar(255)NO NULL
表格comp_product
product_id int(11)否PRI NULL auto_increment
description varchar(255)NO NULL
varchar(255)NO NULL
cylinda_num int(11)NO NULL
eel_num int(11)NO NULL
point int(11)NO NULL
SELECT
u.fname,u.lname,SUM(p.point)作为分数
FROM
comp_sale s
JOIN
comp_product p ON s.prod_id = p.product_id
JOIN
comp_user u ON s.sale_id = u.wp_id
GROUP BY
u.fname,u.lname
ORDER BY
points DESC
LIMIT 25
另外,为了清晰起见,使用显式JOIN
This query I've written, listing the top 25 in a sales competition, is showing the wrong results.
Users aren't showing that should be pretty far up on the list. Any idea what the issue could be?
SELECT u.fname, u.lname, SUM(p.point) as points
FROM comp_sale s, comp_product p, comp_user u
WHERE s.prod_id = p.product_id
AND s.sale_id = u.wp_id
GROUP BY lname
ORDER BY points DESC limit 25
table comp_user:
user_id int(11) NO PRI NULL auto_increment
fname varchar(255) NO NULL
lname varchar(255) NO NULL
storename varchar(255) NO NULL
city varchar(255) NO NULL
phone varchar(255) NO NULL
wp_id int(11) NO NULL
type varchar(255) NO NULL
table comp_sale
prod_id int(11) NO NULL
sale_id int(11) NO NULL
serial varchar(255) NO NULL
table comp_product
product_id int(11) NO PRI NULL auto_increment
description varchar(255) NO NULL
type varchar(255) NO NULL
cylinda_num int(11) NO NULL
eel_num int(11) NO NULL
point int(11) NO NULL
解决方案
Try using a proper, ANSI standard GROUP BY
SELECT
u.fname, u.lname, SUM(p.point) as points
FROM
comp_sale s
JOIN
comp_product p ON s.prod_id = p.product_id
JOIN
comp_user u ON s.sale_id = u.wp_id
GROUP BY
u.fname, u.lname
ORDER BY
points DESC
LIMIT 25
Also, use explicit JOINs for clarity
这篇关于GROUP BY lname ORDER BY显示错误的结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文