比较复杂的sql语句

发布网友 发布时间：2022-04-30 21:12

我来回答

共4个回答

懂视网 时间：2022-05-01 01:33

= REPLACE(content,‘国家级‘,‘‘),content = REPLACE(content,‘世界级‘,‘‘),content = REPLACE(content,‘最高级‘,‘‘) ,content = REPLACE(content,‘最佳‘,‘‘),content = REPLACE(content,‘首个‘,‘‘),content = REPLACE(content,‘首选‘,‘‘) ,content = REPLACE(content,‘唯一‘,‘‘),content = REPLACE(content,‘世界级‘,‘‘),content = REPLACE(content,‘世界级‘,‘‘) ,content = REPLACE(content,‘最好‘,‘‘),content = REPLACE(content,‘最大‘,‘‘),content = REPLACE(content,‘精确‘,‘‘) ,content = REPLACE(content,‘顶级‘,‘‘),content = REPLACE(content,‘最高‘,‘‘),content = REPLACE(content,‘最低‘,‘‘) ,content = REPLACE(content,‘最‘,‘‘),content = REPLACE(content,‘最具‘,‘‘),content = REPLACE(content,‘最便宜‘,‘‘) ,content = REPLACE(content,‘最新‘,‘‘),content = REPLACE(content,‘最先进‘,‘‘),content = REPLACE(content,‘最大程度‘,‘‘) ,content = REPLACE(content,‘最新技术‘,‘‘),content = REPLACE(content,‘最先进科学‘,‘‘),content = REPLACE(content,‘国家级产品‘,‘‘) ,content = REPLACE(content,‘填补国内空白‘,‘‘),content = REPLACE(content,‘绝对‘,‘‘),content = REPLACE(content,‘独家‘,‘‘) ,content = REPLACE(content,‘首家‘,‘‘),content = REPLACE(content,‘最先进‘,‘‘),content = REPLACE(content,‘万能‘,‘‘) ,content = REPLACE(content,‘第一品牌‘,‘‘),content = REPLACE(content,‘金牌‘,‘‘),content = REPLACE(content,‘名牌‘,‘‘) ,content = REPLACE(content,‘优秀‘,‘‘),content = REPLACE(content,‘最先‘,‘‘),content = REPLACE(content,‘顶级‘,‘‘) ,content = REPLACE(content,‘独家‘,‘‘),content = REPLACE(content,‘全网销量第一‘,‘‘),content = REPLACE(content,‘全球首发‘,‘‘) ,content = REPLACE(content,‘全国首家‘,‘‘),content = REPLACE(content,‘全网首发‘,‘‘),content = REPLACE(content,‘世界领先‘,‘‘) ,content = REPLACE(content,‘顶级工艺‘,‘‘),content = REPLACE(content,‘最新科学‘,‘‘),content = REPLACE(content,‘最新技术‘,‘‘) ,content = REPLACE(content,‘最先进加工工艺‘,‘‘),content = REPLACE(content,‘最时尚‘,‘‘),content = REPLACE(content,‘极品‘,‘‘) ,content = REPLACE(content,‘顶级‘,‘‘),content = REPLACE(content,‘顶尖‘,‘‘),content = REPLACE(content,‘终极‘,‘‘) ,content = REPLACE(content,‘王牌‘,‘‘),content = REPLACE(content,‘销量冠军‘,‘‘),content = REPLACE(content,‘最受欢迎‘,‘‘) ,content = REPLACE(content,‘NO.1‘,‘‘),content = REPLACE(content,‘Top1‘,‘‘),content = REPLACE(content,‘极致‘,‘‘) ,content = REPLACE(content,‘永久‘,‘‘),content = REPLACE(content,‘王牌‘,‘‘),content = REPLACE(content,‘掌门人‘,‘‘) ,content = REPLACE(content,‘领袖品牌‘,‘‘),content = REPLACE(content,‘独一无二‘,‘‘),content = REPLACE(content,‘独家‘,‘‘) ,content = REPLACE(content,‘绝无仅有‘,‘‘),content = REPLACE(content,‘前无古人‘,‘‘),content = REPLACE(content,‘史无前例‘,‘‘) ,content = REPLACE(content,‘第一‘,‘‘) WHERE id = 2;

UPDATE shopnc_goods_common SET goods_body = 

INSERT(goods_body, INSTR(goods_body,‘<div class="p_titles_2">商品特色</div>‘), INSTR(goods_body,‘<div class="p_titles_2">商品参数</div>‘)- INSTR(goods_body,‘<div class="p_titles_2">商品特色</div>‘),‘‘)

WHERE gc_id_2 in (294,284,289,261,216) and goods_body LIKE ‘%商品特色%‘

