#SQL1242错误

---------------------

1. 子查询更新数据时遇到多条数据时&＃xff0c;可以使用SUM&MIN等函数解决&＃xff1a;如下&＃xff1a;
   
   

   //正确的方法一对多
   UPDATE &＃96;yd_draw_prize_order&＃96; SET &＃96;adopt_update_time&＃96; &＃61; (SELECT MIN(&＃96;update_time&＃96;) FROM &＃96;yd_draw_prize_order_number&＃96; WHERE &＃96;d_p_order_id&＃96; &＃61; &＃96;yd_draw_prize_order&＃96;.&＃96;d_p_order_id&＃96;) WHERE &＃96;adopt_update_time&＃96; IS NULL//报1242错误方式一对多
   UPDATE &＃96;yd_draw_prize_order&＃96; SET &＃96;adopt_update_time&＃96; &＃61; (SELECT &＃96;update_time&＃96; FROM &＃96;yd_draw_prize_order_number&＃96; WHERE &＃96;d_p_order_id&＃96; &＃61; &＃96;yd_draw_prize_order&＃96;.&＃96;d_p_order_id&＃96;) WHERE &＃96;adopt_update_time&＃96; IS NULL

   View Code

    

2. 时间查询     简单举例&＃xff1a; SELECT COUNT(&＃39;$column&＃39;) FROM &＃96;%table&＃96; WHERE &＃96;$column&＃96; BETWEEN UNIX_TIMESTAMP(&＃39;2018-05-03 00:00:01&＃39;) AND UNIX_TIMESTAMP(&＃39;2018-05-03 23:59:59&＃39;)
   更多时间查询案例&＃xff1b;
   案例分享&＃xff1a;

   //FROM_UNIXTIME 将时间戳转换为字符串日期;
//UNIX_TIMESTAMP 将其他时间转换为时间戳;
SELECT&＃96;yd_draw_prize_order&＃96;.&＃96;d_p_order_id&＃96;,yd_draw_prize_order_log.&＃96;rand_num&＃96;,FROM_UNIXTIME(yd_draw_prize_order.adopt_time) AS adopt_time,FROM_UNIXTIME(yd_draw_prize_order_log.draw_time) AS &＃39;抽奖时间&＃39;,yd_draw_prize_order_log.&＃96;d_p_order_activity_id&＃96;,yd_draw_prize_order_log.&＃96;d_p_order_activity_name&＃96;,yd_draw_prize_order_log.&＃96;draw_num&＃96;,yd_draw_prize_order_log.&＃96;winning&＃96;
FROM&＃96;yd_draw_prize_order&＃96;INNER JOIN &＃96;yd_draw_prize_order_number&＃96;ON yd_draw_prize_order.d_p_order_id &＃61; yd_draw_prize_order_number.d_p_order_idINNER JOIN &＃96;yd_draw_prize_order_log&＃96;ON yd_draw_prize_order.d_p_order_id &＃61; yd_draw_prize_order_log.d_p_order_id
WHERE &＃96;customer_phone&＃96; &＃61; &＃39;15512816085&＃39;AND &＃96;adopt&＃96; &＃61; 1AND yd_draw_prize_order_number.&＃96;reward_num&＃96; &＃61; 04028AND yd_draw_prize_order_log.draw_time > UNIX_TIMESTAMP(NOW())AND yd_draw_prize_order_log.&＃96;d_p_order_activity_id&＃96; &＃61; 2


   案例2

   //日期子查询;
SELECT*
FROM&＃96;yd_extension_data_1&＃96;
WHERE &＃96;distrbutor_id&＃96; IN(SELECT&＃96;distrbutor_id&＃96;FROM&＃96;yd_distrbutor&＃96;WHERE &＃96;distrbutor_provinceid&＃96; &＃61; 19)AND &＃96;create_time&＃96; BETWEEN &＃39;2017-01-01 00:00:01&＃39;AND &＃39;2018-01-01 00:00:00&＃39;
AND &＃96;distrbutor_id&＃96; NOT IN (1,23)
ORDER BY &＃96;create_time&＃96;


   　　

    

