sql 语句

最新推荐文章于 2023-02-20 08:19:30 发布

WriteHappy

最新推荐文章于 2023-02-20 08:19:30 发布

阅读量561

点赞数

分类专栏：数据库文章标签： sql image user join null login

本文链接：https://blog.csdn.net/WriteHappy/article/details/7000160

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

结算记录

SELECT

DATE_FORMAT(union_member_apply_bill.`apply_time`,'%Y-%m-%d')AS requestTime, -- 格式化时间

union_member_account_record.`before`,

union_member_apply_bill.`amount`,

CONCAT(union_member_apply_bill.`check_status`,union_member_apply_bill.`status`)AS`status`, --联合两个字段实际上是byte[] ，比如 01, -1-1

union_member_apply_bill.`bill_amount`,

union_member_apply_bill.`drawback`,

CASE WHENISNULL(union_member_apply_bill.`bill_time`) THEN '--' ELSEDATE_FORMAT(union_member_apply_bill.`bill_time`,'%Y-%m-%d') END AS payTime,--当这个字段的值是空的时候 '--'，不为空则按照这个时间格式输出

CASE WHENISNULL(union_member_taxes_back.taxesback_id) THEN '--' ELSEunion_member_taxes_back.`invoice_image` END AS `haveInvoice`,

CASE

WHEN union_member_apply_bill.`amount`>800 ANDISNULL(union_member_taxes_back.`taxesback_id`) --金额大于800 且字段不为空ANDISNULL(union_member_taxes_back.`invoice_image`) AND union_member_apply_bill.`check_status`!=-1 ANDTO_DAYS(NOW())-TO_DAYS(union_member_apply_bill.`apply_time`)<=90 THEN '申请'

WHEN union_member_apply_bill.`amount`>800 ANDunion_member_apply_bill.`taxes`>0 AND!ISNULL(union_member_taxes_back.`taxesback_id`) AND!ISNULL(union_member_taxes_back.`invoice_image`) ANDunion_member_apply_bill.`drawback`=0 AND ISNULL(union_member_taxes_back.`bill_time`)THEN '已申请'

WHEN union_member_apply_bill.`amount`>800 ANDunion_member_apply_bill.`taxes`>0 AND!ISNULL(union_member_taxes_back.`taxesback_id`) AND!ISNULL(union_member_taxes_back.`invoice_image`) ANDunion_member_apply_bill.`drawback`>0 AND!ISNULL(union_member_taxes_back.`bill_time`)THEN '已退税'

ELSE '--'

END AS `DrawbackCondition`,

union_member_apply_bill.`apply_id` AS applyId

FROM union_member_apply_bill

LEFT JOIN union_member_account_record ONunion_member_apply_bill.account_record=union_member_account_record.account_record_id

LEFT JOINunion_member_taxes_back ON union_member_apply_bill.apply_id = union_member_taxes_back.apply_id

WHEREunion_member_apply_bill.user_id=1

ORDER BYunion_member_apply_bill.`apply_time` DESC

Drawback

SELECT

union_member_taxes_back.`add_time`,union_member_apply_bill.`bill_notes`,union_member_apply_bill.`taxes`AS taxes ,union_member_taxes_back.`invoice_image` AS`invoiceInfo`,union_member_apply_bill.`drawback`,

CASE WHENISNULL(union_member_taxes_back.`bill_time`) THEN '--' ELSEDATE_FORMAT(union_member_taxes_back.`bill_time`,'%Y-%m-%d') END AS `billTime`,

CONCAT(union_member_taxes_back.`check_status`,union_member_taxes_back.`status`)AS `status`,

union_member_taxes_back.`taxesback_id`

FROMunion_member_apply_bill,union_member_taxes_back

WHEREunion_member_apply_bill.`user_id`=1 AND union_member_taxes_back.apply_id = union_member_apply_bill.apply_id

ANDunion_member_apply_bill.`taxes`>0

AND!ISNULL(union_member_taxes_back.`invoice_image`)

ORDER BYunion_member_taxes_back.`add_time` DESC

oneDrawbakcDetail

SELECTunion_member_apply_bill.`user_id`,union_member_taxes_back.`taxesback_id`,

uc_user_login.`username`,union_member_account_record.`before`,union_member_apply_bill.`amount`,

union_member_taxes_back.`add_time`,union_member_apply_bill.`check_user`,union_member_apply_bill.`check_time`,

uc_user_login.`username`ASaccountant,union_member_apply_bill.`bill_amount`,union_member_apply_bill.`taxes`,

union_member_apply_bill.`bill_time`,

CASE WHENISNULL(union_member_taxes_back.taxesback_id) THEN '无' ELSE '有' END AS`haveInvoice`,

CASE WHENISNULL(union_member_taxes_back.`taxesback_id`) THEN '无' ELSE '有' END AS`haveDrawback`,

union_member_taxes_back.`check_status`,

union_member_taxes_back.`status`,

CONCAT(union_member_taxes_back.`check_status`,union_member_taxes_back.`status`)AS `status`

FROMunion_member_apply_bill

LEFT JOINunion_member_account_record ON union_member_apply_bill.account_record=union_member_account_record.account_record_id

LEFT JOINunion_member_taxes_back ON union_member_apply_bill.apply_id = union_member_taxes_back.apply_id

LEFT JOINuc_user_login ON union_member_apply_bill.`user_id`=uc_user_login.`user_id`

WHEREunion_member_apply_bill.user_id=1 AND union_member_taxes_back.`taxesback_id`=3

相对效率比较低的一条sql 语句：

SELECT site_id, slot_id, COUNT(*) FROM union_ad_creative 
                WHERE `status`!=0 AND slot_id NOT IN (SELECT DISTINCT(slot_id) FROM union_ad_creative WHERE `status`=0 ) 
                GROUP BY slot_id ORDER BY COUNT(*) ASC LIMIT 1

原本这是写在unit test中的sql语句，目的是为了disable一条记录。做完unit test后要将数据还原回去。

所以最好筛选那些 slot_id现在不是0的数据。即

slot_id NOT IN (SELECT DISTINCT(slot_id) FROM union_ad_creative WHERE `status`=0 )

但是效果不好，因为not in引种影响效率。

优化之后的sql语句，效率明显提升了很多。

SELECT a.site_id, a.slot_id, COUNT(*) FROM union_ad_creative a
                    LEFT JOIN(SELECT DISTINCT(slot_id) FROM union_ad_creative WHERE `status`=0) b
                    ON a.slot_id=b.slot_id
                    WHERE a.status!=0 AND b.slot_id IS NULL
                    GROUP BY a.slot_id ORDER BY COUNT(*) ASC LIMIT 1

left join了， b.slot_id 就又可能为null了，而我们就是要找这样的数据