Last active
May 15, 2019 08:51
-
-
Save wenghengcong/5a694143018ab7e886f39411fe915732 to your computer and use it in GitHub Desktop.
SQL
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| // 假如已经存在的表 | |
| ALTER TABLE bd_channel_code MODIFY create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL; | |
| ALTER TABLE bd_channel_code MODIFY update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; | |
| //建表 | |
| // mysql 5.6 | |
| create table tweet ( | |
| id integer not null auto_increment primary key, | |
| create_time timestamp default now(), | |
| update_time timestamp default now() on update now(), | |
| message varchar(163) | |
| ) |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| https://www.yiibai.com/sql/sql-having.html | |
| Where和Having的异同 | |
| (1)where是查询返回结果之前进行过滤的 | |
| (2)having是查询返回结果之后，对结果进行过滤的 | |
| (3)在SQL中增加 HAVING 子句原因是，where关键字无法与聚合函数一起使用,having子句常跟group by一同使用，过滤分组后的数据 | |
| SELECT | |
| manager_id, | |
| first_name, | |
| last_name, | |
| COUNT(employee_id) direct_reports | |
| FROM | |
| employees | |
| WHERE | |
| manager_id IS NOT NULL | |
| GROUP BY manager_id | |
| HAVING direct_reports >= 5;原文出自【易百教程】，商业转载请联系作者获得授权，非商业请保留原文链接：https://www.yiibai.com/sql/sql-having.html | |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT | |
| bb.id AS id, | |
| bb.benefits_name AS benefitsName, | |
| bb.partner_id AS partnerId, | |
| bb.start_time AS startTime, | |
| bb.end_time AS endTime, | |
| bb.bd_manager AS bdManager, | |
| bu. NAME AS bdManagerName, | |
| bb.coupon_code_num AS couponCodeNum, | |
| ( SELECT @verifiedNum:=(CASE WHEN SUM(bbv.verify_count) IS NULL THEN 0 ELSE SUM(bbv.verify_count) END) FROM bd_benefits_verify bbv WHERE bbv.benefits_id = bb.id ) AS verifiedNum, | |
| IFNULL(bb.coupon_code_num,0) - IFNULL((@verifiedNum),0) AS remainingCodeNum, | |
| bb.detail_img AS detailImg, | |
| bb.entrance_img AS entranceImg, | |
| bb.benefits_process AS benefitsProcess, | |
| bb.benefits_instruction AS benefitsInstruction, | |
| (CASE WHEN NOW() > bb.end_time THEN 10 ELSE bb.state END) as state, | |
| bb.sign_bd_manager AS signBdManager, | |
| bb.benefits_detail AS benefitsDetail, | |
| bb.create_user AS createUser, | |
| bb.update_user AS updateUser, | |
| bb.create_time AS createTime, | |
| bb.update_time AS updateTime, | |
| bp.partner_name AS partnerName, | |
| bp.agent_name AS agentName, | |
| bbr.id AS ruleId, | |
| bbr.convert_num convertNum | |
| FROM | |
| bd_benefits bb | |
| LEFT JOIN bd_partner bp ON bb.partner_id = bp.id | |
| LEFT JOIN bd_user bu ON bp.bd_manager = bu.id | |
| LEFT JOIN bd_benefits_rule bbr ON bb.id = bbr.benefits_id | |
| WHERE | |
| bb.state IN (1,4,5) | |
| AND bb.bd_manager IN | |
| ( | |
| SELECT | |
| bp.bd_manager | |
| FROM | |
| bd_user_partner bup | |
| LEFT JOIN | |
| bd_partner bp | |
| ON bup.partner_id = bp.id | |
| WHERE | |
| bup.user_id = 14 | |
| ) | |
| -- AND ( ( IFNULL(bb.coupon_code_num,0) - IFNULL(@verifiedNum,0) )/IFNULL(bb.coupon_code_num,0) ) >= 0.98 | |
| GROUP BY | |
| bb.id | |
| HAVING ( ( IFNULL(bb.coupon_code_num,0) - IFNULL(@verifiedNum,0) )/IFNULL(bb.coupon_code_num,0) ) < 0.98 AND bb.`state` = 4 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment