我正在尝试从3个表中合并数据，如下所示：
SELECT u.USER_ID,u.USERNAME,u.FIRST_NAME,u.LAST_NAME,u.ISACTIVE,
u.ISADMIN, COUNT(m.PURCHASED_ID) AS MOVIES_PURCHASED,
COUNT(r.RENTED_ID) AS MOVIES_RENTED
FROM TBL_USERS AS u
LEFT JOIN TBL_MOVIE_PURCHASED AS m
ON u.USER_ID = m.USER_ID
LEFT JOIN TBL_RENTED_MOVIES AS r
ON u.USER_ID = r.USER_ID
GROUP BY u.USER_ID,u.USERNAME,u.FIRST_NAME,u.LAST_NAME,u.ISACTIVE,u.ISADMIN
ORDER BY MOVIES_PURCHASED, MOVIES_RENTED;
这些表具有以下列：
TBL_USERS :
USER_ID, USERNAME, FIRSTNAME, LASTNAME, ISACTIVE, ISADMIN
TBL_MOVIE_PURCHASED :
USER_ID, MOVIE_ID, PURCHASE_ID, PURCHASED_ON (purchase ID is unique)
TBL_RENTED_MOVIES :
USER_ID, MOVIE_ID, RENTED_ID, RENTED_ON (rented ID is unique )
我正在尝试显示：
1. all contents of TBL_USERS
2. the count of the rented movies for every user from TBL_RENTED_MOVIES,
3. the count of the purchased movies for every user from TBL_MOVIES_PURCHASED.
4. ORDER BY the results based on both COUNTS.
如果说user1租了5部电影并购买了10部电影，则该查询应该返回：
(MOVIES_PURCHASED, MOVIES_RENTED) = (5,10).
而是，查询为两列返回5 * 10 = 50：
(MOVIES_PURCHASED, MOVIES_RENTED) = (50,50)
我知道我加入结果时出错了。我也尝试使用UNION合并结果，但是没有用。有任何想法吗？
因此，理想情况下，输出应为：
USER_ID, USERNAME, FIRST_NAME, LAST_NAME, ISACTIVE, ISADMIN, MOVIES_PURCHASED, MOVIES-RENTED :
1, user1, userFirst, userLast, Active, NotAdmin, 5, 10
。任何帮助表示赞赏。
最佳答案
这是一种方法
CREATE table USERS(USER_ID integer primary key,
USERNAME varchar(50), FIRST_NAME varchar(50),
LAST_NAME varchar(50), ISACTIVE VARCHAR(10)
DEFAULT '0',
ISADMIN VARCHAR(10) DEFAULT '0');
CREATE table MOVIES_PURCHASED(PURCHASE_ID integer primary key,
USER_ID integer,
FOREIGN KEY(USER_ID) REFERENCES USERS(USER_ID),
PURCHASED_ON VARCHAR(50));
CREATE table MOVIES_RENTED(RENTED_ID integer primary key,
USER_ID integer,
FOREIGN KEY(USER_ID) REFERENCES USERS(USER_ID),
RENTED_ON VARCHAR(50));
INSERT INTO USERS (USER_ID, USERNAME, FIRST_NAME, LAST_NAME, ISACTIVE, ISADMIN)
VALUES
(1, 'user1', 'user1FN', 'user1LN', '1', '0'),
(2, 'user2', 'user2FN', 'user2LN', '1', '0'),
(3, 'user3', 'user3FN', 'user3LN', '1', '0'),
(4, 'user4', 'user4FN', 'user4LN', '1', '0'),
(5, 'user5', 'user5FN', 'user5LN', '1', '0');
insert into MOVIES_RENTED (RENTED_ID, USER_ID, RENTED_ON)
values
(1, 5 ,'2014-07-05'),
(2, 4 ,'2014-07-05'),
(3, 5 ,'2014-07-05'),
(4, 4 ,'2014-07-05'),
(5, 5 ,'2014-07-05'),
(6, 5 ,'2014-07-05'),
(7, 3 ,'2014-07-05'),
(8, 2 ,'2014-07-05'),
(9, 2 ,'2014-07-05'),
(10, 1 ,'2014-07-05');
insert into MOVIES_PURCHASED (PURCHASE_ID, USER_ID, PURCHASED_ON)
values
(1, 1 ,'2014-07-05'),
(2, 3 ,'2014-07-05'),
(3, 3 ,'2014-07-05'),
(4, 3 ,'2014-07-05'),
(5, 4 ,'2014-07-05'),
(6, 4 ,'2014-07-05'),
(7, 5 ,'2014-07-05'),
(8, 5 ,'2014-07-05'),
(9, 1 ,'2014-07-05'),
(10, 2 ,'2014-07-05'),
(11, 2 ,'2014-07-05'),
(12, 2 ,'2014-07-05');
SELECT u.*
, SUM(x.source = 'rented') rented
, SUM(x.source = 'purchased') purchased
, COUNT(x.source) total
FROM users u
LEFT
JOIN
( SELECT 'rented' source, user_id FROM movies_rented
UNION ALL
SELECT 'purchased', user_id FROM movies_purchased
) x
ON x.user_id = u.user_id
GROUP
BY user_id
ORDER
BY total DESC;
USER_ID USERNAME FIRST_NAME LAST_NAME ISACTIVE ISADMIN RENTED PURCHASED TOTAL
5 user5 user5FN user5LN 1 0 4 2 6
2 user2 user2FN user2LN 1 0 2 3 5
4 user4 user4FN user4LN 1 0 2 2 4
3 user3 user3FN user3LN 1 0 1 3 4
1 user1 user1FN user1LN 1 0 1 2 3
http://sqlfiddle.com/#!2/8b3c8/10
关于mysql - 从t1中选择SELECT列，在t2中与COUNT个columnS，在t3中与COUNT个列连接，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/24899517/