获取所有用户的下载详细信息
我的表结构---
--
-- 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_downloads
FROM 下载 AS D
左加入壁纸 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，文件名
ORDER BY user_id ASC LIMIT 500
上面的查询只返回已下载壁纸的用户的数据，但我需要列出我的用户表中的所有用户，无论他们的壁纸是否已下载。
哔哔one浏览 179回答 4
4回答
-
蓝山帝景
试试这个查询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这里需要用到子查询。试试这个查询，希望它能正常工作。 -
慕斯王
您的查询中有错误的顺序。由于您从下载开始，您已经过滤掉了没有下载的用户，无论您是否放置了左连接。如果您从用户开始，那应该没问题。SELECT U.user_id, U.login, D.dload_id, W.filename, IFNULL(COUNT(D.dload_id), 0) AS total_downloadsFROM users AS ULEFT JOIN wallpapers AS W on W.user_id = U.user_idLEFT JOIN downloads AS D ON W.wall_id = D.wall_idWHERE D.dload_date BETWEEN '2020-01-01 00:00:00' AND '2020-01-31 00:00:00'GROUP BY U.user_id, filenameORDER BY user_id ASC LIMIT 500 -
小唯快跑啊
您可以使用此查询它使用子查询来计算 wall_id 的数量SELECT U.`user_id`, U.login,W.filename ,D.total_countsFROM wallpapers AS W LEFT Join (SELECT Count(*) total_counts, wall_id FROM downloads AS DWHERE 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_idORDER 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_countsFROM wallpapers AS W LEFT Join (SELECT Count(*) total_counts, wall_id FROM downloads AS DWHERE 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_idORDER 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 | 1db<>在这里摆弄 -
萧十郎
如果您想要结果中的所有用户，那么您应该开始加入Users，然后离开加入其他表。日期的条件也应移至ON子句：SELECT U.user_id, U.login, W.filename, COUNT(D.dload_id) AS total_downloadsFROM users AS ULEFT JOIN wallpapers AS W ON W.user_id = U.user_idLEFT 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.filenameORDER BY U.user_id ASC LIMIT 500没有必要，IFNULL()因为如果没有下载COUNT(D.dload_id)将返回。0请参阅演示。
随时随地看视频慕课网APP
PHP