英文:
How make request more readable and scalable?
问题
SELECT user_id FROM merchant_data
WHERE user_id IN (
SELECT user_id FROM merchant_data
WHERE merchant_id = 1134
AND created_date = '2022-12-02'
GROUP BY user_id
HAVING COUNT() > 2)
AND merchant_id = 1167
AND created_date = '2022-12-02'
GROUP BY user_id
HAVING COUNT() = 2;
这个查询会返回类似日志表的数据。在这种情况下，我需要获取所有具有2个或更多merchant_id == 1134和2个merchant_id == 1167的行的用户。但如何处理4个、5个或6个类似merchant_id == ...的条件呢？
英文:
i have request:
SELECT user_id FROM merchant_data
WHERE user_id IN (
SELECT user_id FROM merchant_data
WHERE merchant_id = 1134
AND created_date = '2022-12-02'
GROUP BY user_id
HAVING COUNT(*) > 2)
AND merchant_id = 1167
AND created_date = '2022-12-02'
GROUP BY user_id
HAVING COUNT(*) = 2;
That request return me data from something like log table. In this case i need to get all users that have 2 more rows with merchant_id == 1134 and 2 rows merchant_id == 1167. But how make it for 4 or 5 or 6 condition like merchant_id == ...?
答案1
得分: 1
以下是代码的翻译部分：
原始代码部分：
SELECT user_id FROM merchant_data
WHERE created_date = '2022-12-02'
AND merchant_id IN (1134, 1167, 1186, ...)
GROUP BY user_id
HAVING SUM(merchant_id = 1134) >= 2
AND SUM(merchant_id = 1167) >= 2
AND SUM(merchant_id = 1186) >= 2
AND ...
更标准的SQL版本：
SELECT user_id FROM merchant_data
WHERE created_date = '2022-12-02'
AND merchant_id IN (1134, 1167, 1186, ...)
GROUP BY user_id
HAVING COUNT(CASE merchant_id WHEN 1134 THEN 1 END) >= 2
AND COUNT(CASE merchant_id WHEN 1167 THEN 1 END) >= 2
AND COUNT(CASE merchant_id WHEN 1186 THEN 1 END) >= 2
AND ...
请注意，上述内容是代码的翻译，没有其他内容。
英文:
SELECT user_id FROM merchant_data
WHERE created_date = '2022-12-02'
AND merchant_id IN (1134, 1167, 1186, ...)
GROUP BY user_id
HAVING SUM(merchant_id = 1134) >= 2
AND SUM(merchant_id = 1167) >= 2
AND SUM(merchant_id = 1186) >= 2
AND ...
That depends on an odd MySQL feature that booleans are literally the integer values 1 for true and 0 for false, so you can SUM() a boolean expression. You can't do that in standard SQL.
You could make it more standard SQL by using CASE expressions with no ELSE clause. CASE returns NULL if there is no match, and COUNT() will ignore NULLs.
SELECT user_id FROM merchant_data
WHERE created_date = '2022-12-02'
AND merchant_id IN (1134, 1167, 1186, ...)
GROUP BY user_id
HAVING COUNT(CASE merchant_id WHEN 1134 THEN 1 END) >= 2
AND COUNT(CASE merchant_id WHEN 1167 THEN 1 END) >= 2
AND COUNT(CASE merchant_id WHEN 1186 THEN 1 END) >= 2
AND ...
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论