在MySQL中运行多个查询而不使用子查询 [英] Running multiple queries in MySQL without using sub-query

问题描述

我有两个桌子， 一张名为users的表，

I have two tables, one table called users with,

fsname
emailaddress

和名为attempts的第二张表，其中包含emailaddress，score和datetime.

and second table called attempts with emailaddress, score and datetime.

现在我想做的是先按datetime排序attempts表，然后选择attempt表和users表(如果它们具有相同的emailaddress)，然后选择最后的尝试每个唯一的用户.

Now what I wanted to do is first order the attempts table by datetime and then pick then join the attempt table with users table if they have same emailaddress and then pick the final attempts of each unique user.

简而言之，我必须通过连接这些表来选择每个用户的最后一次尝试，这是我为实现此目的而生成的查询，

In short, I have to pick the last attempt of each user by joining these table and this is the query that I have generated to achieve this,

$query = "SELECT 
            distinct users.fsname, users.emailaddress, attempts.score 
            FROM users 
            INNER JOIN attempts 
            ON users.emailaddress = attempts.emailaddress 
            ORDER BY datetime DESC";

此查询首先假定按datetime对attempts进行排序，并且在此处仅选择具有不同名字的值fsname.

This query first suppose to order the attempts by datetime and only pick values with distinct first name which is fsname over here.

但是当我执行上述查询时，即使我将DISTINCT与fsname一起使用，它也会返回具有非唯一值fsname的结果.

but When I execute the above query it returns the result with having non-unique values of fsname eventhough I am using DISTINCT with fsname.

有人可以告诉我为什么DISTINCT不能只选择不同的fsname吗?

Can anyone please tell me why DISTINCT is not working to only pick distinct fsname ?

我已经尝试了DISTINCT fsname和DISTINCT(fsname)，但是这些都不起作用.

I have tried both DISTINCT fsname and DISTINCT(fsname) but none of these are working.

推荐答案

它无法正常运行，并且

It doesn't work as you think it should and the documentation explains the meaning of DISTINCT: it's about distinct rows:

ALL和DISTINCT选项指定是否应返回重复的行. ALL(默认值)指定应返回所有匹配的行，包括重复行. DISTINCT指定从结果集中删除重复的行.同时指定这两个选项是错误的. DISTINCTROW是DISTINCT的同义词.

The ALL and DISTINCT options specify whether duplicate rows should be returned. ALL (the default) specifies that all matching rows should be returned, including duplicates. DISTINCT specifies removal of duplicate rows from the result set. It is an error to specify both options. DISTINCTROW is a synonym for DISTINCT.

(来源: http://dev.mysql. com/doc/refman/5.7/en/select.html )

您需要按用户对行进行分组，以便为​​每个用户获得单行，但是不幸的是，您无法以这种方式获得他们的最新分数. 您可以获得最大，最小，平均分数和其他计算值.检查 GROUP BY聚合函数的列表

You need to group the rows by user in order to get a single row for each user but, unfortunately, you cannot get their most recent score this way. You can get the maximum, minimum, average score and other computed values. Check the list of GROUP BY aggregate functions.

这是获取所需值的查询:

This is the query that gets the values you need:

SELECT u.fsname, u.emailaddress, la.score 
FROM users u
INNER JOIN attempts la                # 'la' from 'last attempt'
    ON u.emailaddress = la.emailaddress
LEFT JOIN attempts mr                 # 'mr' from 'more recent' (than last attempt)
    ON la.emailaddress = mr.emailaddress AND la.datetime < mr.datetime
WHERE mr.datetime IS NULL

工作原理

使用emailaddress作为匹配列，将表users(别名为u)与表attempts(别名为la，是最后尝试"的缩写)连接在一起.这是查询中已经存在的联接，我添加了别名是因为它们从那时起可以帮助您减少编写工作.

How it works

It joins table users (aliased as u) with table attempts (aliased as la, short for "last attempt") using emailaddress as the matching column. It's the join you already have in your query, I added the aliases because they help you write less from that point on.

接下来，它再次连接attempts表(从"比上次尝试最近的中别名为mr).它会将来自la的每次尝试与来自同一用户(由其emailaddress标识的)和来自datetime且具有最新的datetime的所有尝试进行匹配. LEFT JOIN确保la中的每一行至少与mr中的一行匹配. la中不匹配的la行是每个emailaddress具有最大datetime值的行.它们与充满NULL的行(对于mr部分)匹配.

Next, it joins the attempts table again (aliased as mr from "more recent than the last attempt"). It matches each attempt from la with all the attempts from mr of the same user (identified by their emailaddress) and that have a more recent datetime. The LEFT JOIN ensures that each row from la matches at least one row from mr. The rows from la that do not have a match in mr are the rows that have the biggest values of datetime for each emailaddress. They are matched with rows full of NULL (for the mr part).

最后，WHERE子句仅保留从mr中选择的行的datetime列中具有NULL的行.这些行与每个emailaddress值匹配的la最新条目的行.

Finally, the WHERE clause keeps only the rows that have NULL in the datetime column of the row selected from mr. These are the rows that matched the most recent entries from la for each value of emailaddress.

为了快速运行此查询(任何查询！)，需要在JOIN，WHERE，GROUP BY和ORDER BY子句中使用的列上建立索引.

In order to run fast this query (any query!) needs indexes on the columns used in the JOIN, WHERE, GROUP BY and ORDER BY clauses.

您不应使用表attempts中的emailaddress来标识用户.您应该在表users上具有PK(主键)，并将其用作表attempts(以及其他引用用户的表)中的FK(外键).如果emailaddress是表users的PK，则将其更改为UNIQUE INDEX，并使用新的INTEGER AUTO INCREMENT ed列userId作为PK.与字符串列上的索引相比，数字列上的索引速度更快，并且占用的空间更少.

You should not use emailaddress in table attempts to identify the user. You should have a PK (primary key) on table users and use that as a FK (foreign key) in table attempts (and other tables that refer to a user). If emailaddress is the PK of table users change it to an UNIQUE INDEX and use a new INTEGER AUTO INCREMENTed column userId as PK instead. The indexes on numeric columns are faster and use less space than the indexes on string columns.

这篇关于在MySQL中运行多个查询而不使用子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！