英文:
SQL to query the id with the most count in a table
问题
我有这些表格：
我想查询特定月份（任何月份）中购买量最多的商店ID。我提出了以下查询：
SELECT DISTINCT store_id FROM (SELECT store_id, purchase_date FROM purchase WHERE purchase_date >= '2021-04-01' AND purchase_date <= '2021-04-30';
SELECT DISTINCT store)
我在SQL方面还是初学者，似乎无法继续前进。
英文:
I have these tables here:
and I want to query the store ID with the most purchases in a specific month (any month).
I came up with this query:
SELECT DISTINCT store_id FROM (SELECT store_id, purchase_date FROM purchase where purchase_date >= '2021-04-01' AND purchase_date <= '2021-04-30'
SELECT DISTINCT store)
I am still starting in SQL and I can't seem to move from here.
答案1
得分: 1
以下是翻译好的部分：
假设“most purchases”表示最高销售额而不是销售次数，且“purchase_date”不是文本而是日期类型：
select x.store_id, sum(x.total) total from (
select p.store_id, (pi.price*pi.quantity) total from purchase_item pi
left join purchase p on pi.purchase_id=p.id
where p.purchase_date between '2021-04-01' AND '2021-04-30'
) x
group by x.store_id
order by total desc
limit 1;
如果您只想找到购买次数最多的商店，而不是销售总额：
select p.store_id, count(p.store_id) from purchase p
where p.purchase_date between '2021-04-01' AND '2021-04-30'
group by p.store_id
order by count(p.store_id) desc limit 1
英文:
Assuming that most purchases means highest sales and not the number of sales and purchase_date is not text but of type date:
select x.store_id, sum(x.total) total from (
select p.store_id, (pi.price*pi.quantity) total from purchase_item pi
left join purchase p on pi.purchase_id=p.id
where p.purchase_date between '2021-04-01' AND '2021-04-30'
) x
group by x.store_id
order by total desc
limit 1;
If you want to just find store with maximum number of purchases and not the Total value of sales:
select p.store_id, count(p.store_id) from purchase p
where p.purchase_date between '2021-04-01' AND '2021-04-30'
group by p.store_id
order by count(p.store_id) desc limit 1
答案2
得分: 0
select count(store_id) as counted_store,store_id from purchase where MONTH(purchase_date) = '5' GROUP BY counted_store ORDER BY counted_store DESC limit 1
英文:
you can try this
select count(store_id) as counted_store,store_id from purchase where MONTH(purchase_date) = '5' GROUP BY counted_store ORDER BY counted_store DESC limit 1
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论