摘要:
追踪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;
拆分-上半部:
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
ORDER BY
salde.mactualsaleprice;
拆分-下半部:
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测试-上半部:
列存储引擎的数据:
+-------------+--------------------------------------+-----------+---------------+--------------+------------------+------+----------+--------------+-------------+-------------+---------------+--------------------+
| usid | corpname | iscenicid | itickettypeid | icrowdkindid | mactualsaleprice | numb | mont | mderatemoney | ideratenums | mhandcharge | isettlementid | bysalesvouchertype |
+-------------+--------------------------------------+-----------+---------------+--------------+------------------+------+----------+--------------+-------------+-------------+---------------+--------------------+
| jydytg | | 1 | 78 | NULL | 0.01 | 288 | 2.88 | 0.00 | 0 | 0.00 | 08 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | NULL | 40.00 | 11 | 440.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | NULL | 50.00 | 4 | 200.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 59 | NULL | 55.00 | 12 | 660.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 222 | NULL | 55.00 | 238 | 13090.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| jyjtjd | 君亭酒店 | 1 | 217 | NULL | 60.00 | 2 | 120.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| jyyzwcl | 扬州望潮楼大酒店有限公司 | 1 | 217 | NULL | 60.00 | 20 | 1200.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| jyjldjd | 扬州金陵大饭店 | 1 | 217 | NULL | 60.00 | 2 | 120.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| jylvmama | 驴妈妈网 | 1 | 247 | NULL | 60.00 | 42 | 2520.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jydytg | | 1 | 220 | NULL | 60.00 | 120 | 7200.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyzxtc | 扬州中兴天成国际酒店 | 1 | 217 | NULL | 60.00 | 8 | 480.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| ylyzsxh | | 1 | 217 | NULL | 60.00 | 12 | 720.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| jyctrip | 携程网 | 1 | 247 | NULL | 60.00 | 53 | 3180.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 247 | NULL | 60.00 | 72 | 4320.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 247 | NULL | 60.00 | 115 | 6900.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| SSTDZH | 赛思科技 | 1 | 59 | NULL | 72.50 | 1 | 72.50 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | NULL | 75.00 | 92 | 6900.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 59 | NULL | 90.00 | 6 | 540.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| jylvmama | 驴妈妈网 | 1 | 17 | NULL | 92.00 | 2 | 184.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 17 | NULL | 92.00 | 30 | 2760.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| feizhu | 1 | 1 | 17 | NULL | 92.00 | 3 | 276.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 17 | NULL | 92.00 | 35 | 3220.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 17 | NULL | 92.00 | 17 | 1564.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 17 | NULL | 100.00 | 4 | 400.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | NULL | 100.00 | 4 | 400.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| jyctrip | 携程网 | 1 | 68 | NULL | 100.00 | 21 | 2100.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 68 | NULL | 100.00 | 18 | 1800.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 68 | NULL | 100.00 | 34 | 3400.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 17 | NULL | 100.00 | 39 | 3900.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jylvmama | 驴妈妈网 | 1 | 68 | NULL | 100.00 | 14 | 1400.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 17 | NULL | 100.00 | 64 | 6400.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| feizhu | 1 | 1 | 17 | NULL | 100.00 | 5 | 500.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jydytg | | 1 | 234 | NULL | 120.00 | 57 | 6840.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 169 | NULL | 130.00 | 9 | 1170.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 23 | NULL | 130.00 | 5 | 650.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 170 | NULL | 130.00 | 2 | 260.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 171 | NULL | 130.00 | 2 | 260.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 170 | NULL | 130.00 | 2 | 260.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 170 | NULL | 130.00 | 10 | 1300.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jylvmama | 驴妈妈网 | 1 | 48 | NULL | 135.00 | 2 | 270.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jylvmama | 驴妈妈网 | 1 | 48 | NULL | 140.00 | 4 | 560.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 173 | NULL | 160.00 | 2 | 320.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 174 | NULL | 160.00 | 3 | 480.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 176 | NULL | 160.00 | 3 | 480.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 173 | NULL | 160.00 | 2 | 320.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 176 | NULL | 160.00 | 9 | 1440.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jydytg | | 1 | 235 | NULL | 165.00 | 4 | 660.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 177 | NULL | 190.00 | 4 | 760.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 177 | NULL | 190.00 | 7 | 1330.00 | 0.00 | 0 | 0.00 | 08 | 01 |
+-------------+--------------------------------------+-----------+---------------+--------------+------------------+------+----------+--------------+-------------+-------------+---------------+--------------------+
49 rows in set (0.29 sec)
Innodb引擎数据:
+-------------+--------------------------------------+-----------+---------------+--------------+------------------+------+----------+--------------+-------------+-------------+---------------+--------------------+
| usid | corpname | iscenicid | itickettypeid | icrowdkindid | mactualsaleprice | numb | mont | mderatemoney | ideratenums | mhandcharge | isettlementid | bysalesvouchertype |
+-------------+--------------------------------------+-----------+---------------+--------------+------------------+------+----------+--------------+-------------+-------------+---------------+--------------------+
| jydytg | | 1 | 78 | 10 | 0.01 | 288 | 2.88 | 0.00 | 0 | 0.00 | 08 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | 2 | 40.00 | 11 | 440.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | 3 | 50.00 | 3 | 150.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | 2 | 50.00 | 1 | 50.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 222 | 1 | 55.00 | 238 | 13090.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 59 | 2 | 55.00 | 12 | 660.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| ylyzsxh | | 1 | 217 | 1 | 60.00 | 12 | 720.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| jyctrip | 携程网 | 1 | 247 | 14 | 60.00 | 53 | 3180.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyjldjd | 扬州金陵大饭店 | 1 | 217 | 1 | 60.00 | 2 | 120.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| dcmeituan | 美团网 | 1 | 247 | 14 | 60.00 | 115 | 6900.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyyzwcl | 扬州望潮楼大酒店有限公司 | 1 | 217 | 1 | 60.00 | 20 | 1200.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| jytongcheng | 同程网 | 1 | 247 | 14 | 60.00 | 72 | 4320.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyjtjd | 君亭酒店 | 1 | 217 | 1 | 60.00 | 2 | 120.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| jylvmama | 驴妈妈网 | 1 | 247 | 14 | 60.00 | 42 | 2520.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyzxtc | 扬州中兴天成国际酒店 | 1 | 217 | 1 | 60.00 | 8 | 480.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| jydytg | | 1 | 220 | 44 | 60.00 | 120 | 7200.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| SSTDZH | 赛思科技 | 1 | 59 | 3 | 72.50 | 1 | 72.50 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | 1 | 75.00 | 92 | 6900.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 59 | 1 | 90.00 | 6 | 540.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| dcmeituan | 美团网 | 1 | 17 | 8 | 92.00 | 35 | 3220.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| feizhu | 1 | 1 | 17 | 8 | 92.00 | 3 | 276.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jylvmama | 驴妈妈网 | 1 | 17 | 8 | 92.00 | 2 | 184.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 17 | 8 | 92.00 | 17 | 1564.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 17 | 8 | 92.00 | 30 | 2760.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | 1 | 100.00 | 4 | 400.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| jytongcheng | 同程网 | 1 | 17 | 7 | 100.00 | 4 | 400.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| feizhu | 1 | 1 | 17 | 7 | 100.00 | 5 | 500.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jylvmama | 驴妈妈网 | 1 | 68 | 7 | 100.00 | 14 | 1400.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 17 | 7 | 100.00 | 39 | 3900.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 68 | 7 | 100.00 | 34 | 3400.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 68 | 7 | 100.00 | 21 | 2100.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 17 | 7 | 100.00 | 64 | 6400.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 68 | 7 | 100.00 | 18 | 1800.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jydytg | | 1 | 234 | 7 | 120.00 | 57 | 6840.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 170 | 1 | 130.00 | 10 | 1300.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 170 | 1 | 130.00 | 2 | 260.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 170 | 1 | 130.00 | 2 | 260.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 169 | 1 | 130.00 | 9 | 1170.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 23 | 9 | 130.00 | 5 | 650.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 171 | 9 | 130.00 | 2 | 260.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jylvmama | 驴妈妈网 | 1 | 48 | 46 | 135.00 | 2 | 270.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jylvmama | 驴妈妈网 | 1 | 48 | 45 | 140.00 | 4 | 560.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 173 | 1 | 160.00 | 2 | 320.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 176 | 1 | 160.00 | 9 | 1440.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 173 | 1 | 160.00 | 2 | 320.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 176 | 1 | 160.00 | 3 | 480.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 174 | 1 | 160.00 | 3 | 480.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jydytg | | 1 | 235 | 7 | 165.00 | 4 | 660.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 177 | 1 | 190.00 | 7 | 1330.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 177 | 1 | 190.00 | 4 | 760.00 | 0.00 | 0 | 0.00 | 08 | 01 |
+-------------+--------------------------------------+-----------+---------------+--------------+------------------+------+----------+--------------+-------------+-------------+---------------+--------------------+
50 rows in set (13.97 sec)
结论:
- 列存储引擎缺少一行数据
- 列存储引擎的icrowdkindid列没有查询出数据
| SSTDZH | 赛思科技 | 1 | 59 | 2 | 55.00 | 12 | 660.00 | 0.00 | 0 | 0.00 | 88 | 01 |
icrowdkindid
------------
NULL
NULL
NULL
NULL
NULL
上半部-查询数据遗漏分析:
查询数据分析:
列存储引擎查出的数据:
| SSTDZH | 赛思科技 | 1 | 5 | NULL | 40.00 | 11 | 440.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | NULL | 50.00 | 4 | 200.00 | 0.00 | 0 | 0.00 | 88 | 01 |
innodb引擎查出的数据:
| SSTDZH | 赛思科技 | 1 | 5 | 2 | 40.00 | 11 | 440.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | 3 | 50.00 | 3 | 150.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | 2 | 50.00 | 1 | 50.00 | 0.00 | 0 | 0.00 | 88 | 01 |
问题分析:
- icrowdkindid 在列存储引擎中查出的值为NULL
- 聚合时 icrowdkindid 列被当作相等的
使用其他的列增加聚合列测试:
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,
salde.meventmoney
ORDER BY
salde.mactualsaleprice,
salde.itickettypeid;
列存储引擎:
+-------------+--------------------------------------+-----------+---------------+--------------+------------------+------+---------+--------------+-------------+-------------+---------------+--------------------+
| usid | corpname | iscenicid | itickettypeid | icrowdkindid | mactualsaleprice | numb | mont | mderatemoney | ideratenums | mhandcharge | isettlementid | bysalesvouchertype |
+-------------+--------------------------------------+-----------+---------------+--------------+------------------+------+---------+--------------+-------------+-------------+---------------+--------------------+
| jydytg | | 1 | 78 | NULL | 0.01 | 288 | 2.88 | 0.00 | 0 | 0.00 | 08 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | NULL | 40.00 | 11 | 440.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | NULL | 50.00 | 3 | 150.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | NULL | 50.00 | 1 | 50.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 59 | NULL | 55.00 | 12 | 660.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 222 | NULL | 55.00 | 11 | 605.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 222 | NULL | 55.00 | 72 | 3960.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 222 | NULL | 55.00 | 10 | 550.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 222 | NULL | 55.00 | 54 | 2970.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 222 | NULL | 55.00 | 42 | 2310.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 222 | NULL | 55.00 | 34 | 1870.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 222 | NULL | 55.00 | 15 | 825.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| jyjtjd | 君亭酒店 | 1 | 217 | NULL | 60.00 | 2 | 120.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| jyzxtc | 扬州中兴天成国际酒店 | 1 | 217 | NULL | 60.00 | 8 | 480.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| ylyzsxh | | 1 | 217 | NULL | 60.00 | 12 | 720.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| jyjldjd | 扬州金陵大饭店 | 1 | 217 | NULL | 60.00 | 2 | 120.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| jyyzwcl | 扬州望潮楼大酒店有限公司 | 1 | 217 | NULL | 60.00 | 20 | 1200.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| jydytg | | 1 | 220 | NULL | 60.00 | 118 | 7080.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jydytg | | 1 | 220 | NULL | 60.00 | 2 | 120.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 247 | NULL | 60.00 | 5 | 300.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 247 | NULL | 60.00 | 2 | 120.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 247 | NULL | 60.00 | 4 | 240.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 247 | NULL | 60.00 | 9 | 540.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jylvmama | 驴妈妈网 | 1 | 247 | NULL | 60.00 | 42 | 2520.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 247 | NULL | 60.00 | 6 | 360.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 247 | NULL | 60.00 | 31 | 1860.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 247 | NULL | 60.00 | 4 | 240.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 247 | NULL | 60.00 | 64 | 3840.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 247 | NULL | 60.00 | 115 | 6900.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| SSTDZH | 赛思科技 | 1 | 59 | NULL | 72.50 | 1 | 72.50 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | NULL | 75.00 | 54 | 4050.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | NULL | 75.00 | 20 | 1500.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | NULL | 75.00 | 18 | 1350.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 59 | NULL | 90.00 | 6 | 540.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| jyctrip | 携程网 | 1 | 17 | NULL | 92.00 | 8 | 736.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| feizhu | 1 | 1 | 17 | NULL | 92.00 | 1 | 92.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 17 | NULL | 92.00 | 4 | 368.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 17 | NULL | 92.00 | 5 | 460.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 17 | NULL | 92.00 | 6 | 552.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| feizhu | 1 | 1 | 17 | NULL | 92.00 | 2 | 184.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jylvmama | 驴妈妈网 | 1 | 17 | NULL | 92.00 | 2 | 184.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 17 | NULL | 92.00 | 6 | 552.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 17 | NULL | 92.00 | 8 | 736.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 17 | NULL | 92.00 | 18 | 1656.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 17 | NULL | 92.00 | 6 | 552.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 17 | NULL | 92.00 | 2 | 184.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 17 | NULL | 92.00 | 5 | 460.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 17 | NULL | 92.00 | 3 | 276.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 17 | NULL | 92.00 | 11 | 1012.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | NULL | 100.00 | 4 | 400.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| jyctrip | 携程网 | 1 | 17 | NULL | 100.00 | 3 | 300.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| feizhu | 1 | 1 | 17 | NULL | 100.00 | 2 | 200.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| feizhu | 1 | 1 | 17 | NULL | 100.00 | 3 | 300.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 17 | NULL | 100.00 | 12 | 1200.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 17 | NULL | 100.00 | 4 | 400.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 17 | NULL | 100.00 | 36 | 3600.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 17 | NULL | 100.00 | 9 | 900.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 17 | NULL | 100.00 | 6 | 600.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 17 | NULL | 100.00 | 12 | 1200.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 17 | NULL | 100.00 | 8 | 800.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 17 | NULL | 100.00 | 4 | 400.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 17 | NULL | 100.00 | 6 | 600.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 17 | NULL | 100.00 | 4 | 400.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 17 | NULL | 100.00 | 3 | 300.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 68 | NULL | 100.00 | 6 | 600.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 68 | NULL | 100.00 | 11 | 1100.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 68 | NULL | 100.00 | 10 | 1000.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 68 | NULL | 100.00 | 10 | 1000.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 68 | NULL | 100.00 | 6 | 600.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 68 | NULL | 100.00 | 8 | 800.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jylvmama | 驴妈妈网 | 1 | 68 | NULL | 100.00 | 3 | 300.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 68 | NULL | 100.00 | 16 | 1600.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 68 | NULL | 100.00 | 6 | 600.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jylvmama | 驴妈妈网 | 1 | 68 | NULL | 100.00 | 7 | 700.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jylvmama | 驴妈妈网 | 1 | 68 | NULL | 100.00 | 4 | 400.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jydytg | | 1 | 234 | NULL | 120.00 | 57 | 6840.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 23 | NULL | 130.00 | 3 | 390.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 23 | NULL | 130.00 | 2 | 260.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 169 | NULL | 130.00 | 1 | 130.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 169 | NULL | 130.00 | 6 | 780.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 169 | NULL | 130.00 | 2 | 260.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 170 | NULL | 130.00 | 2 | 260.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 170 | NULL | 130.00 | 2 | 260.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 170 | NULL | 130.00 | 4 | 520.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 170 | NULL | 130.00 | 4 | 520.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 170 | NULL | 130.00 | 2 | 260.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 171 | NULL | 130.00 | 2 | 260.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jylvmama | 驴妈妈网 | 1 | 48 | NULL | 135.00 | 2 | 270.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jylvmama | 驴妈妈网 | 1 | 48 | NULL | 140.00 | 4 | 560.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 173 | NULL | 160.00 | 2 | 320.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 173 | NULL | 160.00 | 2 | 320.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 174 | NULL | 160.00 | 3 | 480.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 176 | NULL | 160.00 | 3 | 480.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 176 | NULL | 160.00 | 3 | 480.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 176 | NULL | 160.00 | 2 | 320.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 176 | NULL | 160.00 | 4 | 640.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jydytg | | 1 | 235 | NULL | 165.00 | 4 | 660.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 177 | NULL | 190.00 | 2 | 380.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 177 | NULL | 190.00 | 2 | 380.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 177 | NULL | 190.00 | 5 | 950.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 177 | NULL | 190.00 | 2 | 380.00 | 0.00 | 0 | 0.00 | 08 | 01 |
+-------------+--------------------------------------+-----------+---------------+--------------+------------------+------+---------+--------------+-------------+-------------+---------------+--------------------+
101 rows in set (0.33 sec)
innodb引擎:
+-------------+--------------------------------------+-----------+---------------+--------------+------------------+------+---------+--------------+-------------+-------------+---------------+--------------------+
| usid | corpname | iscenicid | itickettypeid | icrowdkindid | mactualsaleprice | numb | mont | mderatemoney | ideratenums | mhandcharge | isettlementid | bysalesvouchertype |
+-------------+--------------------------------------+-----------+---------------+--------------+------------------+------+---------+--------------+-------------+-------------+---------------+--------------------+
| jydytg | | 1 | 78 | 10 | 0.01 | 288 | 2.88 | 0.00 | 0 | 0.00 | 08 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | 2 | 40.00 | 11 | 440.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | 3 | 50.00 | 3 | 150.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | 2 | 50.00 | 1 | 50.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 59 | 2 | 55.00 | 12 | 660.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 222 | 1 | 55.00 | 10 | 550.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 222 | 1 | 55.00 | 54 | 2970.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 222 | 1 | 55.00 | 34 | 1870.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 222 | 1 | 55.00 | 72 | 3960.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 222 | 1 | 55.00 | 11 | 605.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 222 | 1 | 55.00 | 42 | 2310.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 222 | 1 | 55.00 | 15 | 825.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| jyjldjd | 扬州金陵大饭店 | 1 | 217 | 1 | 60.00 | 2 | 120.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| jyzxtc | 扬州中兴天成国际酒店 | 1 | 217 | 1 | 60.00 | 8 | 480.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| jyjtjd | 君亭酒店 | 1 | 217 | 1 | 60.00 | 2 | 120.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| ylyzsxh | | 1 | 217 | 1 | 60.00 | 12 | 720.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| jyyzwcl | 扬州望潮楼大酒店有限公司 | 1 | 217 | 1 | 60.00 | 20 | 1200.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| jydytg | | 1 | 220 | 44 | 60.00 | 118 | 7080.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jydytg | | 1 | 220 | 44 | 60.00 | 2 | 120.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 247 | 14 | 60.00 | 64 | 3840.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 247 | 14 | 60.00 | 115 | 6900.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 247 | 14 | 60.00 | 5 | 300.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 247 | 14 | 60.00 | 9 | 540.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 247 | 14 | 60.00 | 31 | 1860.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 247 | 14 | 60.00 | 2 | 120.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 247 | 14 | 60.00 | 4 | 240.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 247 | 14 | 60.00 | 6 | 360.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 247 | 14 | 60.00 | 4 | 240.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jylvmama | 驴妈妈网 | 1 | 247 | 14 | 60.00 | 42 | 2520.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| SSTDZH | 赛思科技 | 1 | 59 | 3 | 72.50 | 1 | 72.50 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | 1 | 75.00 | 20 | 1500.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | 1 | 75.00 | 54 | 4050.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | 1 | 75.00 | 18 | 1350.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 59 | 1 | 90.00 | 6 | 540.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| jytongcheng | 同程网 | 1 | 17 | 8 | 92.00 | 11 | 1012.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 17 | 8 | 92.00 | 8 | 736.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| feizhu | 1 | 1 | 17 | 8 | 92.00 | 1 | 92.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 17 | 8 | 92.00 | 6 | 552.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 17 | 8 | 92.00 | 2 | 184.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 17 | 8 | 92.00 | 5 | 460.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 17 | 8 | 92.00 | 6 | 552.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 17 | 8 | 92.00 | 3 | 276.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 17 | 8 | 92.00 | 4 | 368.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 17 | 8 | 92.00 | 5 | 460.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| feizhu | 1 | 1 | 17 | 8 | 92.00 | 2 | 184.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 17 | 8 | 92.00 | 8 | 736.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 17 | 8 | 92.00 | 18 | 1656.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 17 | 8 | 92.00 | 6 | 552.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jylvmama | 驴妈妈网 | 1 | 17 | 8 | 92.00 | 2 | 184.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | 1 | 100.00 | 4 | 400.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| dcmeituan | 美团网 | 1 | 17 | 7 | 100.00 | 3 | 300.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 17 | 7 | 100.00 | 12 | 1200.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 17 | 7 | 100.00 | 4 | 400.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 17 | 7 | 100.00 | 12 | 1200.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 17 | 7 | 100.00 | 4 | 400.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 17 | 7 | 100.00 | 9 | 900.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| feizhu | 1 | 1 | 17 | 7 | 100.00 | 2 | 200.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 17 | 7 | 100.00 | 36 | 3600.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 17 | 7 | 100.00 | 8 | 800.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 17 | 7 | 100.00 | 6 | 600.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 17 | 7 | 100.00 | 4 | 400.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| feizhu | 1 | 1 | 17 | 7 | 100.00 | 3 | 300.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 17 | 7 | 100.00 | 6 | 600.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 17 | 7 | 100.00 | 3 | 300.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 68 | 7 | 100.00 | 6 | 600.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jylvmama | 驴妈妈网 | 1 | 68 | 7 | 100.00 | 4 | 400.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jylvmama | 驴妈妈网 | 1 | 68 | 7 | 100.00 | 7 | 700.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 68 | 7 | 100.00 | 6 | 600.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 68 | 7 | 100.00 | 8 | 800.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 68 | 7 | 100.00 | 16 | 1600.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jylvmama | 驴妈妈网 | 1 | 68 | 7 | 100.00 | 3 | 300.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 68 | 7 | 100.00 | 11 | 1100.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 68 | 7 | 100.00 | 6 | 600.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 68 | 7 | 100.00 | 10 | 1000.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 68 | 7 | 100.00 | 10 | 1000.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jydytg | | 1 | 234 | 7 | 120.00 | 57 | 6840.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 23 | 9 | 130.00 | 2 | 260.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 23 | 9 | 130.00 | 3 | 390.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 169 | 1 | 130.00 | 6 | 780.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 169 | 1 | 130.00 | 2 | 260.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 169 | 1 | 130.00 | 1 | 130.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 170 | 1 | 130.00 | 4 | 520.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 170 | 1 | 130.00 | 4 | 520.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 170 | 1 | 130.00 | 2 | 260.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 170 | 1 | 130.00 | 2 | 260.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 170 | 1 | 130.00 | 2 | 260.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 171 | 9 | 130.00 | 2 | 260.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jylvmama | 驴妈妈网 | 1 | 48 | 46 | 135.00 | 2 | 270.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jylvmama | 驴妈妈网 | 1 | 48 | 45 | 140.00 | 4 | 560.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 173 | 1 | 160.00 | 2 | 320.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 173 | 1 | 160.00 | 2 | 320.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 174 | 1 | 160.00 | 3 | 480.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 176 | 1 | 160.00 | 4 | 640.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 176 | 1 | 160.00 | 2 | 320.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 176 | 1 | 160.00 | 3 | 480.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 176 | 1 | 160.00 | 3 | 480.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jydytg | | 1 | 235 | 7 | 165.00 | 4 | 660.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 177 | 1 | 190.00 | 5 | 950.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 177 | 1 | 190.00 | 2 | 380.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 177 | 1 | 190.00 | 2 | 380.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 177 | 1 | 190.00 | 2 | 380.00 | 0.00 | 0 | 0.00 | 08 | 01 |
+-------------+--------------------------------------+-----------+---------------+--------------+------------------+------+---------+--------------+-------------+-------------+---------------+--------------------+
101 rows in set (14.51 sec)
列存储引擎icrowdkindid查询为NULL追踪
SQL:
select
salde.itickettypeid AS itickettypeid
from
STSSALESVOUCHERDETAILSTAB salde
GROUP BY
itickettypeid
ORDER BY
itickettypeid
limit 10;
查询结果:
mysql> select
-> salde.itickettypeid AS itickettypeid
-> from
-> STSSALESVOUCHERDETAILSTAB salde
-> GROUP BY
-> itickettypeid
-> ORDER BY
-> itickettypeid
-> limit 10;
+---------------+
| itickettypeid |
+---------------+
| 3 |
| 5 |
| 13 |
| 17 |
| 19 |
| 21 |
| 23 |
| 25 |
| 26 |
| 48 |
+---------------+
10 rows in set (0.00 sec)
调用自定义动态函数测试
SQL:
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;
调用结果:
mysql> 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;
+----------------+-----------+-------+----------------+--------------------------------------------------------------------------------+-------------+------+
| SUSID | SCORPNAME | BNAME | USID | CORPNAME | IBUSINESSID | TTLB |
+----------------+-----------+-------+----------------+--------------------------------------------------------------------------------+-------------+------+
| ARTIZAN | NULL | NULL | ARTIZAN | 上海佳华国际旅行社有限公司 | 2 | 01 |
| chuntian123456 | NULL | NULL | chuntian123456 | 江苏春天国际旅行社有限公司 | 2 | 01 |
| CITS202020 | NULL | NULL | CITS202020 | 中国国旅（浙江）国际旅行社有限公司 | 2 | 01 |
| CODEUSER | NULL | NULL | CODEUSER | 扫码购 | 2 | 01 |
| cytcyt | NULL | NULL | cytcyt | 中景旅游网 | 2 | 99 |
| cyttest | NULL | NULL | cyttest | 测试 | 2 | 99 |
| cytToTdosfxs | NULL | NULL | cytToTdosfxs | 中景旅游 | 2 | 99 |
| dcmeituan | NULL | NULL | dcmeituan | 美团网 | 2 | 99 |
| ecgrup | NULL | NULL | ecgrup | 深圳市鼎游开发测试 | 2 | 01 |
| feizhu | NULL | NULL | feizhu | 1 | 2 | 99 |
| jxht | NULL | NULL | jxht | 嘉兴海棠旅游有限公司 | 2 | 01 |
| jyclhx | NULL | NULL | jyclhx | 长乐鸿禧酒店 | 2 | 99 |
| jyclkz | NULL | NULL | jyclkz | 长乐客栈 | 2 | 99 |
| jycqgj | NULL | NULL | jycqgj | 长青国际酒店 | 2 | 99 |
| jyctrip | NULL | NULL | jyctrip | 携程网 | 2 | 99 |
| jydyh | NULL | NULL | jydyh | 古运河 | 2 | 99 |
| jydytg | NULL | NULL | jydytg | | 2 | 99 |
| jyessq | NULL | NULL | jyessq | 二十四桥酒店 | 2 | 99 |
| jyfhly | NULL | NULL | jyfhly | | 2 | 99 |
| jyfskh | NULL | NULL | jyfskh | 佛山康辉旅行社 | 2 | 99 |
| jygey | NULL | NULL | jygey | 个园 | 2 | 99 |
| jyhey | NULL | NULL | jyhey | 何园 | 2 | 99 |
| jyhgjr | NULL | NULL | jyhgjr | 扬州皇冠假日酒店 | 2 | 99 |
| jyhmdks | NULL | NULL | jyhmdks | 扬州华美达凯莎广场酒店 | 2 | 99 |
| jyhqf | NULL | NULL | jyhqf | 虹桥坊 | 2 | 99 |
| jyhqfjd | NULL | NULL | jyhqfjd | 虹桥坊酒店 | 2 | 99 |
| jyhygj | NULL | NULL | jyhygj | 扬州花园国际大酒店有限公司 | 2 | 99 |
| jyhzl | NULL | NULL | jyhzl | | 2 | 99 |
| jyhzyd | NULL | NULL | jyhzyd | 杭州远大 | 2 | 99 |
| jyjldjd | NULL | NULL | jyjldjd | 扬州金陵大饭店 | 2 | 99 |
| jyjsmljd | NULL | NULL | jyjsmljd | 江苏福满楼酒店管理有限公司（扬州文昌阁智选假日酒店） | 2 | 99 |
| jyjtjd | NULL | NULL | jyjtjd | 君亭酒店 | 2 | 99 |
| jyjyxzgy | NULL | NULL | jyjyxzgy | 扬州中集菁英行政公寓 | 2 | 99 |
| jyldfpjd | NULL | NULL | jyldfpjd | | 2 | 99 |
| jyldfu | NULL | NULL | jyldfu | 扬州绿地福朋喜来登酒店 | 2 | 99 |
| jylvmama | NULL | NULL | jylvmama | 驴妈妈网 | 2 | 99 |
| jylxs | NULL | NULL | jylxs | | 2 | 99 |
| jyscdj | NULL | NULL | jyscdj | 瘦西湖宋城度假酒店 | 2 | 99 |
| jysedyl | NULL | NULL | jysedyl | 扬州三盛希尔顿逸林酒店 | 2 | 99 |
| jyshzh | NULL | NULL | jyshzh | 上海甄荟网络科技有限公司（自我游） | 2 | 99 |
| jysxhkj | NULL | NULL | jysxhkj | 瘦西湖科技 | 2 | 99 |
| jysxhwq | NULL | NULL | jysxhwq | 瘦西湖温泉 | 2 | 99 |
| jysxhwqdj | NULL | NULL | jysxhwqdj | 隐居瘦西湖温泉度假酒店 | 2 | 99 |
| jysxhxcx | NULL | NULL | jysxhxcx | 瘦西湖小程序 | 2 | 99 |
| jysxhzfb | NULL | NULL | jysxhzfb | 瘦西湖支付宝小程序 | 2 | 99 |
| jytcwl | NULL | NULL | jytcwl | 同程文旅 | 2 | 99 |
| jytest | NULL | NULL | jytest | | 2 | 99 |
| jytongcheng | NULL | NULL | jytongcheng | 同程网 | 2 | 99 |
| jywl | NULL | NULL | jywl | 123微旅 | 2 | 99 |
| jywqdjc | NULL | NULL | jywqdjc | 温泉度假村 | 2 | 99 |
| jywts1688 | NULL | NULL | jywts1688 | 江阴市职工旅行社有限公司 | 2 | 01 |
| jyxedhp | NULL | NULL | jyxedhp | 扬州东高铁站希尔顿欢朋酒店 | 2 | 99 |
| jyxgll | NULL | NULL | jyxgll | 扬州香格里拉 | 2 | 99 |
| jyxpjd | NULL | NULL | jyxpjd | 扬州希朋酒店管理有限公司 | 2 | 99 |
| jyxxqg | NULL | NULL | jyxxqg | 学习强国分销 | 2 | 99 |
| jyybg | NULL | NULL | jyybg | 迎宾馆 | 2 | 99 |
| jyybgh | NULL | NULL | jyybgh | 迎宾馆 | 2 | 99 |
| jyyjjd | NULL | NULL | jyyjjd | 隐居酒店 | 2 | 99 |
| jyypjj | NULL | NULL | jyypjj | 扬鹏锦江酒店 | 2 | 99 |
| jyyxb | NULL | NULL | jyyxb | | 2 | 99 |
| jyyxx | NULL | NULL | jyyxx | | 2 | 99 |
| jyyyzz | NULL | NULL | jyyyzz | 友谊智助 | 2 | 99 |
| jyyzjxed | NULL | NULL | jyyzjxed | 扬州扬子江希尔顿欢朋酒店 | 2 | 99 |
| jyyzwcl | NULL | NULL | jyyzwcl | 扬州望潮楼大酒店有限公司 | 2 | 99 |
| jyyzyxyhyjd | NULL | NULL | jyyzyxyhyjd | 扬州国医书院合怡酒店 | 2 | 99 |
| jyzf | NULL | NULL | jyzf | 郑峰 | 2 | 99 |
| jyzxtc | NULL | NULL | jyzxtc | 扬州中兴天成国际酒店 | 2 | 99 |
| jyzyw | NULL | NULL | jyzyw | 茱萸湾 | 2 | 99 |
| nodout | NULL | NULL | nodout | 慧敏旅行社服务（上海）有限公司 | 2 | 01 |
| rzpagl123 | NULL | NULL | rzpagl123 | 日照平安国际旅行社有限公司 | 2 | 01 |
| SSTDZH | NULL | NULL | SSTDZH | 赛思科技 | 2 | 01 |
| SYGL | NULL | NULL | SYGL | 江苏世友国际旅行社有限公司 | 2 | 01 |
| szcyts0512 | NULL | NULL | szcyts0512 | 苏州青年旅行社股份有限公司 | 2 | 01 |
| taobao | NULL | NULL | taobao | 淘宝 | 2 | 99 |
| wh1715 | NULL | NULL | wh1715 | 五湖国际旅行社（苏州）有限公司 | 2 | 01 |
| whoami1 | NULL | NULL | whoami1 | whoami | 2 | 01 |
| yinhongji2 | NULL | NULL | yinhongji2 | test | 2 | 01 |
| ylyzsxh | NULL | NULL | ylyzsxh | | 2 | 99 |
| yztrdjd | NULL | NULL | yztrdjd | 扬州泰润大酒店 | 2 | 99 |
| yzwdyhjd | NULL | NULL | yzwdyhjd | 扬州万达颐华酒店 | 2 | 99 |
| yzzjglytdjd | NULL | NULL | yzzjglytdjd | 扬州中集格兰云天大酒店 | 2 | 99 |
| ZLGJ | NULL | NULL | ZLGJ | 中旅国际 | 2 | 01 |
+----------------+-----------+-------+----------------+--------------------------------------------------------------------------------+-------------+------+
82 rows in set, 1 warning (0.03 sec)
使用视图取代自定义函数调用
SQL
CREATE
VIEW VCUS AS 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;
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,
VCUS as 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
ORDER BY
salde.mactualsaleprice,
salde.itickettypeid;
查询结果
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,
-> VCUS as 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
-> ORDER BY
-> salde.mactualsaleprice,
-> salde.itickettypeid;
+-------------+--------------------------------------+-----------+---------------+--------------+------------------+------+----------+--------------+-------------+-------------+---------------+--------------------+
| usid | corpname | iscenicid | itickettypeid | icrowdkindid | mactualsaleprice | numb | mont | mderatemoney | ideratenums | mhandcharge | isettlementid | bysalesvouchertype |
+-------------+--------------------------------------+-----------+---------------+--------------+------------------+------+----------+--------------+-------------+-------------+---------------+--------------------+
| jydytg | | 1 | 78 | NULL | 0.01 | 288 | 2.88 | 0.00 | 0 | 0.00 | 08 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | NULL | 40.00 | 11 | 440.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | NULL | 50.00 | 4 | 200.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 59 | NULL | 55.00 | 12 | 660.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 222 | NULL | 55.00 | 238 | 13090.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| jyjtjd | 君亭酒店 | 1 | 217 | NULL | 60.00 | 2 | 120.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| ylyzsxh | | 1 | 217 | NULL | 60.00 | 12 | 720.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| jyyzwcl | 扬州望潮楼大酒店有限公司 | 1 | 217 | NULL | 60.00 | 20 | 1200.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| jyjldjd | 扬州金陵大饭店 | 1 | 217 | NULL | 60.00 | 2 | 120.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| jyzxtc | 扬州中兴天成国际酒店 | 1 | 217 | NULL | 60.00 | 8 | 480.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| jydytg | | 1 | 220 | NULL | 60.00 | 120 | 7200.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 247 | NULL | 60.00 | 115 | 6900.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 247 | NULL | 60.00 | 72 | 4320.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 247 | NULL | 60.00 | 53 | 3180.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jylvmama | 驴妈妈网 | 1 | 247 | NULL | 60.00 | 42 | 2520.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| SSTDZH | 赛思科技 | 1 | 59 | NULL | 72.50 | 1 | 72.50 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | NULL | 75.00 | 92 | 6900.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 59 | NULL | 90.00 | 6 | 540.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| jytongcheng | 同程网 | 1 | 17 | NULL | 92.00 | 17 | 1564.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jylvmama | 驴妈妈网 | 1 | 17 | NULL | 92.00 | 2 | 184.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 17 | NULL | 92.00 | 30 | 2760.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 17 | NULL | 92.00 | 35 | 3220.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| feizhu | 1 | 1 | 17 | NULL | 92.00 | 3 | 276.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | NULL | 100.00 | 4 | 400.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| dcmeituan | 美团网 | 1 | 17 | NULL | 100.00 | 64 | 6400.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| feizhu | 1 | 1 | 17 | NULL | 100.00 | 5 | 500.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 17 | NULL | 100.00 | 39 | 3900.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 17 | NULL | 100.00 | 4 | 400.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 68 | NULL | 100.00 | 34 | 3400.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 68 | NULL | 100.00 | 21 | 2100.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 68 | NULL | 100.00 | 18 | 1800.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jylvmama | 驴妈妈网 | 1 | 68 | NULL | 100.00 | 14 | 1400.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jydytg | | 1 | 234 | NULL | 120.00 | 57 | 6840.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 23 | NULL | 130.00 | 5 | 650.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 169 | NULL | 130.00 | 9 | 1170.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 170 | NULL | 130.00 | 2 | 260.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 170 | NULL | 130.00 | 2 | 260.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 170 | NULL | 130.00 | 10 | 1300.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 171 | NULL | 130.00 | 2 | 260.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jylvmama | 驴妈妈网 | 1 | 48 | NULL | 135.00 | 2 | 270.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jylvmama | 驴妈妈网 | 1 | 48 | NULL | 140.00 | 4 | 560.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 173 | NULL | 160.00 | 2 | 320.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 173 | NULL | 160.00 | 2 | 320.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 174 | NULL | 160.00 | 3 | 480.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 176 | NULL | 160.00 | 3 | 480.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 176 | NULL | 160.00 | 9 | 1440.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jydytg | | 1 | 235 | NULL | 165.00 | 4 | 660.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 177 | NULL | 190.00 | 4 | 760.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 177 | NULL | 190.00 | 7 | 1330.00 | 0.00 | 0 | 0.00 | 08 | 01 |
+-------------+--------------------------------------+-----------+---------------+--------------+------------------+------+----------+--------------+-------------+-------------+---------------+--------------------+
49 rows in set (0.44 sec)
表结构：
price
EDMCROWDKINDPRICETAB price
mysql> show create table EDMCROWDKINDPRICETAB\G
*************************** 1. row ***************************
Table: EDMCROWDKINDPRICETAB
Create Table: CREATE TABLE `EDMCROWDKINDPRICETAB` (
`ICROWDKINDPRICEID` int(11) NOT NULL,
`ITICKETTYPEID` int(11) NOT NULL,
`PRICEDATETYPE` int(11) DEFAULT NULL,
`ICROWDKINDID` int(11) NOT NULL,
`IPRICECATEGORYID` int(11) DEFAULT NULL,
`STARTDATA` varchar(10) NOT NULL,
`ENDDATA` varchar(10) NOT NULL,
`IPEOPLENUMRANGE` int(11) NOT NULL,
`LISTINGPRICE` decimal(10,2) NOT NULL,
`WEEKENDPRICE` decimal(10,2) DEFAULT NULL,
`MACTUALSALEPRICE` decimal(10,2) NOT NULL,
`ISEQUENCE` int(11) NOT NULL,
`BYISUSE` int(11) NOT NULL,
`SZMEMO` text,
`IBUSINESSID` int(11) NOT NULL,
`ISNET` int(11) NOT NULL,
`ISSCENE` int(11) NOT NULL,
`ICROWDKINDPRICECODE` varchar(10) DEFAULT NULL,
`JSPRICE` decimal(10,2) NOT NULL,
`DTMAKEDATE` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`NOTE1` varchar(20) DEFAULT NULL,
`NOTE2` varchar(200) DEFAULT NULL,
`NOTE3` varchar(20) DEFAULT NULL,
`NOTE4` varchar(20) DEFAULT NULL,
`INOTE1` int(11) DEFAULT NULL,
`INOTE2` int(11) DEFAULT NULL,
`INOTE3` int(11) DEFAULT NULL,
`INOTE4` int(11) DEFAULT NULL,
`INOTE5` int(11) DEFAULT NULL,
`INOTE6` int(11) DEFAULT NULL,
`INOTE7` int(11) DEFAULT NULL,
`INOTE8` int(11) DEFAULT NULL,
`CHANNELCODE` varchar(20) DEFAULT NULL,
`CHANNELPRICEID` varchar(20) DEFAULT NULL,
`TODAYPRICE` decimal(10,2) DEFAULT NULL,
`INOTE9` int(11) DEFAULT NULL,
`INOTE10` int(11) DEFAULT NULL,
PRIMARY KEY (`ICROWDKINDPRICEID`) USING BTREE,
KEY `FK_PRICE_REFERENCE_BUSINESS` (`IBUSINESSID`) USING BTREE,
KEY `FK_PRICE_REFERENCE_CROWDKIND` (`ICROWDKINDID`) USING BTREE,
KEY `FK_PRICE_REFERENCE_ESBSCENI` (`ITICKETTYPEID`) USING BTREE,
KEY `INDEX_E2` (`DTMAKEDATE`) USING BTREE,
KEY `IDX_26_202000912` (`ISNET`,`NOTE1`,`BYISUSE`,`ICROWDKINDID`,`STARTDATA`,`ITICKETTYPEID`,`IBUSINESSID`),
CONSTRAINT `FK_PRICE_REFERENCE_BUSINESS` FOREIGN KEY (`IBUSINESSID`) REFERENCES `EDMBUSINESSTAB` (`IBUSINESSID`),
CONSTRAINT `FK_PRICE_REFERENCE_CROWDKIND` FOREIGN KEY (`ICROWDKINDID`) REFERENCES `EDPCROWDKINDTAB` (`ICROWDKINDID`),
CONSTRAINT `FK_PRICE_REFERENCE_ESBSCENI` FOREIGN KEY (`ITICKETTYPEID`) REFERENCES `EDMTICKETTYPETAB` (`ITICKETTYPEID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
salde
STSSALESVOUCHERDETAILSTAB salde
mysql> show create table STSSALESVOUCHERDETAILSTAB\G
*************************** 1. row ***************************
Table: STSSALESVOUCHERDETAILSTAB
Create Table: CREATE TABLE `STSSALESVOUCHERDETAILSTAB` (
`ISALESVOUCHERDETAILSID` int(11) NOT NULL,
`ISALESVOUCHERID` int(11) NOT NULL,
`ITICKETSTATIONID` int(11) NOT NULL,
`ICROWDKINDPRICEID` int(11) DEFAULT NULL,
`ITICKETTYPEID` int(11) DEFAULT NULL,
`ITICKETWINID` int(11) DEFAULT NULL,
`IPLAYERPERTICKET` int(11) NOT NULL COMMENT '???1???1????????',
`ITICKETNUM` int(11) NOT NULL,
`ITICKETPLAYER` int(11) NOT NULL,
`DTSTARTDATE` varchar(10) NOT NULL,
`DTENDDATE` varchar(10) NOT NULL,
`ISTARTID` int(11) NOT NULL,
`IENDID` int(11) NOT NULL,
`SZSTARTSERIAL` varchar(30) NOT NULL,
`SZENDSERIAL` varchar(30) NOT NULL,
`IOFFERSSCHEMEID` int(11) DEFAULT NULL,
`IAMOUNT` int(11) NOT NULL,
`IPRESENTNUMS` int(11) NOT NULL,
`IDERATENUMS` int(11) NOT NULL,
`IFACTNUM` int(11) NOT NULL,
`IUSEABLENESSNUM` int(11) NOT NULL,
`MACTUALSALEPRICE` decimal(10,2) NOT NULL,
`MEVENTMONEY` decimal(10,2) NOT NULL,
`MDERATEMONEY` decimal(10,2) NOT NULL,
`MPRESENTMONEY` decimal(10,2) NOT NULL,
`MNOMINALFEE` decimal(10,2) NOT NULL,
`MDEPOSIT` decimal(10,2) NOT NULL,
`MHANDCHARGE` decimal(10,2) NOT NULL,
`BYCONSUMETYPE` varchar(10) NOT NULL,
`ICONSUMENUM` decimal(10,2) NOT NULL,
`IVERSION` int(11) NOT NULL,
`MTOTALAMOUNT` decimal(10,2) DEFAULT NULL,
`ITOTALNUMBER` int(11) DEFAULT NULL,
`ITOTALMINUTES` int(11) DEFAULT NULL,
`BYISOUT` int(11) DEFAULT NULL,
`DTMAKEDATE` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`ISALESVOUCHERDETAILSID`,`ISALESVOUCHERID`,`ITICKETSTATIONID`) USING BTREE,
KEY `FK_STSSALES_REFERENCE_EDMTICKE` (`ITICKETTYPEID`) USING BTREE,
KEY `FK_STSSALES_REFERENCE_STSSALS` (`ISALESVOUCHERID`,`ITICKETSTATIONID`) USING BTREE,
KEY `INDEX_S6` (`DTMAKEDATE`) USING BTREE,
KEY `IDX_3_20210315` (`ISALESVOUCHERID`,`ITICKETSTATIONID`,`ITICKETTYPEID`,`ITICKETPLAYER`),
CONSTRAINT `FK_STSSALES_REFERENCE_EDMTICKE` FOREIGN KEY (`ITICKETTYPEID`) REFERENCES `EDMTICKETTYPETAB` (`ITICKETTYPEID`),
CONSTRAINT `FK_STSSALES_REFERENCE_STSSALS` FOREIGN KEY (`ISALESVOUCHERID`, `ITICKETSTATIONID`) REFERENCES `STSSALESVOUCHERTAB` (`ISALESVOUCHERID`, `ITICKETSTATIONID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
EDMTICKETTYPETAB
mysql> show create table EDMTICKETTYPETAB\G
*************************** 1. row ***************************
Table: EDMTICKETTYPETAB
Create Table: CREATE TABLE `EDMTICKETTYPETAB` (
`ITICKETTYPEID` int(11) NOT NULL COMMENT '??ID',
`SZTICKETTYPECODE` varchar(30) NOT NULL,
`SZTICKETTYPENAME` varchar(50) NOT NULL,
`ITICKETNORULEID` int(11) NOT NULL,
`ISCENICID` int(11) NOT NULL,
`BYCATEGORYTYPE` varchar(10) NOT NULL,
`BYUSAGE` int(11) NOT NULL COMMENT '0 -- ???, 1-- ???,2 -- ???,3 -- ???,4 -- ???,5 -- ???,6 -- ???+ ??',
`BYUSELIMIT` int(11) NOT NULL COMMENT '0 -- ???,1 -- ???',
`BYMAKETICKETWAY` varchar(10) NOT NULL COMMENT '00 -- ????,01 --????,02--????',
`BYMEDIATYPE` varchar(10) NOT NULL COMMENT '00 -- ??,01 -- ??? RI ?,02 -- ??? RWI ?,03-- ??? RWII ?',
`SZTICKETPRINTCODE` varchar(50) NOT NULL,
`MCOSTPRICE` decimal(10,2) NOT NULL,
`MNOMINALFEE` decimal(10,2) NOT NULL,
`BYISUSE` int(11) NOT NULL COMMENT '0 -- ????,1 -- ????',
`SZMEMO` text,
`BISPERSONTIMESTAT` int(11) NOT NULL,
`VALIDITYDAY` int(11) NOT NULL,
`ISEQUENCE` int(11) NOT NULL,
`ISCONTROL` int(11) NOT NULL,
`ISCONTROLSALE` int(11) NOT NULL,
`ISSALE` int(11) NOT NULL,
`ISCANSALE` int(11) NOT NULL,
`DTMAKEDATE` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`ICID` varchar(6) DEFAULT NULL,
`INT1` int(11) DEFAULT NULL,
`INT2` int(11) DEFAULT NULL,
`INT3` int(11) DEFAULT NULL,
`INT4` int(11) DEFAULT NULL,
`NOTE1` varchar(50) DEFAULT NULL,
`NOTE2` varchar(50) DEFAULT NULL,
`NOTE3` varchar(50) DEFAULT NULL,
`NOTE4` varchar(50) DEFAULT NULL,
`NOTE5` varchar(50) DEFAULT NULL,
`INT5` int(11) DEFAULT NULL,
`FACENUM` int(11) DEFAULT NULL,
`INT6` int(11) DEFAULT NULL,
`INT7` int(11) DEFAULT NULL,
PRIMARY KEY (`ITICKETTYPEID`) USING BTREE,
KEY `FK_EDMTICKE_REFERENCE_ESBSCENI` (`ISCENICID`) USING BTREE,
KEY `INDEX_EDMTICKETTYPETAB` (`DTMAKEDATE`) USING BTREE,
KEY `IDX_9_202000914` (`ITICKETTYPEID`),
KEY `IDX_10_202000914` (`ITICKETTYPEID`,`SZTICKETTYPENAME`),
CONSTRAINT `FK_EDMTICKE_REFERENCE_ESBSCENI` FOREIGN KEY (`ISCENICID`) REFERENCES `ESBSCENICAREATAB` (`ISCENICID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
EDPCROWDKINDTAB
mysql> show create table EDPCROWDKINDTAB\G
*************************** 1. row ***************************
Table: EDPCROWDKINDTAB
Create Table: CREATE TABLE `EDPCROWDKINDTAB` (
`ICROWDKINDID` int(11) NOT NULL,
`IROOTID` int(11) NOT NULL,
`IPARENTID` int(11) NOT NULL,
`ILEVEL` int(11) NOT NULL,
`ILEVELSEQUENCE` int(11) NOT NULL,
`BISLEAF` int(11) NOT NULL,
`SZINNERID` text NOT NULL,
`SZINNERCODE` text NOT NULL,
`SZINNERNAME` text NOT NULL,
`SZCROWDKINDCODE` varchar(30) NOT NULL,
`SZCROWDKINDNAME` varchar(50) NOT NULL,
`BYREGFINGERPRINTMODE` int(11) NOT NULL,
`BYREGFINGERPRINTTYPE` int(11) NOT NULL,
`BYSTORAGE` int(11) NOT NULL,
`BYISUSE` int(11) NOT NULL,
`SZMEMO` text,
`DTMAKEDATE` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`ISJH` int(11) DEFAULT NULL,
`ISZS` int(11) DEFAULT NULL,
PRIMARY KEY (`ICROWDKINDID`) USING BTREE,
KEY `INDEX_EDPCROWDKINDTAB` (`DTMAKEDATE`) USING BTREE,
KEY `IDX_25_202000912` (`ICROWDKINDID`,`SZCROWDKINDNAME`),
KEY `IDX_1_20210220` (`SZCROWDKINDNAME`,`ICROWDKINDID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
问题定位:
原因
- salde.icrowdkindpriceid 缺省为NULL
- 列存储引擎对于NULL的判断存在问题
修改:
添加: AND salde.icrowdkindpriceid IS NOT NULL
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 salde.icrowdkindpriceid IS NOT NULL
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
ORDER BY
salde.mactualsaleprice;
查询结果:
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 salde.icrowdkindpriceid IS NOT NULL
-> 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
-> ORDER BY
-> salde.mactualsaleprice;
+-------------+--------------------------------------+-----------+---------------+--------------+------------------+------+----------+--------------+-------------+-------------+---------------+--------------------+
| usid | corpname | iscenicid | itickettypeid | icrowdkindid | mactualsaleprice | numb | mont | mderatemoney | ideratenums | mhandcharge | isettlementid | bysalesvouchertype |
+-------------+--------------------------------------+-----------+---------------+--------------+------------------+------+----------+--------------+-------------+-------------+---------------+--------------------+
| jydytg | | 1 | 78 | 10 | 0.01 | 288 | 2.88 | 0.00 | 0 | 0.00 | 08 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | 2 | 40.00 | 11 | 440.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | 2 | 50.00 | 1 | 50.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | 3 | 50.00 | 3 | 150.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 59 | 2 | 55.00 | 12 | 660.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 222 | 1 | 55.00 | 238 | 13090.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| jyjtjd | 君亭酒店 | 1 | 217 | 1 | 60.00 | 2 | 120.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| jyyzwcl | 扬州望潮楼大酒店有限公司 | 1 | 217 | 1 | 60.00 | 20 | 1200.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| ylyzsxh | | 1 | 217 | 1 | 60.00 | 12 | 720.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| jylvmama | 驴妈妈网 | 1 | 247 | 14 | 60.00 | 42 | 2520.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jydytg | | 1 | 220 | 44 | 60.00 | 120 | 7200.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyzxtc | 扬州中兴天成国际酒店 | 1 | 217 | 1 | 60.00 | 8 | 480.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| jyjldjd | 扬州金陵大饭店 | 1 | 217 | 1 | 60.00 | 2 | 120.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| jyctrip | 携程网 | 1 | 247 | 14 | 60.00 | 53 | 3180.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 247 | 14 | 60.00 | 72 | 4320.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 247 | 14 | 60.00 | 115 | 6900.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| SSTDZH | 赛思科技 | 1 | 59 | 3 | 72.50 | 1 | 72.50 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | 1 | 75.00 | 92 | 6900.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 59 | 1 | 90.00 | 6 | 540.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| jylvmama | 驴妈妈网 | 1 | 17 | 8 | 92.00 | 2 | 184.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 17 | 8 | 92.00 | 30 | 2760.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| feizhu | 1 | 1 | 17 | 8 | 92.00 | 3 | 276.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 17 | 8 | 92.00 | 35 | 3220.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 17 | 8 | 92.00 | 17 | 1564.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 17 | 7 | 100.00 | 4 | 400.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | 1 | 100.00 | 4 | 400.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| jyctrip | 携程网 | 1 | 68 | 7 | 100.00 | 21 | 2100.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 17 | 7 | 100.00 | 39 | 3900.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| feizhu | 1 | 1 | 17 | 7 | 100.00 | 5 | 500.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 68 | 7 | 100.00 | 18 | 1800.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 68 | 7 | 100.00 | 34 | 3400.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jylvmama | 驴妈妈网 | 1 | 68 | 7 | 100.00 | 14 | 1400.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 17 | 7 | 100.00 | 64 | 6400.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jydytg | | 1 | 234 | 7 | 120.00 | 57 | 6840.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 170 | 1 | 130.00 | 10 | 1300.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 170 | 1 | 130.00 | 2 | 260.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 170 | 1 | 130.00 | 2 | 260.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 23 | 9 | 130.00 | 5 | 650.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 171 | 9 | 130.00 | 2 | 260.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 169 | 1 | 130.00 | 9 | 1170.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jylvmama | 驴妈妈网 | 1 | 48 | 46 | 135.00 | 2 | 270.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jylvmama | 驴妈妈网 | 1 | 48 | 45 | 140.00 | 4 | 560.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 176 | 1 | 160.00 | 3 | 480.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 174 | 1 | 160.00 | 3 | 480.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 173 | 1 | 160.00 | 2 | 320.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 173 | 1 | 160.00 | 2 | 320.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 176 | 1 | 160.00 | 9 | 1440.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jydytg | | 1 | 235 | 7 | 165.00 | 4 | 660.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 177 | 1 | 190.00 | 4 | 760.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 177 | 1 | 190.00 | 7 | 1330.00 | 0.00 | 0 | 0.00 | 08 | 01 |
+-------------+--------------------------------------+-----------+---------------+--------------+------------------+------+----------+--------------+-------------+-------------+---------------+--------------------+
50 rows in set (0.28 sec)
将Left Join替换为Join
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
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;
结果查询正常
mysql> SELECT
-> sale.usid AS usid,
-> cus.corpname AS corpname,
-> sale.iscenicid AS iscenicid,
-> salde.itickettypeid AS itickettypeid,
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),
-> 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
-> 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;
+-------------+--------------------------------------+-----------+---------------+--------------+------------------+------+----------+--------------+-------------+-------------+---------------+--------------------+
| usid | corpname | iscenicid | itickettypeid | icrowdkindid | mactualsaleprice | numb | mont | mderatemoney | ideratenums | mhandcharge | isettlementid | bysalesvouchertype |
+-------------+--------------------------------------+-----------+---------------+--------------+------------------+------+----------+--------------+-------------+-------------+---------------+--------------------+
| jyjldjd | 扬州金陵大饭店 | 1 | 217 | 1 | 60.00 | 2 | 120.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| dcmeituan | 美团网 | 1 | 247 | 14 | 60.00 | 115 | 6900.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jydytg | | 1 | 234 | 7 | 120.00 | 57 | 6840.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 247 | 14 | 60.00 | 72 | 4320.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 247 | 14 | 60.00 | 53 | 3180.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 68 | 7 | 100.00 | 21 | 2100.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jydytg | | 1 | 78 | 10 | 0.01 | 288 | 2.88 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyzxtc | 扬州中兴天成国际酒店 | 1 | 217 | 1 | 60.00 | 8 | 480.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| jydytg | | 1 | 235 | 7 | 165.00 | 4 | 660.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jydytg | | 1 | 220 | 44 | 60.00 | 120 | 7200.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jylvmama | 驴妈妈网 | 1 | 247 | 14 | 60.00 | 42 | 2520.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 68 | 7 | 100.00 | 18 | 1800.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 68 | 7 | 100.00 | 34 | 3400.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyyzwcl | 扬州望潮楼大酒店有限公司 | 1 | 217 | 1 | 60.00 | 20 | 1200.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| jylvmama | 驴妈妈网 | 1 | 68 | 7 | 100.00 | 14 | 1400.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 17 | 7 | 100.00 | 64 | 6400.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 17 | 8 | 92.00 | 17 | 1564.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 169 | 1 | 130.00 | 9 | 1170.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 171 | 9 | 130.00 | 2 | 260.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 17 | 7 | 100.00 | 39 | 3900.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 176 | 1 | 160.00 | 9 | 1440.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 17 | 7 | 100.00 | 4 | 400.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyjtjd | 君亭酒店 | 1 | 217 | 1 | 60.00 | 2 | 120.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| dcmeituan | 美团网 | 1 | 17 | 8 | 92.00 | 35 | 3220.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| dcmeituan | 美团网 | 1 | 170 | 1 | 130.00 | 10 | 1300.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 177 | 1 | 190.00 | 7 | 1330.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 170 | 1 | 130.00 | 2 | 260.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jylvmama | 驴妈妈网 | 1 | 48 | 45 | 140.00 | 4 | 560.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| feizhu | 1 | 1 | 17 | 8 | 92.00 | 3 | 276.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 173 | 1 | 160.00 | 2 | 320.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 17 | 8 | 92.00 | 30 | 2760.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 170 | 1 | 130.00 | 2 | 260.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 23 | 9 | 130.00 | 5 | 650.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| feizhu | 1 | 1 | 17 | 7 | 100.00 | 5 | 500.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jylvmama | 驴妈妈网 | 1 | 48 | 46 | 135.00 | 2 | 270.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jylvmama | 驴妈妈网 | 1 | 17 | 8 | 92.00 | 2 | 184.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 177 | 1 | 190.00 | 4 | 760.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 173 | 1 | 160.00 | 2 | 320.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jyctrip | 携程网 | 1 | 174 | 1 | 160.00 | 3 | 480.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| jytongcheng | 同程网 | 1 | 176 | 1 | 160.00 | 3 | 480.00 | 0.00 | 0 | 0.00 | 08 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | 2 | 50.00 | 1 | 50.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | 1 | 75.00 | 92 | 6900.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | 3 | 50.00 | 3 | 150.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 222 | 1 | 55.00 | 238 | 13090.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | 2 | 40.00 | 11 | 440.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| ylyzsxh | | 1 | 217 | 1 | 60.00 | 12 | 720.00 | 0.00 | 0 | 0.00 | 50 | 01 |
| SSTDZH | 赛思科技 | 1 | 59 | 1 | 90.00 | 6 | 540.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 59 | 3 | 72.50 | 1 | 72.50 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 59 | 2 | 55.00 | 12 | 660.00 | 0.00 | 0 | 0.00 | 88 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | 1 | 100.00 | 4 | 400.00 | 0.00 | 0 | 0.00 | 88 | 01 |
+-------------+--------------------------------------+-----------+---------------+--------------+------------------+------+----------+--------------+-------------+-------------+---------------+--------------------+
50 rows in set (0.27 sec)
去除查询列saletype.isettlementid AS isettlementid结果正确
SQL
SELECT
sale.usid AS usid,
cus.corpname AS corpname,
sale.iscenicid AS iscenicid,
salde.itickettypeid AS salde_itickettypeid,
price.icrowdkindid AS price_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,
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,
salde.mactualsaleprice,
sale.iscenicid,
salde.itickettypeid,
price.icrowdkindid,
sale.bysalesvouchertype;
查询结果:
mysql> SELECT
-> sale.usid AS usid,
-> cus.corpname AS corpname,
-> sale.iscenicid AS iscenicid,
-> salde.itickettypeid AS salde_itickettypeid,
-> price.icrowdkindid AS price_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,
-> 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,
-> salde.mactualsaleprice,
-> sale.iscenicid,
-> salde.itickettypeid,
-> price.icrowdkindid,
-> sale.bysalesvouchertype;
+-------------+--------------------------------------+-----------+---------------------+--------------------+------------------+------+----------+--------------+-------------+-------------+--------------------+
| usid | corpname | iscenicid | salde_itickettypeid | price_icrowdkindid | mactualsaleprice | numb | mont | mderatemoney | ideratenums | mhandcharge | bysalesvouchertype |
+-------------+--------------------------------------+-----------+---------------------+--------------------+------------------+------+----------+--------------+-------------+-------------+--------------------+
| jydytg | | 1 | 234 | 7 | 120.00 | 57 | 6840.00 | 0.00 | 0 | 0.00 | 01 |
| dcmeituan | 美团网 | 1 | 247 | 14 | 60.00 | 115 | 6900.00 | 0.00 | 0 | 0.00 | 01 |
| jytongcheng | 同程网 | 1 | 247 | 14 | 60.00 | 72 | 4320.00 | 0.00 | 0 | 0.00 | 01 |
| jyctrip | 携程网 | 1 | 247 | 14 | 60.00 | 53 | 3180.00 | 0.00 | 0 | 0.00 | 01 |
| jyctrip | 携程网 | 1 | 68 | 7 | 100.00 | 21 | 2100.00 | 0.00 | 0 | 0.00 | 01 |
| jydytg | | 1 | 78 | 10 | 0.01 | 288 | 2.88 | 0.00 | 0 | 0.00 | 01 |
| jyzxtc | 扬州中兴天成国际酒店 | 1 | 217 | 1 | 60.00 | 8 | 480.00 | 0.00 | 0 | 0.00 | 01 |
| jydytg | | 1 | 235 | 7 | 165.00 | 4 | 660.00 | 0.00 | 0 | 0.00 | 01 |
| jydytg | | 1 | 220 | 44 | 60.00 | 120 | 7200.00 | 0.00 | 0 | 0.00 | 01 |
| jytongcheng | 同程网 | 1 | 68 | 7 | 100.00 | 18 | 1800.00 | 0.00 | 0 | 0.00 | 01 |
| jylvmama | 驴妈妈网 | 1 | 247 | 14 | 60.00 | 42 | 2520.00 | 0.00 | 0 | 0.00 | 01 |
| jyjldjd | 扬州金陵大饭店 | 1 | 217 | 1 | 60.00 | 2 | 120.00 | 0.00 | 0 | 0.00 | 01 |
| dcmeituan | 美团网 | 1 | 68 | 7 | 100.00 | 34 | 3400.00 | 0.00 | 0 | 0.00 | 01 |
| jyyzwcl | 扬州望潮楼大酒店有限公司 | 1 | 217 | 1 | 60.00 | 20 | 1200.00 | 0.00 | 0 | 0.00 | 01 |
| jylvmama | 驴妈妈网 | 1 | 68 | 7 | 100.00 | 14 | 1400.00 | 0.00 | 0 | 0.00 | 01 |
| dcmeituan | 美团网 | 1 | 17 | 7 | 100.00 | 64 | 6400.00 | 0.00 | 0 | 0.00 | 01 |
| jytongcheng | 同程网 | 1 | 17 | 8 | 92.00 | 17 | 1564.00 | 0.00 | 0 | 0.00 | 01 |
| dcmeituan | 美团网 | 1 | 169 | 1 | 130.00 | 9 | 1170.00 | 0.00 | 0 | 0.00 | 01 |
| dcmeituan | 美团网 | 1 | 171 | 9 | 130.00 | 2 | 260.00 | 0.00 | 0 | 0.00 | 01 |
| jyctrip | 携程网 | 1 | 17 | 7 | 100.00 | 39 | 3900.00 | 0.00 | 0 | 0.00 | 01 |
| dcmeituan | 美团网 | 1 | 176 | 1 | 160.00 | 9 | 1440.00 | 0.00 | 0 | 0.00 | 01 |
| jytongcheng | 同程网 | 1 | 17 | 7 | 100.00 | 4 | 400.00 | 0.00 | 0 | 0.00 | 01 |
| jyjtjd | 君亭酒店 | 1 | 217 | 1 | 60.00 | 2 | 120.00 | 0.00 | 0 | 0.00 | 01 |
| dcmeituan | 美团网 | 1 | 17 | 8 | 92.00 | 35 | 3220.00 | 0.00 | 0 | 0.00 | 01 |
| dcmeituan | 美团网 | 1 | 170 | 1 | 130.00 | 10 | 1300.00 | 0.00 | 0 | 0.00 | 01 |
| jytongcheng | 同程网 | 1 | 177 | 1 | 190.00 | 7 | 1330.00 | 0.00 | 0 | 0.00 | 01 |
| jyctrip | 携程网 | 1 | 170 | 1 | 130.00 | 2 | 260.00 | 0.00 | 0 | 0.00 | 01 |
| jylvmama | 驴妈妈网 | 1 | 48 | 45 | 140.00 | 4 | 560.00 | 0.00 | 0 | 0.00 | 01 |
| feizhu | 1 | 1 | 17 | 8 | 92.00 | 3 | 276.00 | 0.00 | 0 | 0.00 | 01 |
| jyctrip | 携程网 | 1 | 173 | 1 | 160.00 | 2 | 320.00 | 0.00 | 0 | 0.00 | 01 |
| jyctrip | 携程网 | 1 | 17 | 8 | 92.00 | 30 | 2760.00 | 0.00 | 0 | 0.00 | 01 |
| jytongcheng | 同程网 | 1 | 170 | 1 | 130.00 | 2 | 260.00 | 0.00 | 0 | 0.00 | 01 |
| jyctrip | 携程网 | 1 | 23 | 9 | 130.00 | 5 | 650.00 | 0.00 | 0 | 0.00 | 01 |
| feizhu | 1 | 1 | 17 | 7 | 100.00 | 5 | 500.00 | 0.00 | 0 | 0.00 | 01 |
| jylvmama | 驴妈妈网 | 1 | 48 | 46 | 135.00 | 2 | 270.00 | 0.00 | 0 | 0.00 | 01 |
| jylvmama | 驴妈妈网 | 1 | 17 | 8 | 92.00 | 2 | 184.00 | 0.00 | 0 | 0.00 | 01 |
| jyctrip | 携程网 | 1 | 177 | 1 | 190.00 | 4 | 760.00 | 0.00 | 0 | 0.00 | 01 |
| jytongcheng | 同程网 | 1 | 173 | 1 | 160.00 | 2 | 320.00 | 0.00 | 0 | 0.00 | 01 |
| jyctrip | 携程网 | 1 | 174 | 1 | 160.00 | 3 | 480.00 | 0.00 | 0 | 0.00 | 01 |
| jytongcheng | 同程网 | 1 | 176 | 1 | 160.00 | 3 | 480.00 | 0.00 | 0 | 0.00 | 01 |
| SSTDZH | 赛思科技 | 1 | 59 | 1 | 90.00 | 6 | 540.00 | 0.00 | 0 | 0.00 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | 2 | 40.00 | 11 | 440.00 | 0.00 | 0 | 0.00 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | 3 | 50.00 | 3 | 150.00 | 0.00 | 0 | 0.00 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | 1 | 75.00 | 92 | 6900.00 | 0.00 | 0 | 0.00 | 01 |
| SSTDZH | 赛思科技 | 1 | 59 | 3 | 72.50 | 1 | 72.50 | 0.00 | 0 | 0.00 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | 2 | 50.00 | 1 | 50.00 | 0.00 | 0 | 0.00 | 01 |
| SSTDZH | 赛思科技 | 1 | 222 | 1 | 55.00 | 238 | 13090.00 | 0.00 | 0 | 0.00 | 01 |
| ylyzsxh | | 1 | 217 | 1 | 60.00 | 12 | 720.00 | 0.00 | 0 | 0.00 | 01 |
| SSTDZH | 赛思科技 | 1 | 59 | 2 | 55.00 | 12 | 660.00 | 0.00 | 0 | 0.00 | 01 |
| SSTDZH | 赛思科技 | 1 | 5 | 1 | 100.00 | 4 | 400.00 | 0.00 | 0 | 0.00 | 01 |
+-------------+--------------------------------------+-----------+---------------------+--------------------+------------------+------+----------+--------------+-------------+-------------+--------------------+
50 rows in set (0.32 sec)