between不适用于mysql中的所有查询？

hl0ma9xz 于 2021-06-25 发布在 Mysql

关注(0)|答案(2)|浏览(424)

关闭。这个问题需要细节或清晰。它目前不接受答案。
**想改进这个问题吗？**通过编辑这个帖子来添加细节并澄清问题。

两年前关门了。
改进这个问题
所以我有一些代码，其中它提取了某个特定联盟的数据，你将和你的朋友在一起，也就是：

<?php //Take the information for the league
      $leagueinfo = $link->query("SELECT * FROM leagues WHERE joincode='$joincode'");

      $info = $leagueinfo->fetch_assoc();
        $info['league_id'];
        $info['league_name'];
        $info['start_date'];
        $info['end_date']; ?>

第二个问题

$result = $link->query("SELECT SUM(DISTINCT step_count.steps) as total,  logins.nickname, MAX(step_count.steps) as maxsteps,
        COUNT(DISTINCT leagues.league_id) as leaguecount, ROUND(AVG (DISTINCT step_count.steps)) as average
            FROM step_count
            INNER JOIN logins on
              step_count.unique_id=logins.unique_id
            INNER JOIN leagues ON
              leagues.unique_id=logins.unique_id
            WHERE step_count.date BETWEEN '$info[start_date]' AND '$info[end_date]'
            GROUP BY logins.unique_id
            ORDER BY `total` DESC
          ", MYSQLI_USE_RESULT)

这是我的排行榜：

unique_id | league_id | league_name | start_date | end_date   | Active | joincode
        1 |         1 | Test        | 2018-01-01 | 2018-01-05 |      1 | testjoincode

这是我的步骤计数表：

unique_id | date       | steps
        1 | 2018-01-01 | 200
        1 | 2018-01-07 | 200
        2 | 2018-01-07 | 300

然后我想使用$info中的信息添加到第二个查询中，以便它在开始日期和结束日期之间提取数据。它正确地拉取用户，这样就不会显示unique\u id[2]，但是它显示unique\u id[1]有400个步骤，而他们应该只有200个步骤，这意味着它只是拉取用户的所有信息，所以有没有办法确保这两者之间的关系适用于所有内容？

mysql php mysqli

来源：https://stackoverflow.com/questions/48184459/between-not-applying-to-all-of-query-in-mysql

2条答案

按热度按时间

7d7tgy0s1#

问题在于变量名 $info['start_date'] 以及 $info['end_date'] .
有三种方法可以解决这个问题。
把你的衣服包起来 PHP 变量 {} ```
$result = $link->query("SELECT SUM(DISTINCT step_count.steps) as total, logins.nickname, MAX(step_count.steps) as maxsteps,
COUNT(DISTINCT leagues.league_id) as leaguecount, ROUND(AVG (DISTINCT step_count.steps)) as average
FROM step_count
INNER JOIN logins on
step_count.unique_id=logins.unique_id
INNER JOIN leagues ON
leagues.unique_id=logins.unique_id
WHERE leagues.joincode='$joincode'
BETWEEN '{$info['start_date']}' AND '{$info['end_date']}'
GROUP BY logins.unique_id
ORDER BY total DESC
", MYSQLI_USE_RESULT);

打开/关闭双引号，如下所示：

$result = $link->query("SELECT SUM(DISTINCT step_count.steps) as total, logins.nickname, MAX(step_count.steps) as maxsteps,
COUNT(DISTINCT leagues.league_id) as leaguecount, ROUND(AVG (DISTINCT step_count.steps)) as average
FROM step_count
INNER JOIN logins on
step_count.unique_id=logins.unique_id
INNER JOIN leagues ON
leagues.unique_id=logins.unique_id
WHERE leagues.joincode='$joincode'
BETWEEN '" . $info['start_date'] . "' AND '" . $info['end_date'] . "'
GROUP BY logins.unique_id
ORDER BY total DESC
", MYSQLI_USE_RESULT);

从数组索引中删除单引号：

$result = $link->query("SELECT SUM(DISTINCT step_count.steps) as total, logins.nickname, MAX(step_count.steps) as maxsteps,
COUNT(DISTINCT leagues.league_id) as leaguecount, ROUND(AVG (DISTINCT step_count.steps)) as average
FROM step_count
INNER JOIN logins on
step_count.unique_id=logins.unique_id
INNER JOIN leagues ON
leagues.unique_id=logins.unique_id
WHERE leagues.joincode='$joincode'
BETWEEN '$info[start_date]' AND '$info[end_date]'
GROUP BY logins.unique_id
ORDER BY total DESC
", MYSQLI_USE_RESULT);

更新
维持 `step_count` 对于使用聚合的其他列，可以使用 `outer query` .

SELECT SUM(DISTINCT z.steps) as total, z.nickname, MAX(z.steps) as maxsteps,
COUNT(DISTINCT z.league_id) as leaguecount, ROUND(AVG (DISTINCT z.steps)) as average
FROM
(
SELECT step_count.steps, logins.nickname,
leagues.league_id
FROM step_count
INNER JOIN logins on
step_count.unique_id=logins.unique_id
INNER JOIN leagues ON
leagues.unique_id=logins.unique_id
WHERE leagues.joincode='$joincode'
BETWEEN '{$info['start_date']}' AND '{$info['end_date']}'
GROUP BY logins.unique_id
) AS z

ORDER BY total DESC

赞(0）回复(0）举报 2021-06-25

plicqrtu2#

添加时间（例如，在2018-01-01 00:00am和2018-01-01 23:59pm之间）

赞(0）回复(0）举报 2021-06-25