3. 综合查询&&综合更新
   综合查询案例1&＃xff1a;
   
   

   1.
   SELECTb.id AS &＃39;用户id&＃39;,user_name AS &＃39;用户名&＃39;,&＃96;active_points&＃96; AS &＃39;积分&＃39;,COUNT(bet_state) AS &＃39;猜中数&＃39;,GROUP_CONCAT(bet_state) AS &＃39;统计情况&＃39;,GROUP_CONCAT(match_id) AS &＃39;赛事ID&＃39;,b.share AS &＃39;1为己分享&＃39;,&＃96;use_points&＃96; AS &＃39;消耗积分&＃39;
   FROMyd_draw_worldcup_betting AS aINNER JOIN yd_draw_worldcup_user AS b
   WHERE a.&＃96;worldcup_user_id&＃96; &＃61; b.&＃96;id&＃96;AND a.&＃96;bet_state&＃96; IN (20, 21, 22)AND b.&＃96;use_points&＃96; <> 0
   GROUP BY id;2.SELECTb.id AS &＃39;用户id&＃39;,user_name AS &＃39;用户名&＃39;,&＃96;active_points&＃96; AS &＃39;积分&＃39;,b.share &＃43; COUNT(bet_state) AS &＃39;自定义&＃39;,COUNT(bet_state) AS &＃39;猜中数&＃39;,b.share AS &＃39;1为己分享&＃39;,&＃96;use_points&＃96; AS &＃39;消耗积分&＃39;FROMyd_draw_worldcup_betting AS aINNER JOIN yd_draw_worldcup_user AS b
   WHERE a.&＃96;worldcup_user_id&＃96; &＃61; b.&＃96;id&＃96;AND a.&＃96;bet_state&＃96; IN (20, 21, 22)AND b.&＃96;use_points&＃96; &＃61; 0
   GROUP BY id;

   View Code

    

   综合简单查询更新1&＃xff1a;
   
   

   1.UPDATE&＃96;yd_draw_worldcup_user&＃96;
   SET&＃96;active_points&＃96; &＃61; &＃96;active_points&＃96; &＃43; 1
   WHERE &＃96;id&＃96; IN(SELECT&＃96;worldcup_user_id&＃96;FROM&＃96;yd_draw_worldcup_betting&＃96;WHERE &＃96;match_id&＃96; &＃61; 2AND &＃96;bet_state&＃96; IN (20, 21, 22))

   View Code

    综合链表更新2&＃xff1a;

   

   1.
   update yd_draw_worldcup_user as a,yd_draw_worldcup_betting as b set
   a.active_points &＃61; 0,
   b.bet_state &＃61; 10,
   b.draw_number &＃61; &＃39;&＃39;where b.match_id in (1,2,3,4,5) and b.worldcup_user_id &＃61; a.id;

   View Code

    

4. 判断语句&＃xff1a;
   

   UPDATE yd_draw_worldcup_user AS a
SET a.active_points &＃61;
(CASE WHEN (a.active_points &＃43; a.&＃96;share&＃96;

5. 事务语句&＃xff1a;
   

   #mysql使用事务的关键字
#begin //打开一个事务
#commit //提交到数据库
#rollback //取消操作
#savepoint //保存&＃xff0c;部分取消&＃xff0c;部分提交
#alter table person type&＃61;INNODB //修改数据引擎begin;
update tags set tagid &＃61; 133 where docid &＃61; 1;
SAVEPOINT tags1;
update tags set tagid &＃61; 530 where docid &＃61;2;
SAVEPOINT tags2;
ROLLBACK TO SAVEPOINT tags2;
SELECT * from tags where docid in(1,2);
commit;


   　　

6. 自定义序列号字段:
   

   SET &＃64;rownum&＃61;0;
SELECT&＃64;rownum:&＃61;&＃64;rownum&＃43;1 AS &＃39;序列&＃39;,a. AS &＃39;用户名称&＃39;
FROM(SELECT &＃64;rownum:&＃61;0) r, AS a
WHERE 1
ORDER BY . ASC


   　　

7. mysql update You can&＃39;t specify target table &＃39;yd_qr_code&＃39; for update in FROM clause . 

原&＃xff1a;update &＃96;yd_qr_code&＃96; set winning_description &＃61;  (select a.&＃96;winning_description&＃96; from &＃96;yd_qr_code&＃96; as a where a.&＃96;qr_id&＃96; &＃61; 1) where qr_id &＃61; 2
原理&＃xff1a;mysql 不能在同表操作更新&＃xff0c;我们要用一个 中间表 来让数据库认为不是同表操作&＃xff1b;
后&＃xff1a;update &＃96;yd_qr_code&＃96; set winning_description &＃61; (select b.&＃96;winning_description&＃96; from (select a.&＃96;winning_description&＃96; from &＃96;yd_qr_code&＃96; as a where a.&＃96;qr_id&＃96; &＃61; 1)b) where qr_id &＃61; 2



 --------------------------------