SELECT shopnc_cps.order_sn as 订单号,shopnc_orders.buyer_name as 用户名,FROM_UNIXTIME(shopnc_orders.add_time,‘%Y-%m-%d %H:%i:%S‘) as 下单时间,
shopnc_order_goods.goods_name as 商品名称,shopnc_orders.store_name as 店铺名,shopnc_orders.CustomerNo as 用户编号,
CASE
WHEN shopnc_orders.order_state = ‘0‘ THEN ‘以取消‘
WHEN shopnc_orders.order_state = ‘10‘ THEN ‘未付款‘
WHEN shopnc_orders.order_state = ‘20‘ THEN ‘以付款‘
WHEN shopnc_orders.order_state = ‘30‘ THEN ‘以发货‘
WHEN shopnc_orders.order_state = ‘40‘ THEN ‘以收货‘
END AS 订单状态,

CASE
WHEN shopnc_goods.gc_id_1 = ‘1‘ THEN ‘汽车精品‘
WHEN shopnc_goods.gc_id_1 = ‘15‘ THEN ‘服装鞋帽‘
WHEN shopnc_goods.gc_id_1 = ‘53‘ THEN ‘箱包配饰‘
WHEN shopnc_goods.gc_id_1 = ‘77‘ THEN ‘数码家电‘
WHEN shopnc_goods.gc_id_1 = ‘124‘ THEN ‘个护彩妆‘
WHEN shopnc_goods.gc_id_1 = ‘159‘ THEN ‘家居日用‘
WHEN shopnc_goods.gc_id_1 = ‘215‘ THEN ‘珠宝收藏‘
WHEN shopnc_goods.gc_id_1 = ‘264‘ THEN ‘食品保健‘
WHEN shopnc_goods.gc_id_1 = ‘302‘ THEN ‘旅游度假‘
WHEN shopnc_goods.gc_id_1 = ‘313‘ THEN ‘母婴玩具‘
WHEN shopnc_goods.gc_id_1 = ‘319‘ THEN ‘燕京八绝‘
WHEN shopnc_goods.gc_id_1 = ‘354‘ THEN ‘手机充值‘
WHEN shopnc_goods.gc_id_1 = ‘355‘ THEN ‘其他分类‘
END as 商品分类,

CASE
WHEN shopnc_orders.payment_code = ‘offline‘ THEN ‘货到付款‘
WHEN shopnc_orders.payment_code = ‘online‘ THEN ‘在线支付‘
WHEN shopnc_orders.payment_code = ‘wxpay‘ THEN ‘微信支付‘
WHEN shopnc_orders.payment_code = ‘alipay‘ THEN ‘支付宝‘
WHEN shopnc_orders.payment_code = ‘unionpay‘ THEN ‘银联‘
WHEN shopnc_orders.payment_code = ‘pingan‘ THEN ‘平安银行‘
ELSE ‘不知道什么支付方式‘
END
as 支付方式,shopnc_order_goods.goods_id as 商品ID,shopnc_order_goods.goods_price as 商品价格,shopnc_order_goods.goods_pay_price as 订单支付价格,
shopnc_order_goods.goods_num as 商品数量,shopnc_order_goods.unit_code as 德珍商品编号,shopnc_order_goods.goods_usevipdiscount as 会员折扣,shopnc_order_goods.goods_usediscount as 代金券,
shopnc_order_goods.goods_useuserpoint as 使用购物金,shopnc_order_goods.goods_freight as 运费,shopnc_order_goods.living_goods_discount as 正在直播商品优惠,shopnc_order_goods.yihe_cuxiao as 移动端货到付款优惠,
shopnc_order_goods.gc_id as 商品分类 ,
CASE
WHEN shopnc_goods.gc_id_1 IN (15,53,124,159,264) AND shopnc_goods.goods_promotion_type =0 THEN ‘5%‘
WHEN shopnc_goods.gc_id_1 IN (15,53,124,159,264) AND shopnc_goods.goods_promotion_type != 0 THEN ‘3%‘
WHEN shopnc_goods.gc_id_1 IN (215,302) THEN ‘3%‘
WHEN shopnc_goods.gc_id_1 = ‘77‘ AND shopnc_goods.gc_id_2 != ‘110‘ THEN ‘3%‘
ELSE ‘0%‘
END as 佣金比例 ,

(CASE
WHEN shopnc_goods.gc_id_1 IN (15,53,124,159,264) AND shopnc_goods.goods_promotion_type =0 THEN 0.05
WHEN shopnc_goods.gc_id_1 IN (15,53,124,159,264) AND shopnc_goods.goods_promotion_type != 0 THEN 0.03
WHEN shopnc_goods.gc_id_1 IN (215,302) THEN 0.03
WHEN shopnc_goods.gc_id_1 = ‘77‘ AND shopnc_goods.gc_id_2 != ‘110‘ THEN 0.03
ELSE 0
END)*(shopnc_order_goods.goods_pay_price - 21) AS 佣金金额,‘16‘ AS 包装费 ,‘5‘ AS 邮费 ,
 shopnc_order_goods.rcb as 使用充值卡金额,shopnc_order_goods.pd as 使用预付款金额,

