Demo表(一定要要注意date的格式为datetime)
-
计算所有产品数量
SELECT SUM( num ) AS n
FROM `demo`
-
按产品名排列并计算每个产品的总数量
SELECT name, SUM( num ) AS n
FROM `demo`
GROUP BY name
LIMIT 0 , 30
-
计算总销售额
SELECT SUM( price * num ) AS total
FROM `demo`
-
按产品排列并计算出每个产品销售总数和销售额
SELECT name, SUM( num ) AS N, SUM( price * num ) AS total
FROM `demo`
GROUP BY name
LIMIT 0 , 30
-
按产品排列查询每个产品的平均价
SELECT name, AVG( price ) AS AVG
FROM `demo`
GROUP BY name
LIMIT 0 , 30
-
按日期排列查询是销售产品的数量和销售额
SELECT DATE( DATE ) AS
DAY , SUM( num ) AS N, SUM( price * num ) AS total
FROM `demo`
GROUP BY DAY
LIMIT 0 , 30
-
按日期和商品名排列查询是销售产品的数量和销售额
SELECT DATE( DATE ) AS
DAY , name, SUM( num ) AS N, SUM( price * num ) AS total
FROM `demo`
GROUP BY DAY , name
LIMIT 0 , 30
-
按日期排列查询是销售产品的数量和销售额在8月1-15日(一定要设置时间)
SELECT DATE( DATE ) AS
DAY , SUM( num ) AS N, SUM( price * num ) AS total
FROM `demo`
WHERE DATE
BETWEEN '2023-8-1 00:00:00'
AND '2023-8-15 23:59:59'
GROUP BY DAY
LIMIT 0 , 30
-
按日期排列查询是销售产品的数量和销售额在8月1-18日(一定要设置时间)
SELECT DATE( DATE ) AS
DAY , SUM( num ) AS N, SUM( price * num ) AS total
FROM `demo`
WHERE DATE
BETWEEN '2023-8-1 00:00:00'
AND '2023-8-18 23:59:59'
GROUP BY DAY
LIMIT 0 , 30
-
Join命令格式
SELECT *
FROM table1
JOIN table2 ON table1.column = table2.column
JOIN table3 ON table2.column = table3.column;
-
中文字段名使用案例
$sql=" SELECT DATE(`操作时间`) AS once_date, SUM(`数量`) AS total_num , SUM(`金额`) AS total_amount ,SUM(`进价`) AS total_cost
FROM `商品信息` JOIN `尺码颜色` ON `商品信息`.`条码` = `尺码颜色`.`主条码` JOIN `销售记录` ON `销售记录`.`条码`=`尺码颜色`.`子条码`
WHERE `操作时间`
BETWEEN '$startDate ' AND '$endDate'
GROUP BY DATE(`操作时间`)";