You have two tables in a database ,Users and Products.
the table Users has two fields:user_id and username
the table Product has two fields:p_id and user_id
Query the database to get the top 3 users who made the biggest
products .
SELECT u.`username`,COUNT(username) pcount FROM users u ,products p WHERE u.`user_id` = p.`user_id`
GROUP BY u.`username`
ORDER BY pcount DESC
LIMIT 0,3;
Write a database migration that would denormalize the Users and save number of products he made.
方案一:
CREATE TABLE Users2 (
SELECT u.`user_id` Id,u.`username` name,COUNT(username) pcount
FROM users u ,products p WHERE u.`user_id` = p.`user_id`
GROUP BY Id
);
方案二:
SELECT * INTO user2 FROM (
SELECT u.`user_id` Id,u.`username` name,COUNT(username) pcount
FROM users u ,products p WHERE u.`user_id` = p.`user_id`
GROUP BY Id
);
/*mysql 不支持这种操作*/