本周最受欢迎

lx0bsm1f  于 2021-06-20  发布在  Mysql
关注(0)|答案(2)|浏览(249)

我的sql中有两个表：
agames（游戏id（必须匹配newentries id），结束（1或0））
newentries（game\u id，created是它应该检查日期的地方）
我希望得到本周最受欢迎的游戏，它还没有结束（agames.ended=1）
怎样？我试过了，运气不好，希望有人能帮我！

SELECT 
count(id) as a.rank, 
a.game_id 
FROM a.newentries, b.agames 
WHERE b.ended = 0 AND a.created = THIS_WEEK 
order by a.rank 
desc 
limit 1

这是数据集（以及失败的查询尝试）

CREATE TABLE `newentries` (
  `id` int(11) NOT NULL,
  `user` int(11) NOT NULL,
  `game_id` int(11) NOT NULL,
  `score` int(11) NOT NULL,
  `created` datetime NOT NULL,
  `game_name` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `newentries` (`id`, `user`, `game_id`, `score`, `created`, `game_name`) VALUES
(3, 216, 46, 230, '2018-08-06 16:14:26', ''),
(4, 13436, 46, 0, '2018-08-07 12:14:18', ''),
(5, 13436, 46, 91, '2018-08-07 13:49:04', ''),
(6, 13436, 46, 97, '2018-08-07 15:10:10', ''),
(7, 13436, 46, 128, '2018-08-07 15:10:59', ''),
(8, 13436, 46, 75, '2018-08-07 15:48:44', ''),
(9, 13436, 46, 33, '2018-08-07 15:48:53', ''),
(10, 216, 46, 222, '2018-08-08 17:38:41', ''),
(11, 216, 46, 61, '2018-08-08 20:26:52', ''),
(12, 13440, 46, 56, '2018-08-08 20:39:48', ''),
(13, 13440, 46, 97, '2018-08-08 20:40:04', ''),
(14, 216, 46, 90, '2018-08-08 22:21:23', ''),
(15, 13440, 46, 264, '2018-08-09 12:01:09', ''),
(16, 13440, 46, 218, '2018-08-09 12:04:40', ''),
(17, 13440, 46, 746, '2018-08-09 12:05:54', ''),
(18, 13440, 46, 97, '2018-08-09 14:24:41', ''),
(19, 13440, 46, 91, '2018-08-09 14:25:07', ''),
(20, 13440, 46, 57, '2018-08-09 14:25:17', ''),
(21, 13440, 46, 56, '2018-08-09 14:25:27', ''),
(22, 13440, 46, 83, '2018-08-09 14:25:40', ''),
(23, 13440, 46, 395, '2018-08-09 14:25:54', ''),
(24, 13440, 46, 626, '2018-08-09 14:29:23', ''),
(25, 13440, 46, 165, '2018-08-09 17:56:36', ''),
(26, 13440, 46, 5, '2018-08-09 18:11:58', ''),
(27, 13440, 46, 163, '2018-08-09 18:16:32', ''),
(28, 13440, 46, 69, '2018-08-09 18:16:46', ''),
(29, 13440, 46, 54, '2018-08-10 10:31:20', ''),
(30, 13440, 46, 168, '2018-08-10 10:31:56', ''),
(31, 13440, 46, 242, '2018-08-10 10:32:56', 'candyjam'),
(32, 13440, 46, 165, '2018-08-10 11:14:36', 'sheepjump'),
(33, 13440, 47, 874, '2018-08-14 22:13:38', 'boatdash'),
(34, 13440, 21, 139, '2018-08-17 13:23:04', 'fruitblade'),
(35, 13440, 21, 120, '2018-08-17 13:23:25', 'fruitblade'),
(36, 13440, 51, 49, '2018-08-18 16:00:23', 'escapefromaztec'),
(37, 13436, 14, 150, '2018-08-20 10:55:39', ''),
(38, 13436, 14, 76, '2018-08-20 11:01:14', ''),
(39, 13436, 14, 870, '2018-08-20 11:07:21', ''),
(52, 13436, 14, 13, '2018-08-20 11:09:00', ''),
(53, 13436, 14, 13, '2018-08-20 11:09:00', ''),
(54, 13436, 14, 13, '2018-08-20 11:09:00', ''),
(55, 13436, 14, 13, '2018-08-20 11:09:00', '');

CREATE TABLE `agames` (
  `id` int(11) NOT NULL,
  `prize_id` int(11) NOT NULL,
  `game_id` varchar(250) NOT NULL,
  `start` datetime NOT NULL,
  `end` datetime NOT NULL,
  `ended` int(1) NOT NULL DEFAULT '0',
  `winner` int(11) NOT NULL DEFAULT '0',
  `created` datetime NOT NULL,
  `gift_sent` int(1) NOT NULL DEFAULT '0',
  `site` varchar(250) NOT NULL DEFAULT 'bestplayerwins',
  `type` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Data dump for tabellen `agames`
--

INSERT INTO `agames` (`id`, `prize_id`, `game_id`, `start`, `end`, `ended`, `winner`, `created`, `gift_sent`, `site`, `type`) VALUES
(1, 7, 'fruitblade', '2018-08-24 10:31:08', '2018-08-25 08:31:08', 0, 0, '2018-08-24 10:31:08', 0, 'bestplayerwins', 1),
(2, 14, 'billthebowman', '2018-08-24 10:31:08', '2018-08-25 08:31:08', 0, 0, '2018-08-24 10:31:08', 0, 'bestplayerwins', 1),
(3, 11, 'boatdash', '2018-08-24 10:31:08', '2018-08-25 08:31:08', 0, 0, '2018-08-24 10:31:08', 0, 'bestplayerwins', 1),
(4, 3, 'bananajungle', '2018-08-24 10:31:08', '2018-08-25 08:31:08', 0, 0, '2018-08-24 10:31:08', 0, 'bestplayerwins', 1),
(5, 2, 'gravitykid', '2018-08-24 10:31:08', '2018-08-25 08:31:08', 0, 0, '2018-08-24 10:31:08', 0, 'bestplayerwins', 1),
(6, 13, 'candyjam', '2018-08-24 10:31:08', '2018-08-25 08:31:08', 0, 0, '2018-08-24 10:31:08', 0, 'bestplayerwins', 1),
(7, 19, 'mountainhop', '2018-08-24 10:37:36', '2018-08-31 08:37:36', 0, 0, '2018-08-24 10:37:36', 0, 'bestplayerwins', 2),
(8, 17, 'pinball', '2018-08-24 10:37:36', '2018-08-31 08:37:36', 0, 0, '2018-08-24 10:37:36', 0, 'bestplayerwins', 2),
(9, 23, 'fruitblade', '2018-08-24 10:37:36', '2018-08-31 08:37:36', 0, 0, '2018-08-24 10:37:36', 0, 'bestplayerwins', 2),
(10, 20, 'bananajungle', '2018-08-24 10:37:36', '2018-08-31 08:37:36', 0, 0, '2018-08-24 10:37:36', 0, 'bestplayerwins', 2),
(11, 24, 'candyjam', '2018-08-24 10:37:36', '2018-08-31 08:37:36', 0, 0, '2018-08-24 10:37:36', 0, 'bestplayerwins', 2),
(12, 26, 'sheepjump', '2018-08-24 10:37:36', '2018-08-31 08:37:36', 0, 0, '2018-08-24 10:37:36', 0, 'bestplayerwins', 2),
(13, 30, 'escapefromaztec', '2018-08-24 10:41:50', '2018-09-23 08:41:50', 0, 0, '2018-08-24 10:41:50', 0, 'bestplayerwins', 3),
(14, 4, 'pinball', '2018-08-25 00:00:01', '2018-08-25 22:00:01', 0, 0, '2018-08-25 00:00:01', 0, 'bestplayerwins', 1),
(15, 7, 'boatdash', '2018-08-25 00:00:01', '2018-08-25 22:00:01', 0, 0, '2018-08-25 00:00:01', 0, 'bestplayerwins', 1),
(16, 11, 'bananajungle', '2018-08-25 00:00:01', '2018-08-25 22:00:01', 0, 0, '2018-08-25 00:00:01', 0, 'bestplayerwins', 1),
(17, 2, 'candyjam', '2018-08-25 00:00:01', '2018-08-25 22:00:01', 0, 0, '2018-08-25 00:00:01', 0, 'bestplayerwins', 1),
(18, 5, 'escapefromaztec', '2018-08-25 00:00:01', '2018-08-25 22:00:01', 0, 0, '2018-08-25 00:00:01', 0, 'bestplayerwins', 1),
(19, 12, 'gravitykid', '2018-08-25 00:00:01', '2018-08-25 22:00:01', 0, 0, '2018-08-25 00:00:01', 0, 'bestplayerwins', 1),
(20, 10, 'billthebowman', '2018-08-26 00:00:01', '2018-08-26 22:00:01', 0, 0, '2018-08-26 00:00:01', 0, 'bestplayerwins', 1),
(21, 15, 'sheepjump', '2018-08-26 00:00:01', '2018-08-26 22:00:01', 0, 0, '2018-08-26 00:00:01', 0, 'bestplayerwins', 1),
(22, 14, 'pinball', '2018-08-26 00:00:01', '2018-08-26 22:00:01', 0, 0, '2018-08-26 00:00:01', 0, 'bestplayerwins', 1),
(23, 5, 'mountainhop', '2018-08-26 00:00:01', '2018-08-26 22:00:01', 0, 0, '2018-08-26 00:00:01', 0, 'bestplayerwins', 1),
(24, 12, 'gravitykid', '2018-08-26 00:00:01', '2018-08-26 22:00:01', 0, 0, '2018-08-26 00:00:01', 0, 'bestplayerwins', 1),
(25, 7, 'bananajungle', '2018-08-26 00:00:01', '2018-08-26 22:00:01', 0, 0, '2018-08-26 00:00:01', 0, 'bestplayerwins', 1),
(26, 21, 'mountainhop', '2018-08-26 00:00:01', '2018-09-01 22:00:01', 0, 0, '2018-08-26 00:00:01', 0, 'bestplayerwins', 2),
(27, 16, 'gravitykid', '2018-08-26 00:00:01', '2018-09-01 22:00:01', 0, 0, '2018-08-26 00:00:01', 0, 'bestplayerwins', 2),
(28, 25, 'escapefromaztec', '2018-08-26 00:00:01', '2018-09-01 22:00:01', 0, 0, '2018-08-26 00:00:01', 0, 'bestplayerwins', 2),
(29, 22, 'bananajungle', '2018-08-26 00:00:01', '2018-09-01 22:00:01', 0, 0, '2018-08-26 00:00:01', 0, 'bestplayerwins', 2),
(30, 24, 'fruitblade', '2018-08-26 00:00:01', '2018-09-01 22:00:01', 0, 0, '2018-08-26 00:00:01', 0, 'bestplayerwins', 2),
(31, 20, 'boatdash', '2018-08-26 00:00:01', '2018-09-01 22:00:01', 0, 0, '2018-08-26 00:00:01', 0, 'bestplayerwins', 2),
(32, 15, 'pinball', '2018-08-27 00:00:01', '2018-08-27 22:00:01', 0, 0, '2018-08-27 00:00:01', 0, 'bestplayerwins', 1),
(33, 5, 'mountainhop', '2018-08-27 00:00:01', '2018-08-27 22:00:01', 0, 0, '2018-08-27 00:00:01', 0, 'bestplayerwins', 1),
(34, 14, 'fruitblade', '2018-08-27 00:00:01', '2018-08-27 22:00:01', 0, 0, '2018-08-27 00:00:01', 0, 'bestplayerwins', 1),
(35, 3, 'boatdash', '2018-08-27 00:00:01', '2018-08-27 22:00:01', 0, 0, '2018-08-27 00:00:01', 0, 'bestplayerwins', 1),
(36, 2, 'candyjam', '2018-08-27 00:00:01', '2018-08-27 22:00:01', 0, 0, '2018-08-27 00:00:01', 0, 'bestplayerwins', 1),
(37, 9, 'gravitykid', '2018-08-27 00:00:01', '2018-08-27 22:00:01', 0, 0, '2018-08-27 00:00:01', 0, 'bestplayerwins', 1);

SELECT count(id) as a.rank, a.game_id 
FROM a.newentries ne
JOIN b.agames ag ON ne.game_id = ag.game_id
WHERE b.ended = 0 AND a.created BETWEEN CURDATE() - INTERVAL 1 WEEK AND CURDATE()
ORDER BY a.rank DESC LIMIT 1

和一把同样的小提琴：http://sqlfiddle.com/#!9/190069/1

sqlmysqlphpmysqli

2条答案

按热度按时间
i86rm4rw

i86rm4rw1#

或者你可以试试

SELECT count(ne.id) as rank, ne.game_id 
FROM newentries ne
JOIN agames ag ON ne.game_id = ag.game_id
WHERE ag.ended = 0 AND ag.created BETWEEN CURDATE() - INTERVAL 1 WEEK AND CURDATE()
ORDER BY rank DESC LIMIT 1

如果你想得到准确的时间而不是一天，使用 NOW() 而不是 CURDATE() .
没有了 LAST_WEEK mysql中的表达式。看到这样的情况，你可能会感到困惑：

SELECT Id, SomeField, SomeOtherField,
   SUM(IF(SomeDate BETWEEN CURDATE() - INTERVAL 1 WEEK AND CURDATE(), 1, 0) AS THIS_WEEK,
   SUM(IF(SomeDate BETWEEN CURDATE() - INTERVAL 1 WEEK AND CURDATE() - INTERVAL 2 WEEK, 1, 0) AS LAST_WEEK
FROM SomeTable

此示例将添加字段名 THIS_WEEK 以及 LAST_WEEK 到结果集，分别包含本周和上周发生的日期数。这个 AS 关键字用于重命名结果集中的字段或计算。
编辑：对不起，来晚了。你的另一个问题是 SELECT count(id) as a.rank 会给你一个语法错误。 AS 用于重命名结果中的列标题，列标题中不能有句点。这与指定数据来自哪个服务器（a或b）的想法混淆了。
在看到你的小提琴之后， id 确实是模棱两可的，因为您在两个不同的表中使用它作为名称。您必须指定要选择哪一个。我已经编辑了上面的查询。

赞(0）分享  回复(0）举报  2021-06-21
kqhtkvqz

kqhtkvqz2#

请试一试

SELECT count(id) as a.rank, a.game_id 
FROM a.newentries, b.agames 
WHERE b.ended = 0 AND YEARWEEK(a.created, 1) = YEARWEEK(CURDATE(), 1) 
order by a.rank desc limit 1

use可以使用特定的模式，如0->sunday，1->monday。。。。。

赞(0）分享  回复(0）举报  2021-06-20
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com