首页猿问获取所有用户的下载详细信息

获取所有用户的下载详细信息

PHP

哔哔one 2022-10-22 15:26:26

我的表结构------- Database: `sample`--CREATE DATABASE IF NOT EXISTS `sample` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;USE `sample`;-- ------------------------------------------------------------ Table structure for table `downloads`--DROP TABLE IF EXISTS `downloads`;CREATE TABLE IF NOT EXISTS `downloads` ( `dload_id` int(11) NOT NULL AUTO_INCREMENT, `wall_id` int(11) NOT NULL, `dload_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`dload_id`)) ENGINE=MyISAM AUTO_INCREMENT=88 DEFAULT CHARSET=latin1;---- Table structure for table `users`--DROP TABLE IF EXISTS `users`;CREATE TABLE IF NOT EXISTS `users` ( `user_id` int(11) NOT NULL AUTO_INCREMENT, `login` varchar(45) DEFAULT NULL, `password` varchar(45) DEFAULT NULL, `folder_id` varchar(45) DEFAULT NULL, `last_login` varchar(45) DEFAULT NULL, `status` set('0','1') NOT NULL DEFAULT '1', PRIMARY KEY (`user_id`), UNIQUE KEY `folder_id_UNIQUE` (`folder_id`), UNIQUE KEY `login_UNIQUE` (`login`)) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;---- Table structure for table `wallpapers`--DROP TABLE IF EXISTS `wallpapers`;CREATE TABLE IF NOT EXISTS `wallpapers` ( `wall_id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `filename` varchar(100) DEFAULT NULL, `upload_date` datetime DEFAULT CURRENT_TIMESTAMP, `status` set('0','1') DEFAULT '', PRIMARY KEY (`wall_id`)) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;“用户”包含用户的登录详细信息等。“壁纸”包含用户上传的壁纸和“下载”包含在线用户下载的壁纸。SELECT U.user_id, U.login, D.dload_id, W.filename, IFNULL(COUNT(D.dload_id), 0) AS total_downloadsFROM 下载 AS D左加入壁纸 W.wall_id = D.wall_idLEFT JOIN users AS U on W.user_id = U.user_idWHERE D.dload_date BETWEEN '2020-01-01 00:00:00' AND '2020-01-31 00:00:00'GROUP BY U.user_id，文件名ORDER BY user_id ASC LIMIT 500上面的查询只返回已下载壁纸的用户的数据，但我需要列出我的用户表中的所有用户，无论他们的壁纸是否已下载。

查看完整描述

4 回答

蓝山帝景

TA贡献1843条经验获得超7个赞

试试这个查询

SELECT U.user_id, U.login,N.dload_id,N.filename,N.total_downloads

FROM `users` U left join

(

SELECT D.wall_id,W.filename,W.user_id,D.dload_id,count(D.wall_id) as total_downloads

FROM `downloads` D

left join wallpapers W on D.wall_id=W.wall_id

where D.dload_date BETWEEN '2020-01-01 00:00:00' AND '2020-01-31 00:00:00'

GROUP BY D.`wall_id`

) N on U.user_id=N.user_id

这里需要用到子查询。试试这个查询，希望它能正常工作。

反对回复 2022-10-22

慕斯王

TA贡献1864条经验获得超2个赞

您的查询中有错误的顺序。由于您从下载开始，您已经过滤掉了没有下载的用户，无论您是否放置了左连接。如果您从用户开始，那应该没问题。

SELECT U.user_id, U.login, D.dload_id, W.filename, IFNULL(COUNT(D.dload_id), 0) AS total_downloads

FROM users AS U

LEFT JOIN wallpapers AS W on W.user_id = U.user_id

LEFT JOIN downloads AS D ON W.wall_id = D.wall_id

WHERE D.dload_date BETWEEN '2020-01-01 00:00:00' AND '2020-01-31 00:00:00'

GROUP BY U.user_id, filename

ORDER BY user_id ASC LIMIT 500

反对回复 2022-10-22

小唯快跑啊

TA贡献1863条经验获得超2个赞

您可以使用此查询

它使用子查询来计算 wall_id 的数量

SELECT

U.`user_id`

, U.login

,W.filename

,D.total_counts

