mysql-MySql查询的问题,关于库存量的查询操作
CREATE TABLE who_tmp_real_stock2
SELECT SUM( num ) AS real_stock,goods_id AS goods_id_new
FROM who_goods_stock
GROUP BY goods_id_new
ALTER TABLE who_tmp_real_stock2
ADD UNIQUE (goods_id_new
)
SELECT a.goods_id as goods_id,cat_id, d.real_stock, a.goods_sn as goods_sn,goods_name,keywords,is_on_sale,is_delete,
is_best,is_new,is_hot,is_promote,shop_price*100 as shop_price,(shop_price*6.3-in_price)*1000 as profit,
last_update,goods_number,goods_material,goods_color,b.goods_sku_value,customer_rate_num,
(c.sale_num*15+c.stock_num*2) as stock_num,pattern_value,a.add_time
from who_goods_desc b,
who_privilegestocknum c,
who_goods a LEFT JOIN
who_tmp_real_stock2 d ON a.goods_id=d.goods_id_new
where a.goods_id=b.goods_id and
a.goods_id=c.goods_id and
a.goods_id>0 and
c.stock_num>0 and
is_on_sale=1 and
is_delete=0 and
is_freeshipping=0 and
a.goods_number>0
select a.*
from who_goods a
where a.goods_id=$id
SELECT a.goods_id AS goods_id, cat_id, a.goods_sn AS goods_sn, goods_name, keywords, is_on_sale, is_delete,
is_best,is_new, is_hot, is_promote, shop_price *100 AS shop_price, last_update, goods_number, goods_material,
goods_color, pattern_value, c.real_stock
FROM who_goods AS a,
who_goods_desc AS b LEFT JOIN who_tmp_real_stock c ON b.goods_id=c.goods_id_new
WHERE a.goods_id = b.goods_id
AND is_on_sale =1 AND
is_delete =0 AND
is_freeshipping =0 AND
a.goods_number >0
想获取库存量大于0的商品信息,不知道这个sql是不是
推荐阅读
-
SQL order by ID desc/asc加一个排序的字段解决查询慢问题
-
SQLSERVER 本地查询更新操作远程数据库的代码
-
Sql学习第三天——SQL 关于CTE(公用表达式)的递归查询使用
-
sql脚本查询数据库表,数据,结构,约束等操作的方法
-
关于SQL Server查询语句的使用
-
sqlserver关于分页存储过程的优化【让数据库按我们的意思执行查询计划】
-
SQL Server使用Merge语句当源表数据集为空时,无法进行查询的问题
-
MySQL入门(三) 数据库表的查询操作【重要】
-
mysql unique key在查询中的使用与相关问题
-
Orace查询数据出现乱码的问题解决思路