分析 下列sql 执行失败原因,并修改 update t_goodsprice set modidate = (SELECT NOW()) WHERE GOODSGUID in (select * from ( (SELECT * FROM t_goodsprice t LEFT JOIN t_goods t1 on t1.GOODSGUID = t.GOODSGUID WHERE t.sellstatus = 1 AND t.goodssource = 1 and t1.goodsclassguid = 'sxzfcg-87958' and t1.GOODSBRANDGUID = '1357'))a);
时间: 2024-01-23 18:04:33 浏览: 21
这个 SQL 语句执行失败的原因是在子查询中使用了一个不完整的嵌套查询,导致语法错误。应该在子查询中指定要查询的列名,而不是使用通配符 *。
正确的 SQL 语句应该是:
```
UPDATE t_goodsprice
SET modidate = (SELECT NOW())
WHERE GOODSGUID IN (
SELECT t.GOODSGUID
FROM t_goodsprice t
LEFT JOIN t_goods t1 ON t1.GOODSGUID = t.GOODSGUID
WHERE t.sellstatus = 1
AND t.goodssource = 1
AND t1.goodsclassguid = 'sxzfcg-87958'
AND t1.GOODSBRANDGUID = '1357'
);
```
这里的子查询中指定了要查询的列 `t.GOODSGUID`,同时将查询条件放到了 `WHERE` 子句中。另外,我注意到你使用的是 MySQL 数据库,因此建议在子查询中使用 `JOIN` 而不是嵌套查询,这样可以更清晰地表达查询逻辑。
相关问题
UPDATE GOODS_QUOTATION_OEM_GOODSPRICE SET JGFY =DECODE(XSCL 0.0(XSGZ+XSFY)/XSCL) WHERE BILLID ='OEM2306000009'帮我debug
好的,让我来帮你进行调试。根据你提供的信息,这是一个更新数据库表中的一条记录的SQL语句。根据语法,我发现在DECODE函数中有一个错误。DECODE函数应该包含三个参数,分别是待比较的字段、比较值和返回值。在你的语句中,缺少了一个逗号。请尝试以下修改后的语句:
UPDATE GOODS_QUOTATION_OEM_GOODSPRICE
SET JGFY = DECODE(XSCL, 0.0, (XSGZ+XSFY)/XSCL)
WHERE BILLID = 'OEM2306000009'
请尝试运行修改后的语句,看看是否解决了问题。如果问题仍然存在,请提供更多的信息和错误提示,以便我能够帮助你更好地进行调试。
SELECT a.goods_code goodsCode, a.goods_name goodsName, a.goods_price goodsPrice, a.original_price originalPrice, IFNULL( a.initial_sales, 0 )+ IFNULL( a.real_sales, 0 ) sales, a.thumbnail, CASE WHEN a.exchange_type IS NULL OR a.exchange_type = 0 THEN '不限制' ELSE a.exchange_number END exchangeTypeName, (SELECT MIN(b.redeem_points) redeem_points FROM goods_attr_info b WHERE a.goods_code = b.goods_code ) redeem_points, CASE WHEN (SELECT MIN(b.inventory_number) <= 10 FROM goods_attr_info b WHERE a.goods_code = b.goods_code AND b.inventory_type = 1) THEN CONCAT('剩余',MIN(b.inventory_number),'件') ELSE '' END inventoryNumber FROM goods_main_info a WHERE a.goods_status = 1 这断sql改造成上述语句
可以使用以下 SQL 语句实现:
```
SELECT a.goods_code AS goodsCode,
a.goods_name AS goodsName,
a.goods_price AS goodsPrice,
a.original_price AS originalPrice,
IFNULL(a.initial_sales, 0) + IFNULL(a.real_sales, 0) AS sales,
a.thumbnail,
CASE WHEN a.exchange_type IS NULL OR a.exchange_type = 0 THEN '不限制'
ELSE a.exchange_number END AS exchangeTypeName,
(SELECT MIN(b.redeem_points)
FROM goods_attr_info b
WHERE a.goods_code = b.goods_code) AS redeem_points,
CASE WHEN (SELECT MIN(b.inventory_number)
FROM goods_attr_info b
WHERE a.goods_code = b.goods_code AND b.inventory_type = 1) <= 10
THEN CONCAT('剩余', MIN(b.inventory_number), '件')
ELSE '' END AS inventoryNumber
FROM goods_main_info a
WHERE a.goods_status = 1;
```
这个语句的逻辑与之前的语句相同,只是将子查询部分的逻辑拆分出来,分别使用了两个子查询来计算 `redeem_points` 和 `inventoryNumber`,并且使用了 `CASE WHEN` 语句来判断是否需要输出 `inventoryNumber`。