FROM wallpapers AS W LEFT Join (

SELECT Count(*) total_counts, wall_id FROM downloads AS D

WHERE D.dload_date BETWEEN '2020-01-01 00:00:00' AND '2020-01-31 00:00:00'

GROUP BY wall_id) D ON D.wall_id = W.wall_id RIGHT JOIN users AS U on W.user_id = U.user_id

ORDER BY U.user_id,W.wall_id;

-- Table structure for table `downloads`

DROP TABLE IF EXISTS `downloads`;

CREATE TABLE IF NOT EXISTS `downloads` (

`dload_id` int(11) NOT NULL AUTO_INCREMENT,

`wall_id` int(11) NOT NULL,

`dload_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (`dload_id`)

) ENGINE=MyISAM AUTO_INCREMENT=88 DEFAULT CHARSET=latin1;

✓

-- Table structure for table `users`

DROP TABLE IF EXISTS `users`;

CREATE TABLE IF NOT EXISTS `users` (

`user_id` int(11) NOT NULL AUTO_INCREMENT,

`login` varchar(45) DEFAULT NULL,

`password` varchar(45) DEFAULT NULL,

`folder_id` varchar(45) DEFAULT NULL,

`last_login` varchar(45) DEFAULT NULL,

`status` set('0','1') NOT NULL DEFAULT '1',

PRIMARY KEY (`user_id`),

UNIQUE KEY `folder_id_UNIQUE` (`folder_id`),

UNIQUE KEY `login_UNIQUE` (`login`)

) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

✓

-- Table structure for table `wallpapers`

DROP TABLE IF EXISTS `wallpapers`;

CREATE TABLE IF NOT EXISTS `wallpapers` (

`wall_id` int(11) NOT NULL AUTO_INCREMENT,

`user_id` int(11) NOT NULL,

`filename` varchar(100) DEFAULT NULL,

`upload_date` datetime DEFAULT CURRENT_TIMESTAMP,

`status` set('0','1') DEFAULT '',

PRIMARY KEY (`wall_id`)

) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;

✓

INSERT INTO `downloads` (`dload_id`, `wall_id`, `dload_date`) VALUES

(1, 1, '2020-01-01 00:00:00'),

(2, 7, '2020-01-01 00:00:00'),

(3, 7, '2020-01-01 00:00:00'),

(4, 7, '2020-01-01 00:00:00'),

(5, 6, '2020-01-02 00:00:00'),

(6, 7, '2020-01-02 00:00:00'),

(7, 6, '2020-01-02 00:00:00'),

(8, 6, '2020-01-02 00:00:00'),

(9, 5, '2020-01-02 00:00:00'),

(10, 5, '2020-01-03 00:00:00'),

(11, 5, '2020-01-03 00:00:00'),

(12, 3, '2020-01-03 00:00:00'),

(13, 2, '2020-01-04 00:00:00'),

(14, 1, '2020-01-04 00:00:00'),

(15, 5, '2020-01-04 00:00:00'),

(16, 5, '2020-01-04 00:00:00'),

(17, 3, '2020-01-05 00:00:00'),

(18, 1, '2020-01-06 00:00:00'),

(19, 1, '2020-01-06 00:00:00'),

(20, 6, '2020-01-07 00:00:00'),

(21, 6, '2020-01-08 00:00:00'),

(22, 5, '2020-01-10 00:00:00'),

(23, 5, '2020-01-11 00:00:00'),

(24, 5, '2020-01-11 00:00:00'),

(25, 3, '2020-01-13 00:00:00'),

(26, 2, '2020-01-13 00:00:00'),

(27, 7, '2020-01-15 00:00:00'),

(28, 2, '2020-01-16 00:00:00'),

(29, 3, '2020-01-16 00:00:00'),

(30, 4, '2020-01-16 00:00:00'),

(31, 7, '2020-01-18 00:00:00'),

(32, 7, '2020-01-18 00:00:00'),

(33, 7, '2020-01-20 00:00:00'),

(34, 6, '2020-01-21 00:00:00'),

(35, 7, '2020-01-21 00:00:00'),

(36, 6, '2020-01-21 00:00:00'),

(37, 6, '2020-01-22 00:00:00'),

(38, 5, '2020-01-23 00:00:00'),

(39, 5, '2020-01-24 00:00:00'),

(40, 2, '2020-01-25 00:00:00'),

(41, 3, '2020-01-25 00:00:00'),

(42, 4, '2020-01-26 00:00:00'),

(43, 1, '2020-01-26 00:00:00'),

(44, 2, '2020-01-26 00:00:00'),

(45, 3, '2020-01-28 00:00:00'),

(46, 7, '2020-01-28 00:00:00'),

(47, 7, '2020-01-29 00:00:00'),

(48, 7, '2020-01-29 00:00:00'),

(49, 6, '2020-01-29 00:00:00'),

(50, 7, '2020-01-29 00:00:00'),

(51, 6, '2020-01-29 00:00:00'),

(52, 6, '2020-01-29 00:00:00'),

(53, 5, '2020-01-29 00:00:00'),

(54, 5, '2020-01-29 00:00:00'),

(55, 5, '2020-01-29 00:00:00'),

(56, 3, '2020-02-01 00:00:00'),

(57, 2, '2020-02-01 00:00:00'),

(58, 1, '2020-02-01 00:00:00'),

(59, 5, '2020-02-01 00:00:00'),

(60, 3, '2020-02-02 00:00:00'),

(61, 2, '2020-02-02 00:00:00'),

(62, 1, '2020-02-02 00:00:00'),

(63, 2, '2020-02-02 00:00:00'),

(64, 3, '2020-02-02 00:00:00'),

(65, 4, '2020-02-03 00:00:00'),

(66, 2, '2020-02-03 00:00:00'),

(67, 2, '2020-02-03 00:00:00'),

(68, 5, '2020-02-05 00:00:00'),

(69, 5, '2020-02-05 00:00:00'),

(70, 5, '2020-02-05 00:00:00'),

(71, 5, '2020-02-06 00:00:00'),

(72, 7, '2020-02-06 00:00:00'),

(73, 7, '2020-02-07 00:00:00'),

(74, 7, '2020-02-08 00:00:00'),

(75, 6, '2020-02-09 00:00:00'),

(76, 7, '2020-02-09 00:00:00'),

(77, 6, '2020-02-12 00:00:00'),

(78, 6, '2020-02-12 00:00:00'),

(79, 5, '2020-02-12 00:00:00'),

(80, 5, '2020-02-16 00:00:00'),

(81, 5, '2020-02-16 00:00:00'),

(82, 3, '2020-02-18 00:00:00'),

(83, 2, '2020-02-18 00:00:00'),

(84, 1, '2020-02-20 00:00:00'),

(85, 2, '2020-02-20 00:00:00'),

(86, 3, '2020-02-21 00:00:00'),

(87, 4, '2020-02-21 00:00:00');

INSERT INTO `users` (`user_id`, `login`, `password`, `folder_id`, `last_login`, `status`) VALUES

(1, 'user1@example.com', '1111', 'A001', NULL, '1'),

(2, 'user2@example.com', '1111', 'A002', NULL, '1'),

(3, 'user3@example.com', '1111', 'A003', NULL, '1'),

(4, 'user4@example.com', '1111', 'A004', NULL, '1'),

(5, 'user5@example.com', '1111', 'A005', NULL, '1'),

(6, 'user6@example.com', '1111', 'A006', NULL, '1'),

(7, 'user7@example.com', '1111', 'A007', NULL, '1'),

(8, 'user8@example.com', '1111', 'A008', NULL, '1'),

(9, 'user9@example.com', '1111', 'A009', NULL, '1'),

(10, 'user10@example.com', '1111', 'A010', NULL, '1');

INSERT INTO `wallpapers` (`wall_id`, `user_id`, `filename`, `upload_date`, `status`) VALUES

(1, 2, 'wallpaper1.jpg', '2020-02-01 00:00:00', '1'),

(2, 1, 'wallpaper2.jpg', '2020-02-01 00:00:00', '1'),

(3, 1, 'wallpaper3.jpg', '2020-02-01 00:00:00', '1'),

(4, 2, 'wallpaper4.jpg', '2020-02-01 00:00:00', '1'),

(5, 3, 'wallpaper5.jpg', '2020-02-01 00:00:00', '1'),

(6, 5, 'wallpaper6.jpg', '2020-02-08 00:00:00', '1'),

(7, 6, 'wallpaper7.jpg', '2020-02-08 00:00:00', '1'),

(8, 5, 'wallpaper8.jpg', '2020-02-08 00:00:00', '1'),

(9, 6, 'wallpaper9.jpg', '2020-02-08 00:00:00', '1'),

(10, 4, 'wallpaper10.jpg', '2020-02-08 00:00:00', '1'),

(11, 6, 'wallpaper11.jpg', '2020-02-09 00:00:00', '1'),

(12, 5, 'wallpaper12.jpg', '2020-02-09 00:00:00', '1'),

(13, 1, 'wallpaper13.jpg', '2020-02-09 00:00:00', '1'),

(14, 4, 'wallpaper14.jpg', '2020-02-10 00:00:00', '1'),

(15, 6, 'wallpaper15.jpg', '2020-02-10 00:00:00', '1'),

(16, 1, 'wallpaper16.jpg', '2020-02-10 00:00:00', '1'),

(17, 2, 'wallpaper17.jpg', '2020-02-13 00:00:00', '1'),

(18, 4, 'wallpaper18.jpg', '2020-02-13 00:00:00', '1'),

(19, 6, 'wallpaper19.jpg', '2020-02-19 00:00:00', '1'),

(20, 1, 'wallpaper20.jpg', '2020-02-19 00:00:00', '1');

✓

SELECT

U.`user_id`

, U.login

,W.filename

,D.total_counts

FROM wallpapers AS W LEFT Join (

SELECT Count(*) total_counts, wall_id FROM downloads AS D

WHERE D.dload_date BETWEEN '2020-01-01 00:00:00' AND '2020-01-31 00:00:00'

GROUP BY wall_id) D ON D.wall_id = W.wall_id RIGHT JOIN users AS U on W.user_id = U.user_id

ORDER BY U.user_id,W.wall_id;

用户 ID | 登录 | 文件名 | total_counts

------: | :----------------- | :---------------- | ------------:

1 | user1@example.com | 壁纸2.jpg | 5

1 | user1@example.com | 壁纸3.jpg | 6

1 | user1@example.com | 壁纸13.jpg | 空

1 | user1@example.com | 壁纸16.jpg | 空

1 | user1@example.com | 壁纸20.jpg | 无效的

2 | user2@example.com | 壁纸1.jpg | 5

2 | user2@example.com | 壁纸4.jpg | 2

2 | user2@example.com | 壁纸17.jpg | 无效的

3 | user3@example.com | 壁纸5.jpg | 13

4 | user4@example.com | 壁纸10.jpg | 空

4 | user4@example.com | 壁纸14.jpg | 空

4 | user4@example.com | 壁纸18.jpg | 无效的

5 | user5@example.com | 壁纸6.jpg | 11

5 | user5@example.com | 壁纸8.jpg | 空

5 | user5@example.com | 壁纸12.jpg | 无效的

6 | user6@example.com | 壁纸7.jpg | 13

6 | user6@example.com | 壁纸9.jpg | 空

6 | user6@example.com | 壁纸11.jpg | 空

6 | user6@example.com | 壁纸15.jpg | 空

6 | user6@example.com | 壁纸19.jpg | 空

7 | user7@example.com | 空 | 空

8 | user8@example.com | 空 | 空

9 | user9@example.com | 空 | 空

10 | user10@example.com | 空 | 无效的

SELECT U.user_id, U.login, D.dload_id, W.filename, IFNULL(COUNT(D.dload_id), 0) AS total_downloads

FROM downloads AS D

LEFT JOIN wallpapers AS W ON W.wall_id = D.wall_id

LEFT JOIN users AS U on W.user_id = U.user_id

WHERE D.dload_date BETWEEN '2020-01-01 00:00:00' AND '2020-01-31 00:00:00'

GROUP BY U.user_id, U.login, D.dload_id, W.filename

ORDER BY user_id ASC LIMIT 500

用户 ID | 登录 | dload_id | 文件名 | total_downloads

------: | :---------------- | --------: | :------------- | --------------:

1 | user1@example.com | 12 | 壁纸3.jpg | 1

1 | user1@example.com | 13 | 壁纸2.jpg | 1

1 | user1@example.com | 17 | 壁纸3.jpg | 1

1 | user1@example.com | 25 | 壁纸3.jpg | 1

1 | user1@example.com | 26 | 壁纸2.jpg | 1

1 | user1@example.com | 28 | 壁纸2.jpg | 1

1 | user1@example.com | 29 | 壁纸3.jpg | 1

1 | user1@example.com | 40 | 壁纸2.jpg | 1

1 | user1@example.com | 41 | 壁纸3.jpg | 1

1 | user1@example.com | 44 | 壁纸2.jpg | 1

1 | user1@example.com | 45 | 壁纸3.jpg | 1

2 | user2@example.com | 1 | 壁纸1.jpg | 1

2 | user2@example.com | 14 | 壁纸1.jpg | 1

2 | user2@example.com | 18 | 壁纸1.jpg | 1

2 | user2@example.com | 19 | 壁纸1.jpg | 1

2 | user2@example.com | 30 | 壁纸4.jpg | 1

2 | user2@example.com | 42 | 壁纸4.jpg | 1

2 | user2@example.com | 43 | 壁纸1.jpg | 1

3 | user3@example.com | 9 | 壁纸5.jpg | 1

3 | user3@example.com | 10 | 壁纸5.jpg | 1

3 | user3@example.com | 11 | 壁纸5.jpg | 1

3 | user3@example.com | 15 | 壁纸5.jpg | 1

3 | user3@example.com | 16 | 壁纸5.jpg | 1

3 | user3@example.com | 22 | 壁纸5.jpg | 1

3 | user3@example.com | 23 | 壁纸5.jpg | 1

3 | user3@example.com | 24 | 壁纸5.jpg | 1

3 | user3@example.com | 38 | 壁纸5.jpg | 1

3 | user3@example.com | 39 | 壁纸5.jpg | 1

3 | user3@example.com | 53 | 壁纸5.jpg | 1

3 | user3@example.com | 54 | 壁纸5.jpg | 1

3 | user3@example.com | 55 | 壁纸5.jpg | 1

5 | user5@example.com | 5 | 壁纸6.jpg | 1

5 | user5@example.com | 7 | 壁纸6.jpg | 1

5 | user5@example.com | 8 | 壁纸6.jpg | 1

5 | user5@example.com | 20 | 壁纸6.jpg | 1

5 | user5@example.com | 21 | 壁纸6.jpg | 1

5 | user5@example.com | 34 | 壁纸6.jpg | 1

5 | user5@example.com | 36 | 壁纸6.jpg | 1

5 | user5@example.com | 37 | 壁纸6.jpg | 1

5 | user5@example.com | 49 | 壁纸6.jpg | 1

5 | user5@example.com | 51 | 壁纸6.jpg | 1

5 | user5@example.com | 52 | 壁纸6.jpg | 1

6 | user6@example.com | 2 | 壁纸7.jpg | 1

6 | user6@example.com | 3 | 壁纸7.jpg | 1

6 | user6@example.com | 4 | 壁纸7.jpg | 1

6 | user6@example.com | 6 | 壁纸7.jpg | 1

6 | user6@example.com | 27 | 壁纸7.jpg | 1

6 | user6@example.com | 31 | 壁纸7.jpg | 1

6 | user6@example.com | 32 | 壁纸7.jpg | 1

6 | user6@example.com | 33 | 壁纸7.jpg | 1

6 | user6@example.com | 35 | 壁纸7.jpg | 1

6 | user6@example.com | 46 | 壁纸7.jpg | 1

6 | user6@example.com | 47 | 壁纸7.jpg | 1

6 | user6@example.com | 48 | 壁纸7.jpg | 1

6 | user6@example.com | 50 | 壁纸7.jpg | 1

db<>在这里摆弄

反对回复 2022-10-22

萧十郎

TA贡献1815条经验获得超13个赞

如果您想要结果中的所有用户，那么您应该开始加入Users，然后离开加入其他表。

日期的条件也应移至ON子句：

SELECT U.user_id, U.login, W.filename,

COUNT(D.dload_id) AS total_downloads

FROM users AS U

LEFT JOIN wallpapers AS W ON W.user_id = U.user_id

LEFT JOIN downloads AS D ON W.wall_id = D.wall_id

AND D.dload_date BETWEEN '2020-01-01 00:00:00' AND '2020-01-31 00:00:00'

GROUP BY U.user_id, U.login, W.filename

ORDER BY U.user_id ASC LIMIT 500

没有必要，IFNULL()因为如果没有下载COUNT(D.dload_id)将返回。0请参阅演示。

反对回复 2022-10-22

4 回答
0 关注
178 浏览

关注

添加回答

0/150

提交

取消

热搜

最近搜索清空

获取所有用户的下载详细信息

获取所有用户的下载详细信息

4 回答

添加回答