gpt4 book ai didi

php - sql查询优化执行时间以过滤表1中不存在于表2中的数据

转载 作者:行者123 更新时间:2023-11-29 22:40:39 24 4
gpt4 key购买 nike

表结构...

方案大师

book     receipt     coupon    created
100 700 1200 12-03-2015
100 700 1201 12-03-2015
100 700 1202 12-03-2015
100 701 1203 12-03-2015
100 701 1204 12-03-2015
100 701 1205 12-03-2015

图书发行表

  book_no    Alloted_city    issue_date   
100 nagpur 15-03-2015
100 nagpur 18-03-2015

还书表

 book     surveyor     used_receipt    city
1000 raj 700 nagpur

以下是我的预期输出..

 book      printeddate    issuedate   balancereceipt  allcoupon        city
100 12-03-2015 15-03-2015 701 1203,1204,1205 nagpur

下面是我的 sql 查询。

这工作正常..但是需要 4 到 5 分钟才能执行 50 到 80 行...请帮助我优化下面的查询...

SELECT DISTINCT BI.book_no AS book,BI.issue_date As Issuedate,BI.Alloted_city As city,SM.created AS PrintedDate,
SM.receipt_no as AllReceipt,GROUP_CONCAT(DISTINCT SM.coupon ORDER BY coupon ASC) As Allcoupon FROM book_issue BI LEFT JOIN scheme_master SM ON SM.book_no2=BI.book_no WHERE SM.receipt_no NOT IN (select used_receipt from book_return) AND BI.Alloted_city = :cityname AND BI.book_no BETWEEN :book_no AND :book2 GROUP BY SM.receipt_no order by SM.receipt_no ASC

最佳答案

这样使用,在not in条件下添加where子句,

SELECT DISTINCT BI.book_no AS book,BI.issue_date As Issuedate,BI.Alloted_city As city,SM.created AS PrintedDate,
SM.receipt_no as AllReceipt,GROUP_CONCAT(DISTINCT SM.coupon ORDER BY coupon ASC) As Allcoupon
FROM book_issue BI
LEFT JOIN scheme_master SM ON SM.book_no2=BI.book_no
WHERE SM.receipt_no NOT IN (select used_receipt from book_return where used_receipt = SM.receipt_no)
AND BI.Alloted_city = :cityname AND BI.book_no BETWEEN :book_no AND :book2
GROUP BY SM.receipt_no order by SM.receipt_no ASC

关于php - sql查询优化执行时间以过滤表1中不存在于表2中的数据,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/29368504/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com