摘要:
在解决了POC的崩溃问题后, 需求方提出第二版的需求，本文进行记录
有问题的SQL:
SELECT
sale.usid AS usid,
cus.corpname AS corpname,
sale.iscenicid AS iscenicid,
salde.itickettypeid AS itickettypeid,
price.icrowdkindid AS icrowdkindid,
salde.mactualsaleprice AS mactualsaleprice,
sum(salde.iamount) AS numb,
sum(salde.meventmoney) AS mont,
coalesce(sum(salde.mderatemoney),
0) AS mderatemoney,
coalesce(sum(salde.ideratenums),
0) AS ideratenums,
coalesce(sum(salde.mhandcharge),
0) AS mhandcharge,
saletype.isettlementid AS isettlementid,
sale.bysalesvouchertype AS bysalesvouchertype
FROM
STSSALESVOUCHERTAB sale,
STSSALESVOUCHERDETAILSTAB salde,
(
SELECT FIND_TOP_USID(C.USID) AS SUSID,
C2.CORPNAME AS SCORPNAME,
C2.BNAME,
C.USID,
C.CORPNAME,
C.IBUSINESSID,
C.TTLB
FROM
CUSTOM C
LEFT JOIN CUSTOM C2 ON
C2.USID = C.SUSID
WHERE
C.LGTP = '02'
AND C.USTP = '01'
AND C.IBUSINESSID = 2) cus,
EDMTICKETTYPETAB ti,
EDMCROWDKINDPRICETAB price,
STSSALESSETTLEMENTTAB saletype
WHERE
substr(sale.dtmakedate,
1,
10) >= '2022-08-18'
AND substr(sale.dtmakedate,
1,
10) <= '2022-08-18'
AND sale.isalesvoucherid = saletype.isalesvoucherid
AND sale.iticketstationid = saletype.iticketstationid
AND price.icrowdkindpriceid = salde.icrowdkindpriceid
AND sale.isalesvoucherid = salde.isalesvoucherid
AND sale.iticketstationid = salde.iticketstationid
AND cus.usid = sale.usid
AND salde.itickettypeid = ti.itickettypeid
AND sale.iscenicid IN (1)
AND cus.ibusinessid <> 1
GROUP BY
sale.usid,
cus.corpname,
saletype.isettlementid,
salde.mactualsaleprice,
sale.iscenicid,
salde.itickettypeid,
price.icrowdkindid,
sale.bysalesvouchertype
UNION ALL SELECT
sale.usid AS usid,
cus.corpname AS corpname,
sale.iscenicid AS iscenicid,
salde.itickettypeid AS itickettypeid,
price.icrowdkindid AS icrowdkindid,
salde.mactualsaleprice AS mactualsaleprice,
sum(salde.iamount) AS numb,
sum(salde.meventmoney) AS mont,
coalesce(sum(salde.mderatemoney),
0) AS mderatemoney,
coalesce(sum(salde.ideratenums),
0) AS ideratenums,
coalesce(sum(salde.mhandcharge),
0) AS mhandcharge,
saletype.isettlementid AS isettlementid,
sale.bysalesvouchertype AS bysalesvouchertype
FROM
STSSALESVOUCHERTABLIST sale,
STSSALESVOUCHERDETAILSTABLIST salde,
(
SELECT USID,
CORPNAME,
IBUSINESSID,
TTLB,
FIND_TOP_USID(USID) AS SUSID
FROM
CUSTOM
WHERE
LGTP = '02'
AND USTP = '01') cus,
EDMTICKETTYPETAB ti,
EDMCROWDKINDPRICETAB price,
STSSALESSETTLEMENTTABLIST saletype
WHERE
substr(sale.dtmakedate,
1,
10) >= '2022-08-18'
AND substr(sale.dtmakedate,
1,
10) <= '2022-08-18'
AND sale.isalesvoucherid = saletype.isalesvoucherid
AND sale.iticketstationid = saletype.iticketstationid
AND price.icrowdkindpriceid = salde.icrowdkindpriceid
AND sale.isalesvoucherid = salde.isalesvoucherid
AND sale.iticketstationid = salde.iticketstationid
AND cus.usid = sale.usid
AND salde.itickettypeid = ti.itickettypeid
AND sale.iscenicid IN (1)
AND cus.ibusinessid <> 1
GROUP BY
sale.usid,
cus.susid,
saletype.isettlementid,
salde.mactualsaleprice,
sale.iscenicid,
salde.itickettypeid,
price.icrowdkindid,
sale.bysalesvouchertype;
功能需求描述:
查询结果与Innodb不对
innodb查询结果:
列存储引擎查询结果:
mysql> SELECT sale.usid AS usid,
-> cus.corpname AS corpname,
-> sale.iscenicid AS iscenicid,
-> salde.itickettypeid AS itickettypeid,
-> price.icrowdkindid AS icrowdkindid,
-> salde.mactualsaleprice AS mactualsaleprice,
-> sum(salde.iamount) AS numb,
-> sum(salde.meventmoney) AS mont,
-> coalesce(sum(salde.mderatemoney), 0) AS mderatemoney,
-> coalesce(sum(salde.ideratenums), 0) AS ideratenums,
-> coalesce(sum(salde.mhandcharge), 0) AS mhandcharge,
-> saletype.isettlementid AS isettlementid,
-> sale.bysalesvouchertype AS bysalesvouchertype
-> FROM STSSALESVOUCHERTAB sale,
-> STSSALESVOUCHERDETAILSTAB salde,
-> (SELECT FIND_TOP_USID(C.USID) AS SUSID,
-> C2.CORPNAME AS SCORPNAME,
-> C2.BNAME,
-> C.USID,
-> C.CORPNAME,
-> C.IBUSINESSID,
-> C.TTLB
-> FROM CUSTOM C
-> LEFT JOIN CUSTOM C2
-> ON C2.USID = C.SUSID
-> WHERE C.LGTP = '02'
-> AND C.USTP = '01'
-> AND C.IBUSINESSID = 2) cus,
-> EDMTICKETTYPETAB ti,
-> EDMCROWDKINDPRICETAB price,
-> STSSALESSETTLEMENTTAB saletype
-> WHERE substr(sale.dtmakedate, 1, 10) >= '2022-08-18'
-> AND substr(sale.dtmakedate, 1, 10) <= '2022-08-18'
-> AND sale.isalesvoucherid = saletype.isalesvoucherid
-> AND sale.iticketstationid = saletype.iticketstationid
-> AND price.icrowdkindpriceid = salde.icrowdkindpriceid
-> AND sale.isalesvoucherid = salde.isalesvoucherid
-> AND sale.iticketstationid = salde.iticketstationid
-> AND cus.usid = sale.usid
-> AND salde.itickettypeid = ti.itickettypeid
-> AND sale.iscenicid IN (1)
-> AND cus.ibusinessid <> 1
-> GROUP BY sale.usid,
-> cus.corpname,
-> saletype.isettlementid,
-> salde.mactualsaleprice,
-> sale.iscenicid,
-> salde.itickettypeid,
-> price.icrowdkindid,
-> sale.bysalesvouchertype
-> UNION ALL
-> SELECT sale.usid AS usid,
-> cus.corpname AS corpname,
-> sale.iscenicid AS iscenicid,
-> salde.itickettypeid AS itickettypeid,
-> price.icrowdkindid AS icrowdkindid,
-> salde.mactualsaleprice AS mactualsaleprice,
-> sum(salde.iamount) AS numb,
-> sum(salde.meventmoney) AS mont,
-> coalesce(sum(salde.mderatemoney), 0) AS mderatemoney,
-> coalesce(sum(salde.ideratenums), 0) AS ideratenums,
-> coalesce(sum(salde.mhandcharge), 0) AS mhandcharge,
-> saletype.isettlementid AS isettlementid,
-> sale.bysalesvouchertype AS bysalesvouchertype
-> FROM STSSALESVOUCHERTABLIST sale,
-> STSSALESVOUCHERDETAILSTABLIST salde,
-> (SELECT USID,
-> CORPNAME,
-> IBUSINESSID,
-> TTLB,
-> FIND_TOP_USID(USID) AS SUSID
-> FROM CUSTOM
-> WHERE LGTP = '02'
-> AND USTP = '01') cus,
-> EDMTICKETTYPETAB ti,
-> EDMCROWDKINDPRICETAB price,
-> STSSALESSETTLEMENTTABLIST saletype
-> WHERE substr(sale.dtmakedate, 1, 10) >= '2022-08-18'
-> AND substr(sale.dtmakedate, 1, 10) <= '2022-08-18'
-> AND sale.isalesvoucherid = saletype.isalesvoucherid
-> AND sale.iticketstationid = saletype.iticketstationid
-> AND price.icrowdkindpriceid = salde.icrowdkindpriceid
-> AND sale.isalesvoucherid = salde.isalesvoucherid
-> AND sale.iticketstationid = salde.iticketstationid
-> AND cus.usid = sale.usid
-> AND salde.itickettypeid = ti.itickettypeid
-> AND sale.iscenicid IN (1)
-> AND cus.ibusinessid <> 1
-> GROUP BY sale.usid,
-> cus.susid,
-> saletype.isettlementid,
-> salde.mactualsaleprice,
-> sale.iscenicid,
-> salde.itickettypeid,
-> price.icrowdkindid,
-> sale.bysalesvouchertype;
Empty set, 1 warning (48.15 sec)
