select PRODUCT.PRODID,PRODNAME,SUM(QTYSOLD) as 'TOTAL SALES' from PRODUCT join ORDERLINE on PRODUCT.PRODID = ORDERLINE.PRODID group by PRODUCT.PRODID,PRODNAME order by 'TOTAL SALES'基础上显示TOTAL SALES值在2-7之间的
时间: 2024-02-25 11:59:15 浏览: 133
你可以在原有的 SQL 查询的基础上,使用 `HAVING` 子句来过滤 `TOTAL SALES` 值在 2-7 之间的记录。以下是一个示例 SQL 查询:
```
SELECT PRODUCT.PRODID, PRODNAME, SUM(QTYSOLD) AS 'TOTAL SALES'
FROM PRODUCT
JOIN ORDERLINE ON PRODUCT.PRODID = ORDERLINE.PRODID
GROUP BY PRODUCT.PRODID, PRODNAME
HAVING SUM(QTYSOLD) BETWEEN 2 AND 7
ORDER BY 'TOTAL SALES';
```
在上面的查询中,我们使用 `HAVING` 子句来限制 `TOTAL SALES` 值在 2-7 之间的记录。`BETWEEN` 运算符用于指定一个范围,它包括指定的两个值。最后,我们按照 `TOTAL SALES` 排序结果。
相关问题
INSERT 语句与 FOREIGN KEY 约束"FK__ORDERLINE__prodi__546180BB"冲突。该冲突发生于数据库"master",表"dbo.PRODUCT", column 'prodid'。
这个错误是因为你在插入一条数据时,该数据中的prodid列的值在关联表PRODUCT中不存在。这可能是因为你在插入ORDERLINE表数据时,prodid列的值没有正确地匹配PRODUCT表中的prodid列。你需要检查一下你的插入语句,确保prodid的值存在于PRODUCT表中。另外,你还可以检查一下你的外键约束是否设置正确,以确保它与PRODUCT表中的prodid列相关联。
DELIMITER $$ CREATE /*[DEFINER = { user | CURRENT_USER }]*/ PROCEDURE `qy_task`.`peopletask`(IN job VARCHAR(200),IN seriesid VARCHAR(200),IN classid VARCHAR(200),IN prodid VARCHAR(200),IN cycleid VARCHAR(200) ) /*LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string'*/ BEGIN #把表围绕id创建一边然后再关联输出数据 CREATE TEMPORARY TABLE IF NOT EXISTS taskpeople SELECT gst.job_number, '' AS AREA, SUM(gst.amount) AS amount, SUM(gst.weight) AS weight, SUM(gst.area) AS AREA, SUM(qsc.weight) AS taskweight, SUM(qsc.weight)/SUM(gst.weight) AS percent FROM qy_goal_salesman_task gst INNER JOIN qy_goal_area_task gat ON gat.id =gst.area_task_id INNER JOIN qy_goal_company_task gct ON gct.id=gst.company_task_id INNER JOIN qy_goal_sale_cycle_config scc ON scc.id=gct.cycle_id INNER JOIN qy_goal_prod_config gpc ON gpc.id=gst.prod_id AND gpc.cycle_id=scc.id INNER JOIN qy_goal_prod_series_config psc ON psc.id=gpc.prod_series_id AND psc.cycle_id=scc.id INNER JOIN qy_goal_prod_classify_config pcc ON pcc.id = psc.prod_type_id AND pcc.cycle_id=scc.id INNER JOIN qy_goal_client_task_detail ctd ON gst.salesman_id=ctd.id INNER JOIN qingyuan.sys_circle sc ON sc.id=ctd.client_id INNER JOIN crm_c_duty_saler duty ON duty.c_id=sc.id INNER JOIN qyng_sys_user qsu ON qsu.id= duty.User_Id LEFT JOIN qy_sys_countydetail qsc ON qsc.proname=Pro_Name WHERE scc.id=cycleid AND qsc.CreateTime>=(SELECT start_date FROM qy_goal_sale_cycle_config WHERE id=cycleid) AND qsc.CreateTime<=(SELECT end_date FROM qy_goal_sale_cycle_config WHERE id=cycleid) AND qsc.GetTime=(SELECT MAX(GetTime) FROM qy_sys_countydetail) AND gpc.id=prodid AND pcc.id=classid AND psc.id=seriesid AND gst.job_number=job GROUP BY gst.job_number; #将临时表进行数据转换 UPDATE taskpeople SET job_number= ff.User_Name FROM taskpeople e INNER JOIN qyqx.qy_staff ff ON e.job_number=ff.uid; #查出真正的表 SELECT * FROM taskpeople; #删除临时表 DROP TEMPORARY TABLE taskpeople; END$$ DELIMITER ;这段mysql存储过程哪里出现了语法错误
这段 MySQL 存储过程中出现了语法错误,具体来说是在以下这行代码:
```
GROUP BY gst.job_number;
```
末尾多了一个分号,应该改为:
```
GROUP BY gst.job_number;
```