CASE
WHEN shopnc_cps.c_id = ‘17574‘ THEN ‘汽车精品‘
WHEN shopnc_cps.c_id = ‘18000‘ THEN ‘领克特‘
WHEN shopnc_cps.c_id = ‘19000‘ THEN ‘唯一传媒‘
END as cps来源,
 
CASE
WHEN shopnc_orders.client = ‘1‘ THEN ‘网站‘
WHEN shopnc_orders.client = ‘2‘ THEN ‘wap‘
WHEN shopnc_orders.client = ‘3‘ THEN ‘微信‘
END as 订单来源 ,
shopnc_order_push.order_push_sn as 德珍订单号

FROM shopnc_cps LEFT JOIN shopnc_orders ON shopnc_cps.order_sn = shopnc_orders.order_sn LEFT JOIN shopnc_order_goods ON shopnc_orders.order_id = shopnc_order_goods.order_id LEFT JOIN
 shopnc_goods ON shopnc_order_goods.goods_id =shopnc_goods.goods_id LEFT JOIN shopnc_order_push ON shopnc_orders.order_sn = shopnc_order_push.order_sn
 WHERE shopnc_orders.add_time >= ‘1435680000‘ and shopnc_orders.add_time <= ‘1438358400‘ 

最后一个有点复杂

一些稍微复杂点的sql语句

标签：

热心网友 时间：2022-04-30 22:41

如果你的是ACCESS数据库或者MS SQL数据库，可以使用视图来实现，做起来很容易的。你可以试一下。
以下介绍主要方法：
INNER JOIN 运算
组合两个表中的记录，只要在公共字段之中有相符的值。

语法
FROM table1 INNER JOIN table2 ON table1.field1 compopr table2.field2

INNER JOIN 运算可分为以下几个部分：

部分 说明
table1, table2 记录被组合的表的名称。
field1, field2 被联接的字段的名称。若它们不是由数字构成的，则这些字段必须为相同的数据类型并包含同类数据，但它们无须具有相同的名称。
compopr 任何的关系比较运算子："=," "<," ">," "<=," ">=," 或 "<>."

说明
可以在 FROM 子句中使用INNER JOIN运算。.这是最普通的联接类型。只要在这两个表的公共字段之中有相符值，内部联接将组合两个表中的记录。

可以使用 INNER JOIN 与部门表及员工表选择每一个部门中的全部员工。反之，可以使用 LEFT JOIN或 RIGHT JOIN运算创建 outer join，从而选择所有部门（即使有些并没有员工）或所有员工（即使有些尚未分配到部门）。

若试图联接包含 Memo或 OLE Object数据的字段，会导致错误。

可以联接任何两个相同类型的数值字段。例如，可以联接 AutoNumber和 Long字段，因为它们类型相似。但不能联接 Single 和 Double 类型的字段。

下列示例显示如何在类标识符字段联接类表及产品表：

SELECT CategoryName, ProctName

FROM Categories INNER JOIN Procts

ON Categories.CategoryID = Procts.CategoryID;

在上面的示例中，类标识符是已被联接的字段，但是它并不包含在查询输出中，因它并非被包含在 SELECT 语句之中。在这个示例中，若要包含联接字段，将字段名包含在 SELECT 语句中， Categories.CategoryID.

也可以使用下列语法，在一个 JOIN 语句中链接多个 ON 子句：

SELECT fields
FROM table1 INNER JOIN table2
ON table1.field1 compopr table2.field1 AND
ON table1.field2 compopr table2.field2) OR
ON table1.field3 compopr table2.field3)];

也可以使用下列语法，嵌套 JOIN 语句：

SELECT fields
FROM table1 INNER JOIN
(table2 INNER JOIN [( ]table3
[INNER JOIN [( ]tablex [INNER JOIN ...)]
ON table3.field3 compopr tablex.fieldx)]
ON table2.field2 compopr table3.field3)
ON table1.field1 compopr table2.field2;

在一个 INNER JOIN 之中，可以嵌套 LEFT JOIN 或 RIGHT JOIN，但是在 LEFT JOIN 或 RIGHT JOIN 中不能嵌套 INNER JOIN。

热心网友 时间：2022-04-30 23:59

可以用多条sql语句进行搜索导入在临时表里，在临时表再进行二次搜索。

热心网友 时间：2022-05-01 01:34

你要问什么？？