我的表結構------- 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;“用戶”包含用戶的登錄詳細信息等?！氨诩垺卑脩羯蟼鞯谋诩埡汀跋螺d”包含在線用戶下載的壁紙。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
這里需要用到子查詢。試試這個查詢，希望它能正常工作。
慕斯王
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
小唯快跑啊
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, '[email protected]', '1111', 'A001', NULL, '1'),
(2, '[email protected]', '1111', 'A002', NULL, '1'),
(3, '[email protected]', '1111', 'A003', NULL, '1'),
(4, '[email protected]', '1111', 'A004', NULL, '1'),
(5, '[email protected]', '1111', 'A005', NULL, '1'),
(6, '[email protected]', '1111', 'A006', NULL, '1'),
(7, '[email protected]', '1111', 'A007', NULL, '1'),
(8, '[email protected]', '1111', 'A008', NULL, '1'),
(9, '[email protected]', '1111', 'A009', NULL, '1'),
(10, '[email protected]', '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 | [email protected] | 壁紙2.jpg | 5
1 | [email protected] | 壁紙3.jpg | 6
1 | [email protected] | 壁紙13.jpg | 空
1 | [email protected] | 壁紙16.jpg | 空
1 | [email protected] | 壁紙20.jpg | 無效的
2 | [email protected] | 壁紙1.jpg | 5
2 | [email protected] | 壁紙4.jpg | 2
2 | [email protected] | 壁紙17.jpg | 無效的
3 | [email protected] | 壁紙5.jpg | 13
4 | [email protected] | 壁紙10.jpg | 空
4 | [email protected] | 壁紙14.jpg | 空
4 | [email protected] | 壁紙18.jpg | 無效的
5 | [email protected] | 壁紙6.jpg | 11
5 | [email protected] | 壁紙8.jpg | 空
5 | [email protected] | 壁紙12.jpg | 無效的
6 | [email protected] | 壁紙7.jpg | 13
6 | [email protected] | 壁紙9.jpg | 空
6 | [email protected] | 壁紙11.jpg | 空
6 | [email protected] | 壁紙15.jpg | 空
6 | [email protected] | 壁紙19.jpg | 空
7 | [email protected] | 空 | 空
8 | [email protected] | 空 | 空
9 | [email protected] | 空 | 空
10 | [email protected] | 空 | 無效的
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 | [email protected] | 12 | 壁紙3.jpg | 1
1 | [email protected] | 13 | 壁紙2.jpg | 1
1 | [email protected] | 17 | 壁紙3.jpg | 1
1 | [email protected] | 25 | 壁紙3.jpg | 1
1 | [email protected] | 26 | 壁紙2.jpg | 1
1 | [email protected] | 28 | 壁紙2.jpg | 1
1 | [email protected] | 29 | 壁紙3.jpg | 1
1 | [email protected] | 40 | 壁紙2.jpg | 1
1 | [email protected] | 41 | 壁紙3.jpg | 1
1 | [email protected] | 44 | 壁紙2.jpg | 1
1 | [email protected] | 45 | 壁紙3.jpg | 1
2 | [email protected] | 1 | 壁紙1.jpg | 1
2 | [email protected] | 14 | 壁紙1.jpg | 1
2 | [email protected] | 18 | 壁紙1.jpg | 1
2 | [email protected] | 19 | 壁紙1.jpg | 1
2 | [email protected] | 30 | 壁紙4.jpg | 1
2 | [email protected] | 42 | 壁紙4.jpg | 1
2 | [email protected] | 43 | 壁紙1.jpg | 1
3 | [email protected] | 9 | 壁紙5.jpg | 1
3 | [email protected] | 10 | 壁紙5.jpg | 1
3 | [email protected] | 11 | 壁紙5.jpg | 1
3 | [email protected] | 15 | 壁紙5.jpg | 1
3 | [email protected] | 16 | 壁紙5.jpg | 1
3 | [email protected] | 22 | 壁紙5.jpg | 1
3 | [email protected] | 23 | 壁紙5.jpg | 1
3 | [email protected] | 24 | 壁紙5.jpg | 1
3 | [email protected] | 38 | 壁紙5.jpg | 1
3 | [email protected] | 39 | 壁紙5.jpg | 1
3 | [email protected] | 53 | 壁紙5.jpg | 1
3 | [email protected] | 54 | 壁紙5.jpg | 1
3 | [email protected] | 55 | 壁紙5.jpg | 1
5 | [email protected] | 5 | 壁紙6.jpg | 1
5 | [email protected] | 7 | 壁紙6.jpg | 1
5 | [email protected] | 8 | 壁紙6.jpg | 1
5 | [email protected] | 20 | 壁紙6.jpg | 1
5 | [email protected] | 21 | 壁紙6.jpg | 1
5 | [email protected] | 34 | 壁紙6.jpg | 1
5 | [email protected] | 36 | 壁紙6.jpg | 1
5 | [email protected] | 37 | 壁紙6.jpg | 1
5 | [email protected] | 49 | 壁紙6.jpg | 1
5 | [email protected] | 51 | 壁紙6.jpg | 1
5 | [email protected] | 52 | 壁紙6.jpg | 1
6 | [email protected] | 2 | 壁紙7.jpg | 1
6 | [email protected] | 3 | 壁紙7.jpg | 1
6 | [email protected] | 4 | 壁紙7.jpg | 1
6 | [email protected] | 6 | 壁紙7.jpg | 1
6 | [email protected] | 27 | 壁紙7.jpg | 1
6 | [email protected] | 31 | 壁紙7.jpg | 1
6 | [email protected] | 32 | 壁紙7.jpg | 1
6 | [email protected] | 33 | 壁紙7.jpg | 1
6 | [email protected] | 35 | 壁紙7.jpg | 1
6 | [email protected] | 46 | 壁紙7.jpg | 1
6 | [email protected] | 47 | 壁紙7.jpg | 1
6 | [email protected] | 48 | 壁紙7.jpg | 1
6 | [email protected] | 50 | 壁紙7.jpg | 1
db<>在這里擺弄
蕭十郎
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請參閱演示。
- 4 回答
- 0 關注
- 156 瀏覽
添加回答
舉報
0/150
提交
取消