Summary of some common SQL statements

Source: Internet
Author: User

--Check the name of the best selling product
Select Goods_name from Indent_detail GROUP by goods_id ORDER by sum (goods_num) limit 1;

--Query the name of the product Zhang San purchased
Select Goods_name
From the user join indent on indent.user_id= ' user '. Id
Join Indent_detail on indent.id=indent_detail.indent_id
where Username= ' Zhansan '

--Query the user name with the most consumption amount
Select username
From indent join user on ' user '. id=indent.user_id
GROUP by indent.user_id
ORDER by sum (Total_price) DESC
Limit 1

--The name of the province that inquires the most orders
Select Region_name from indent join region
On indent.province = region.region_id
GROUP by Indent.province
Order BY Count (*) DESC LIMIT 1

--Query the number of orders for each province in the year 2014 and the total order Amount display format province name order quantity order Total amount
Select Region_name, COUNT (*) as Number,sum (Total_price) as total from the indent join region
On indent.province = region.region_id
Where year (add_time) =2014
GROUP by Indent.province

--Query the number of orders in Shandong Province in the last three months
Select COUNT (*) as number from indent join region
On indent.province = region.region_id
where region_name= ' Anhui '
and Date_sub (Curdate (), INTERVAL 3 MONTH) <=add_time

--Check out the top 10 product names sold in the last three days
Select Goods_name
From Indent_detail join indent on indent.id=indent_detail.indent_id
where To_days (now ())-to_days (add_time) <=3
GROUP by goods_id ORDER by sum (goods_num) DESC limit 10;

Select Goods_name
From Indent_detail join indent on indent.id=indent_detail.indent_id
where Date_sub (Curdate (), INTERVAL 3 day) <=add_time
GROUP by goods_id ORDER by sum (goods_num) DESC limit 10;

--inquire about the last three days ' order in Chuzhou, Anhui Province
SELECT * From indent join region
On indent.city = region.region_id
where Region_name= ' Chuzhou '
and Date_sub (Curdate (), INTERVAL 3 MONTH) <=add_time

Summary of some common SQL statements

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.