天财商龙到底是一个什么公司

有些事情真是活久见，做了技术10年+，在今年对接天财商龙，由于需要建立数仓，问他们要了一些他们目前展示报表的查询sql，我贴出来，大家可能都会觉得简直是神奇了， 我看完直接语无伦次了，十几个文件，每个文件一条sql， 每个sql最少1500行+， 坐等如何处理。。。

详见下，大家觉得这样的SQL会有多少问题

SELECT SQL_CACHE

workDate,

deliverDate,

rdcCode,

rdcName,

rdcHouseCode,

rdcHouseName,

oppoCode,

oppoName,

oppoHouseCode,

oppoHouseName,

sum(costMoney) AS costMoney,

sum(costMoneyWithTax) AS costMoneyWithTax,

sum(noTaxMoney) AS noTaxMoney,

sum(grossProMoney) AS grossProMoney,

grossProTaxes,

sum(grossProMoneyWithTax) AS grossProMoneyWithTax,

sum(taxMoney) AS taxMoney,

sum(moneyWithTax) AS moneyWithTax,

supplierType,

supplierCode,

supplierName

FROM

(

SELECT

*

FROM

(

SELECT

outbill.workDate,

outbill.deliverDate,

rdc.id rdcId,

rdc. CODE rdcCode,

rdc. NAME rdcName,

rdcHouse.id rdcHouseId,

rdcHouse. CODE rdcHouseCode,

rdcHouse. NAME rdcHouseName,

shop.id shopId,

shop. CODE oppoCode,

shop. NAME oppoName,

shop.id oppoId,

shopHouse.id shopHouseId,

shopHouse. CODE oppoHouseCode,

shopHouse. NAME oppoHouseName,

outBill.costMoney - IFNULL(rtnBill.costMoney, 0) costMoney,

outBill.costMoneyWithTax - IFNULL(rtnBill.costMoneyWithTax, 0) costMoneyWithTax,

outbill.noTaxMoney - IFNULL(rtnBill.noTaxMoney, 0) noTaxMoney,

outbill.mlMoney - IFNULL(rtnBill.mlMoney, 0) grossProMoney,

CONCAT(

CAST(

ROUND(

CASE

WHEN outbill.noTaxMoney - IFNULL(rtnBill.noTaxMoney, 0) = 0 THEN

0

ELSE

(

outbill.mlMoney - IFNULL(rtnBill.mlMoney, 0)

) / (

outbill.noTaxMoney - IFNULL(rtnBill.noTaxMoney, 0)

)

END * 100,

2

) AS DECIMAL (13, 4)

) + '',

'%'

) AS grossProTaxes,

outBill.mlMoneyWithTax - IFNULL(rtnBill.mlMoneyWithTax, 0) grossProMoneyWithTax,

outBill.taxMoney - IFNULL(rtnBill.taxMoney, 0) taxMoney,

outbill.moneyWithTax - IFNULL(rtnBill.moneyWithTax, 0) moneyWithTax,

'' supplierType,

'' supplierCode,

'' supplierName,

'' supplierId,

'' supplierTypeId

FROM

(

SELECT

DATE_FORMAT(sb.workDate, '%Y-%m-%d') AS workDate,

DATE_FORMAT(sb.deliverDate, '%Y-%m-%d') AS deliverDate,

sb.shopId rdcId,

sb.otherPartyId,

sb.houseId outHouseId,

sbd.shopHouseId inHouseId,

SUM(sbd.noTaxOutMoney) costMoney,

SUM(sbd.outMoneyWithTax) costMoneyWithTax,

SUM(sbd.sendMoney) noTaxMoney,

SUM(

sbd.sendMoney - sbd.noTaxOutMoney

) mlMoney,

SUM(

sbd.sendMoneyWithTax - sbd.outMoneyWithTax

) mlMoneyWithTax,

SUM(sbd.sendTaxMoney) taxMoney,

SUM(sbd.sendMoneyWithTax) moneyWithTax

FROM

dj_storebill sb,

dj_storebilldetail sbd

WHERE

sb.billType = '32'

AND sb.id = sbd.billId

AND sb.billState = 1

AND sb.shopId = '4028874361e1be3b0161e1c0466c0003'

AND sb.workDate <= '2021-02-12 23:59:59'

AND sb.workDate >= '2021-01-19 00:00:00'

GROUP BY

DATE_FORMAT(sb.workDate, '%Y-%m-%d'),

DATE_FORMAT(sb.deliverDate, '%Y-%m-%d'),

sb.shopid,

sb.houseId,

sb.otherPartyId,

sbd.shopHouseId

) outBill

LEFT JOIN jc_shop rdc ON outbill.rdcId = rdc.id

LEFT JOIN jc_shop shop ON outbill.otherPartyId = shop.id

LEFT JOIN jc_house rdcHouse ON outBill.outHouseId = rdcHouse.id

LEFT JOIN jc_house shopHouse ON outBill.inHouseId = shopHouse.id

LEFT JOIN (

SELECT

DATE_FORMAT(sb.workDate, '%Y-%m-%d') AS workDate,

DATE_FORMAT(sb.deliverDate, '%Y-%m-%d') AS deliverDate,

sb.rdcId,

sb.otherPartyId,

sb.houseId,

sbd.shopHouseId,

SUM(sbd.inMoney) costMoney,

SUM(sbd.inMoneyWithTax) costMoneyWithTax,

SUM(sbd.sendMoney) noTaxMoney,

SUM(sbd.sendMoney - sbd.inMoney) mlMoney,

SUM(

sbd.sendMoneyWithTax - sbd.inMoneyWithTax

) mlMoneyWithTax,

SUM(sbd.sendTaxMoney) taxMoney,

SUM(sbd.sendMoneyWithTax) moneyWithTax

FROM

dj_storebill sb,

dj_storebilldetail sbd

WHERE

sb.billType = '35'

AND sb.id = sbd.billId

AND sb.billState = 1

AND sb.workDate <= '2021-02-12 23:59:59'

AND sb.workDate >= '2021-01-19 00:00:00'

GROUP BY

sb.rdcId,

sb.houseId,

sb.otherPartyId,

sbd.shopHouseId

) rtnBill ON outBill.rdcId = rtnBill.rdcId

AND outBill.otherPartyId = rtnBill.otherPartyId

AND outbill.inHouseId = rtnBill.shopHouseId

AND outbill.outHouseId = rtnBill.houseId

UNION ALL

SELECT

inBill.workDate AS workDate,

inBill.deliverDate AS deliverDate,

rdc.id rdcId,

rdc. CODE rdcCode,

rdc. NAME rdcName,

'' rdcHouseId,

'' rdcHouseCode,

'' rdcHouseName,

shop.id shopId,

shop. CODE oppoCode,

shop. NAME oppoName,

shop.id oppoId,

shopHouse.id shopHouseId,

shopHouse. CODE oppoHouseCode,

shopHouse. NAME oppoHouseName,

inBill.costMoney - IFNULL(rtnBIll.costMoney, 0) costMoney,

inBill.costMoneyWithTax - IFNULL(rtnBIll.costMoneyWithTax, 0) costMoneyWithTax,

inBill.noTaxMoney - IFNULL(rtnBIll.noTaxMoney, 0) noTaxMoney,

inBill.mlMoney - IFNULL(rtnBIll.mlMoney, 0) mlMoney,

CONCAT(

CAST(

ROUND(

CASE

WHEN inBill.noTaxMoney - IFNULL(rtnBIll.noTaxMoney, 0) = 0 THEN

0

ELSE

(

inBill.mlMoney - IFNULL(rtnBIll.mlMoney, 0)

) / (

inBill.noTaxMoney - IFNULL(rtnBIll.noTaxMoney, 0)

)

END * 100,

2

) AS DECIMAL (13, 4)

) + '',

'%'

) AS grossProTaxes,

inBill.mlMoneyWithTax - IFNULL(rtnBIll.mlMoneyWithTax, 0) mlMoneyWithTax,

inBill.TaxMoney - IFNULL(rtnBIll.taxMoney, 0) taxMoney,

inBill.moneyWithTax - IFNULL(rtnBIll.moneyWithTax, 0) moneyWithTax,

supplier.typeName supplierType,

supplier. CODE supplierCode,

supplier. NAME supplierName,

supplier.id supplierId,

supplier.typeId supplierTypeId

FROM

(

SELECT

DATE_FORMAT(sb.workDate, '%Y-%m-%d') AS workDate,

DATE_FORMAT(sb.deliverDate, '%Y-%m-%d') AS deliverDate,

sb.rdcId AS rdcId,

sb.supplierId,

sb.shopId,

sbd.inHouseId,

sum(sbd.centerCostMoney) costMoney,

sum(sbd.centerCostMoneyWithTax) costMoneyWithTax,

sum(sbd.inMoney) noTaxMoney,

sum(

sbd.inMoney - sbd.centerCostMoney

) mlMoney,

sum(

sbd.inMoneyWithTax - sbd.centerCostMoneyWithTax

) mlMoneyWithTax,

sum(sbd.taxInMoney) taxMoney,

sum(sbd.inMoneyWithTax) moneyWithTax

FROM

dj_storebill sb,

dj_storebilldetail sbd

WHERE

sb.billType = '20'

AND sb.stockType = 1

AND sb.billState = 1

AND sb.id = sbd.billId

AND sb.workDate <= '2021-02-12 23:59:59'

AND sb.workDate >= '2021-01-19 00:00:00'

GROUP BY

DATE_FORMAT(sb.workDate, '%Y-%m-%d'),

DATE_FORMAT(sb.deliverDate, '%Y-%m-%d'),

sb.rdcId,

sb.shopId,

sbd.inHouseId,

sb.supplierId

) inBill

LEFT JOIN jc_shop rdc ON inBill.rdcId = rdc.id

LEFT JOIN jc_shop shop ON inBill.shopId = shop.id

LEFT JOIN jc_house shopHouse ON inBill.inHouseId = shopHouse.id

LEFT JOIN (

SELECT

a.id,

a. CODE,

a. NAME,

b. NAME typeName,

b.id typeId

FROM

jc_supplier a,

jc_supplier_type b

WHERE

a.supplierTypeId = b.id

) supplier ON supplier.id = inBill.supplierId

LEFT JOIN (

SELECT

rtn.workDate,

rtn.deliverDate,

rtn.rdcId,

rtn.shopId,

rtn.shopHouseId,

rtn.supplierId,

sum(

(

inb.centerCostMoney / inb.mainUnitInAmount

) * rtn.mainUnitOutAmount

) costMoney,

sum(

(

inb.centerCostMoneyWithTax / inb.mainUnitInAmount

) * rtn.mainUnitOutAmount

) costMoneyWithTax,

sum(rtn.noTaxOutMoney) noTaxMoney,

sum(

rtn.noTaxOutMoney - (

(

inb.centerCostMoney / inb.mainUnitInAmount

) * rtn.mainUnitOutAmount

)

) mlMoney,

sum(

rtn.outMoneyWithTax - (

(

inb.centerCostMoneyWithTax / inb.mainUnitInAmount

) * rtn.mainUnitOutAmount

)

) mlMoneyWithTax,

sum(rtn.taxOutMoney) taxMoney,

sum(rtn.outMoneyWithTax) moneyWithTax

FROM

(

SELECT

DATE_FORMAT(sb.workDate, '%Y-%m-%d') AS workDate,

DATE_FORMAT(sb.deliverDate, '%Y-%m-%d') AS deliverDate,

sb.rdcId,

sb.shopId,

sb.supplierId,

sbd.outHouseId shopHouseId,

sbd.goodsId,

sbd.batchCode,

sbd.mainUnitOutAmount,

sbd.noTaxOutMoney,

sbd.taxOutMoney,

sbd.outMoneyWithTax

FROM

dj_storebill sb,

dj_storebilldetail sbd

WHERE

sb.billType = '21'

AND sb.stockType = 1

AND sb.billState = 1

AND sb.workDate <= '2021-02-12 23:59:59'

AND sb.workDate >= '2021-01-19 00:00:00'

AND sb.id = sbd.billId

) rtn

LEFT JOIN (

SELECT

sbd.batchCode,

sbd.goodsId,

sbd.mainUnitInAmount,

sbd.centerCostMoney,

sbd.centerCostMoneyWithTax

FROM

dj_storebill sb,

dj_storebilldetail sbd

WHERE

sb.billType = '20'

AND sb.stockType = 1

AND sb.billState = 1

AND sb.workDate <= '2021-02-12 23:59:59'

AND sb.workDate >= '2021-01-19 00:00:00'

AND sb.id = sbd.billId

) inb ON rtn.batchCode = inb.batchCode

AND rtn.goodsId = inb.goodsId

GROUP BY

rtn.rdcId,

rtn.supplierId,

rtn.shopId,

rtn.shopHouseId

) rtnBIll ON inBill.rdcId = rtnBIll.rdcId

AND inBill.shopId = rtnBIll.shopId

AND inBill.inHouseId = rtnBill.shopHouseId

UNION ALL

SELECT

inbill.workDate,

inbill.deliverDate AS deliverDate,

rdc.id rdcId,

rdc. CODE rdcCode,

rdc. NAME rdcName,

'' rdcHouseId,

'' rdcHouseCode,

'' rdcHouseName,

shop.id shopId,

shop. CODE oppoCode,

shop. NAME oppoName,

shop.id oppoId,

shopHouse.id shopHouseId,

shopHouse. CODE oppoHouseCode,

shopHouse. NAME oppoHouseName,

inBill.costMoney - IFNULL(rtnBIll.costMoney, 0) costMoney,

inBill.costMoneyWithTax - IFNULL(rtnBIll.costMoneyWithTax, 0) costMoneyWithTax,

inBill.noTaxMoney - IFNULL(rtnBIll.noTaxMoney, 0) noTaxMoney,

0 mlMoney,

CONCAT(

CAST(

ROUND(0 * 100, 2) AS DECIMAL (13, 4)

) + '',

'%'

) AS grossProTaxes,

0 mlMoneyWithTax,

inBill.TaxMoney - IFNULL(rtnBIll.taxMoney, 0) taxMoney,

inBill.moneyWithTax - IFNULL(rtnBIll.moneyWithTax, 0) moneyWithTax,

supplier.typeName supplierType,

supplier. CODE supplierCode,

supplier. NAME supplierName,

supplier.id supplierId,

supplier.typeId supplierTypeId

FROM

(

SELECT

DATE_FORMAT(sb.workDate, '%Y-%m-%d') AS workDate,

DATE_FORMAT(sb.deliverDate, '%Y-%m-%d') AS deliverDate,

sb.rdcId AS rdcId,

sb.supplierId,

sb.shopId,

sbd.inHouseId,

sum(sbd.inMoney) costMoney,

sum(sbd.inMoneyWithTax) costMoneyWithTax,

sum(sbd.inMoney) noTaxMoney,

sum(sbd.taxInMoney) taxMoney,

sum(sbd.inMoneyWithTax) moneyWithTax

FROM

dj_storebill sb,

dj_storebilldetail sbd

WHERE

sb.billType = '42'

AND sb.billState = 1

AND sb.id = sbd.billId

AND sb.workDate <= '2021-02-12 23:59:59'

AND sb.workDate >= '2021-01-19 00:00:00'

GROUP BY

DATE_FORMAT(sb.workDate, '%Y-%m-%d'),

DATE_FORMAT(sb.deliverDate, '%Y-%m-%d'),

sb.otherPartyId,

sb.shopId,

sbd.inHouseId,

sb.supplierId

) inBill

LEFT JOIN jc_shop rdc ON inBill.rdcId = rdc.id

LEFT JOIN jc_shop shop ON inBill.shopId = shop.id

LEFT JOIN jc_house shopHouse ON inBill.inHouseId = shopHouse.id

LEFT JOIN (

SELECT

a.id,

a. CODE,

a. NAME,

b. NAME typeName,

b.id typeId

FROM

jc_supplier a,

jc_supplier_type b

WHERE

a.supplierTypeId = b.id

) supplier ON supplier.id = inBill.supplierId

LEFT JOIN (

SELECT

DATE_FORMAT(sb.workDate, '%Y-%m-%d') AS workDate,

DATE_FORMAT(sb.deliverDate, '%Y-%m-%d') AS deliverDate,

sb.rdcId,

sb.shopId,

sbd.outHouseId shopHouseId,

sb.supplierId,

sum(sbd.noTaxOutMoney) costMoney,

sum(sbd.outMoneyWithTax) costMoneyWithTax,

sum(sbd.noTaxOutMoney) noTaxMoney,

sum(sbd.taxOutMoney) taxMoney,

sum(sbd.outMoneyWithTax) moneyWithTax

FROM

dj_storebill sb,

dj_storebilldetail sbd

WHERE

sb.billType = '43'

AND sb.billState = 1

AND sb.workDate <= '2021-02-12 23:59:59'

AND sb.workDate >= '2021-01-19 00:00:00'

AND sb.id = sbd.billId

GROUP BY

sb.rdcId,

sb.supplierId,

sb.shopId,

sbd.outHouseId

) rtnBIll ON inBill.rdcId = rtnBIll.rdcId

AND inBill.shopId = rtnBIll.shopId

AND inBill.inHouseId = rtnBill.shopHouseId

AND inBill.supplierId = rtnBill.supplierId

UNION ALL

SELECT

inBill.workDate,

inBill.deliverDate AS deliverDate,

rdc.id rdcId,

rdc. CODE rdcCode,

rdc. NAME rdcName,

rdcHouse.id rdcHouseId,

rdcHouse. CODE rdcHouseCode,

rdcHouse. NAME rdcHouseName,

shop.id shopId,

shop. CODE oppoCode,

shop. NAME oppoName,

shop.id oppoId,

shopHouse.id shopHouseId,

shopHouse. CODE oppoHouseCode,

shopHouse. NAME oppoHouseName,

inBill.costMoney - IFNULL(rtnBIll.costMoney, 0) costMoney,

inBill.costMoneyWithTax - IFNULL(rtnBIll.costMoneyWithTax, 0) costMoneyWithTax,

inBill.noTaxMoney - IFNULL(rtnBIll.noTaxoutMoney, 0) noTaxMoney,

inBill.mlMoney - IFNULL(rtnBIll.mlMoney, 0) mlMoney,

CONCAT(

CAST(

ROUND(

CASE

WHEN inBill.noTaxMoney - IFNULL(rtnBIll.noTaxoutMoney, 0) = 0 THEN

0

ELSE

(

inBill.mlMoney - IFNULL(rtnBIll.mlMoney, 0)

) / (

inBill.noTaxMoney - IFNULL(rtnBIll.noTaxoutMoney, 0)

)

END * 100,

2

) AS DECIMAL (13, 4)

) + '',

'%'

) AS grossProTaxes,

inBill.mlMoneyWithTax - IFNULL(rtnBIll.mlMoneyWithTax, 0) mlMoneyWithTax,

inBill.TaxMoney - IFNULL(rtnBIll.taxOutMoney, 0) taxMoney,

inBill.moneyWithTax - IFNULL(rtnBIll.moneyWithTax, 0) moneyWithTax,

supplier.typeName supplierType,

supplier. CODE supplierCode,

supplier. NAME supplierName,

supplier.id supplierId,

supplier.typeId supplierTypeId

FROM

(

SELECT

DATE_FORMAT(sb.workDate, '%Y-%m-%d') AS workDate,

DATE_FORMAT(sb.deliverDate, '%Y-%m-%d') AS deliverDate,

sb.rdcId AS rdcId,

sb.supplierId,

sb.shopId,

sbd.inHouseId shopHouseId,

sbd.shopHouseId rdcHouseId,

sum(sbd.centerCostMoney) costMoney,

sum(sbd.centerCostMoneyWithTax) costMoneyWithTax,

sum(sbd.inMoney) noTaxMoney,

sum(sbd.taxInMoney) taxMoney,

sum(sbd.inMoneyWithTax) moneyWithTax,

sum(

sbd.inMoney - sbd.centerCostMoney

) mlMoney,

sum(

sbd.inMoneyWithTax - sbd.centerCostMoneyWithTax

) mlMoneyWithTax

FROM

dj_storebill sb,

dj_storebilldetail sbd

WHERE

sb.billType = '45'

AND sb.billState = 1

AND sb.id = sbd.billId

AND sb.workDate <= '2021-02-12 23:59:59'

AND sb.workDate >= '2021-01-19 00:00:00'

GROUP BY

DATE_FORMAT(sb.workDate, '%Y-%m-%d'),

DATE_FORMAT(sb.deliverDate, '%Y-%m-%d'),

sb.rdcId,

sb.supplierId,

sb.shopId,

sbd.inHouseId,

sbd.shopHouseId

) inBill

LEFT JOIN jc_shop rdc ON inBill.rdcId = rdc.id

LEFT JOIN jc_shop shop ON inBill.shopId = shop.id

LEFT JOIN jc_house shopHouse ON inBill.shopHouseId = shopHouse.id

LEFT JOIN jc_house rdcHouse ON inBill.rdcHouseId = rdcHouse.id

LEFT JOIN (

SELECT

a.id,

a. CODE,

a. NAME,

b. NAME typeName,

b.id typeId

FROM

jc_supplier a,

jc_supplier_type b

WHERE

a.supplierTypeId = b.id

) supplier ON supplier.id = inBill.supplierId

LEFT JOIN (

SELECT

DATE_FORMAT(sb.workDate, '%Y-%m-%d') AS workDate,

DATE_FORMAT(sb.deliverDate, '%Y-%m-%d') AS deliverDate,

sb.rdcId,

sb.shopId,

sbd.outHouseId shopHouseId,

sbd.shopHouseId rdcHouseId,

sb.supplierId,

sum(sbd.centerCostMoney) costMoney,

sum(sbd.centerCostMoneyWithTax) costMoneyWithTax,

sum(sbd.noTaxOutMoney) noTaxOutMoney,

sum(sbd.taxOutMoney) taxOutMoney,

sum(sbd.outMoneyWithTax) moneyWithTax,

sum(

sbd.noTaxOutMoney - sbd.centerCostMoney

) mlMoney,

sum(

sbd.outMoneyWithTax - sbd.centerCostMoneyWithTax

) mlMoneyWithTax

FROM

dj_storebill sb,

dj_storebilldetail sbd

WHERE

sb.billType = '48'

AND sb.billState = 1

AND sb.workDate <= '2021-02-12 23:59:59'

AND sb.workDate >= '2021-01-19 00:00:00'

AND sb.id = sbd.billId

GROUP BY

sb.rdcId,

sb.supplierId,

sb.shopId,

sbd.outHouseId,

sbd.shopHouseId

) rtnBIll ON inBill.rdcId = rtnBIll.rdcId

AND inBill.shopId = rtnBIll.shopId

AND inBill.shopHouseId = rtnBill.shopHouseId

AND inBill.rdcHouseId = rtnBill.rdcHouseId

AND inBill.supplierId = rtnBill.supplierId

UNION ALL

SELECT

outbill.workDate,

outbill.deliverDate,

rdc.id rdcId,

rdc. CODE rdcCode,

rdc. NAME rdcName,

rdcHouse.id rdcHouseId,

rdcHouse. CODE rdcHouseCode,

rdcHouse. NAME rdcHouseName,

shop.id shopId,

shop. CODE oppoCode,

shop. NAME oppoName,

shop.id oppoId,

shopHouse.id shopHouseId,

shopHouse. CODE oppoHouseCode,

shopHouse. NAME oppoHouseName,

outBill.costMoney - IFNULL(rtnBill.costMoney, 0) costMoney,

outBill.costMoneyWithTax - IFNULL(rtnBill.costMoneyWithTax, 0) costMoneyWithTax,

outbill.noTaxMoney - IFNULL(rtnBill.noTaxMoney, 0) noTaxMoney,

outbill.mlMoney - IFNULL(rtnBill.mlMoney, 0) grossProMoney,

CONCAT(

CAST(

ROUND(

CASE

WHEN outbill.noTaxMoney - IFNULL(rtnBill.noTaxMoney, 0) = 0 THEN

0

ELSE

(

outbill.mlMoney - IFNULL(rtnBill.mlMoney, 0)

) / (

outbill.noTaxMoney - IFNULL(rtnBill.noTaxMoney, 0)

)

END * 100,

2

) AS DECIMAL (13, 4)

) + '',

'%'

) AS grossProTaxes,

outBill.mlMoneyWithTax - IFNULL(rtnBill.mlMoneyWithTax, 0) grossProMoneyWithTax,

outBill.taxMoney - IFNULL(rtnBill.taxMoney, 0) taxMoney,

outbill.moneyWithTax - IFNULL(rtnBill.moneyWithTax, 0) moneyWithTax,

'' supplierType,

'' supplierCode,

'' supplierName,

'' supplierId,

'' supplierTypeId

FROM

(

SELECT

DATE_FORMAT(sb.workDate, '%Y-%m-%d') AS workDate,

DATE_FORMAT(sb.deliverDate, '%Y-%m-%d') AS deliverDate,

sb.shopId rdcId,

sb.otherPartyId,

sb.houseId outHouseId,

sbd.shopHouseId inHouseId,

SUM(sbd.noTaxOutMoney) costMoney,

SUM(sbd.outMoneyWithTax) costMoneyWithTax,

SUM(sbd.sendMoney) noTaxMoney,

SUM(

sbd.sendMoney - sbd.noTaxOutMoney

) mlMoney,

SUM(

sbd.sendMoneyWithTax - sbd.outMoneyWithTax

) mlMoneyWithTax,

SUM(sbd.sendTaxMoney) taxMoney,

SUM(sbd.sendMoneyWithTax) moneyWithTax

FROM

dj_storebillHistory sb,

dj_storebilldetailHistory sbd

WHERE

sb.billType = '32'

AND sb.id = sbd.billId

AND sb.billState = 1

AND sb.shopId = '4028874361e1be3b0161e1c0466c0003'

AND sb.workDate <= '2021-02-12 23:59:59'

AND sb.workDate >= '2021-01-19 00:00:00'

GROUP BY

DATE_FORMAT(sb.workDate, '%Y-%m-%d'),

DATE_FORMAT(sb.deliverDate, '%Y-%m-%d'),

sb.shopid,

sb.houseId,

sb.otherPartyId,

sbd.shopHouseId

) outBill

LEFT JOIN jc_shop rdc ON outbill.rdcId = rdc.id

LEFT JOIN jc_shop shop ON outbill.otherPartyId = shop.id

LEFT JOIN jc_house rdcHouse ON outBill.outHouseId = rdcHouse.id

LEFT JOIN jc_house shopHouse ON outBill.inHouseId = shopHouse.id

LEFT JOIN (

SELECT

DATE_FORMAT(sb.workDate, '%Y-%m-%d') AS workDate,

DATE_FORMAT(sb.deliverDate, '%Y-%m-%d') AS deliverDate,

sb.rdcId,

sb.otherPartyId,

sb.houseId,

sbd.shopHouseId,

SUM(sbd.inMoney) costMoney,

SUM(sbd.inMoneyWithTax) costMoneyWithTax,

SUM(sbd.sendMoney) noTaxMoney,

SUM(sbd.sendMoney - sbd.inMoney) mlMoney,

SUM(

sbd.sendMoneyWithTax - sbd.inMoneyWithTax

) mlMoneyWithTax,

SUM(sbd.sendTaxMoney) taxMoney,

SUM(sbd.sendMoneyWithTax) moneyWithTax

FROM

dj_storebillHistory sb,

dj_storebilldetailHistory sbd

WHERE

sb.billType = '35'

AND sb.id = sbd.billId

AND sb.billState = 1

AND sb.workDate <= '2021-02-12 23:59:59'

AND sb.workDate >= '2021-01-19 00:00:00'

GROUP BY

sb.rdcId,

sb.houseId,

sb.otherPartyId,

sbd.shopHouseId

) rtnBill ON outBill.rdcId = rtnBill.rdcId

AND outBill.otherPartyId = rtnBill.otherPartyId

AND outbill.inHouseId = rtnBill.shopHouseId

AND outbill.outHouseId = rtnBill.houseId

UNION ALL

SELECT

inBill.workDate AS workDate,

inBill.deliverDate AS deliverDate,

rdc.id rdcId,

rdc. CODE rdcCode,

rdc. NAME rdcName,

'' rdcHouseId,

'' rdcHouseCode,

'' rdcHouseName,

shop.id shopId,

shop. CODE oppoCode,

shop. NAME oppoName,

shop.id oppoId,

shopHouse.id shopHouseId,

shopHouse. CODE oppoHouseCode,

shopHouse. NAME oppoHouseName,

inBill.costMoney - IFNULL(rtnBIll.costMoney, 0) costMoney,

inBill.costMoneyWithTax - IFNULL(rtnBIll.costMoneyWithTax, 0) costMoneyWithTax,

inBill.noTaxMoney - IFNULL(rtnBIll.noTaxMoney, 0) noTaxMoney,

inBill.mlMoney - IFNULL(rtnBIll.mlMoney, 0) mlMoney,

CONCAT(

CAST(

ROUND(

CASE

WHEN inBill.noTaxMoney - IFNULL(rtnBIll.noTaxMoney, 0) = 0 THEN

0

ELSE

(

inBill.mlMoney - IFNULL(rtnBIll.mlMoney, 0)

) / (

inBill.noTaxMoney - IFNULL(rtnBIll.noTaxMoney, 0)

)

END * 100,

2

) AS DECIMAL (13, 4)

) + '',

'%'

) AS grossProTaxes,

inBill.mlMoneyWithTax - IFNULL(rtnBIll.mlMoneyWithTax, 0) mlMoneyWithTax,

inBill.TaxMoney - IFNULL(rtnBIll.taxMoney, 0) taxMoney,

inBill.moneyWithTax - IFNULL(rtnBIll.moneyWithTax, 0) moneyWithTax,

supplier.typeName supplierType,

supplier. CODE supplierCode,

supplier. NAME supplierName,

supplier.id supplierId,

supplier.typeId supplierTypeId

FROM

(

SELECT

DATE_FORMAT(sb.workDate, '%Y-%m-%d') AS workDate,

DATE_FORMAT(sb.deliverDate, '%Y-%m-%d') AS deliverDate,

sb.rdcId AS rdcId,

sb.supplierId,

sb.shopId,

sbd.inHouseId,

sum(sbd.centerCostMoney) costMoney,

sum(sbd.centerCostMoneyWithTax) costMoneyWithTax,

sum(sbd.inMoney) noTaxMoney,

sum(

sbd.inMoney - sbd.centerCostMoney

) mlMoney,

sum(

sbd.inMoneyWithTax - sbd.centerCostMoneyWithTax

) mlMoneyWithTax,

sum(sbd.taxInMoney) taxMoney,

sum(sbd.inMoneyWithTax) moneyWithTax

FROM

dj_storebillHistory sb,

dj_storebilldetailHistory sbd

WHERE

sb.billType = '20'

AND sb.stockType = 1

AND sb.billState = 1

AND sb.id = sbd.billId

AND sb.workDate <= '2021-02-12 23:59:59'

AND sb.workDate >= '2021-01-19 00:00:00'

GROUP BY

DATE_FORMAT(sb.workDate, '%Y-%m-%d'),

DATE_FORMAT(sb.deliverDate, '%Y-%m-%d'),

sb.rdcId,

sb.shopId,

sbd.inHouseId,

sb.supplierId

) inBill

LEFT JOIN jc_shop rdc ON inBill.rdcId = rdc.id

LEFT JOIN jc_shop shop ON inBill.shopId = shop.id

LEFT JOIN jc_house shopHouse ON inBill.inHouseId = shopHouse.id

LEFT JOIN (

SELECT

a.id,

a. CODE,

a. NAME,

b. NAME typeName,

b.id typeId

FROM

jc_supplier a,

jc_supplier_type b

WHERE

a.supplierTypeId = b.id

) supplier ON supplier.id = inBill.supplierId

LEFT JOIN (

SELECT

rtn.rdcId,

rtn.workDate,

rtn.deliverDate,

rtn.shopId,

rtn.shopHouseId,

rtn.supplierId,

sum(

(

inb.centerCostMoney / inb.mainUnitInAmount

) * rtn.mainUnitOutAmount

) costMoney,

sum(

(

inb.centerCostMoneyWithTax / inb.mainUnitInAmount

) * rtn.mainUnitOutAmount

) costMoneyWithTax,

sum(rtn.noTaxOutMoney) noTaxMoney,

sum(

rtn.noTaxOutMoney - (

(

inb.centerCostMoney / inb.mainUnitInAmount

) * rtn.mainUnitOutAmount

)

) mlMoney,

sum(

rtn.outMoneyWithTax - (

(

inb.centerCostMoneyWithTax / inb.mainUnitInAmount

) * rtn.mainUnitOutAmount

)

) mlMoneyWithTax,

sum(rtn.taxOutMoney) taxMoney,

sum(rtn.outMoneyWithTax) moneyWithTax

FROM

(

SELECT

DATE_FORMAT(sb.workDate, '%Y-%m-%d') AS workDate,

DATE_FORMAT(sb.deliverDate, '%Y-%m-%d') AS deliverDate,

sb.rdcId,

sb.shopId,

sb.supplierId,

sbd.outHouseId shopHouseId,

sbd.goodsId,

sbd.batchCode,

sbd.mainUnitOutAmount,

sbd.noTaxOutMoney,

sbd.taxOutMoney,

sbd.outMoneyWithTax

FROM

dj_storebillHistory sb,

dj_storebilldetailHistory sbd

WHERE

sb.billType = '21'

AND sb.stockType = 1

AND sb.billState = 1

AND sb.workDate <= '2021-02-12 23:59:59'

AND sb.workDate >= '2021-01-19 00:00:00'

AND sb.id = sbd.billId

) rtn

LEFT JOIN (

SELECT

sbd.batchCode,

sbd.goodsId,

sbd.mainUnitInAmount,

sbd.centerCostMoney,

sbd.centerCostMoneyWithTax

FROM

dj_storebillHistory sb,

dj_storebilldetailHistory sbd

WHERE

sb.billType = '20'

AND sb.stockType = 1

AND sb.billState = 1

AND sb.workDate <= '2021-02-12 23:59:59'

AND sb.workDate >= '2021-01-19 00:00:00'

AND sb.id = sbd.billId

) inb ON rtn.batchCode = inb.batchCode

AND rtn.goodsId = inb.goodsId

GROUP BY

rtn.rdcId,

rtn.supplierId,

rtn.shopId,

rtn.shopHouseId

) rtnBIll ON inBill.rdcId = rtnBIll.rdcId

AND inBill.shopId = rtnBIll.shopId

AND inBill.inHouseId = rtnBill.shopHouseId

UNION ALL

SELECT

inbill.workDate,

inbill.deliverDate AS deliverDate,

rdc.id rdcId,

rdc. CODE rdcCode,

rdc. NAME rdcName,

'' rdcHouseId,

'' rdcHouseCode,

'' rdcHouseName,

shop.id shopId,

shop. CODE oppoCode,

shop. NAME oppoName,

shop.id oppoId,

shopHouse.id shopHouseId,

shopHouse. CODE oppoHouseCode,

shopHouse. NAME oppoHouseName,

inBill.costMoney - IFNULL(rtnBIll.costMoney, 0) costMoney,

inBill.costMoneyWithTax - IFNULL(rtnBIll.costMoneyWithTax, 0) costMoneyWithTax,

inBill.noTaxMoney - IFNULL(rtnBIll.noTaxMoney, 0) noTaxMoney,

0 mlMoney,

CONCAT(

CAST(

ROUND(0 * 100, 2) AS DECIMAL (13, 4)

) + '',

'%'

) AS grossProTaxes,

0 mlMoneyWithTax,

inBill.TaxMoney - IFNULL(rtnBIll.taxMoney, 0) taxMoney,

inBill.moneyWithTax - IFNULL(rtnBIll.moneyWithTax, 0) moneyWithTax,

supplier.typeName supplierType,

supplier. CODE supplierCode,

supplier. NAME supplierName,

supplier.id supplierId,

supplier.typeId supplierTypeId

FROM

(

SELECT

DATE_FORMAT(sb.workDate, '%Y-%m-%d') AS workDate,

DATE_FORMAT(sb.deliverDate, '%Y-%m-%d') AS deliverDate,

sb.rdcId AS rdcId,

sb.supplierId,

sb.shopId,

sbd.inHouseId,

sum(sbd.inMoney) costMoney,

sum(sbd.inMoneyWithTax) costMoneyWithTax,

sum(sbd.inMoney) noTaxMoney,

sum(sbd.taxInMoney) taxMoney,

sum(sbd.inMoneyWithTax) moneyWithTax

FROM

dj_storebillHistory sb,

dj_storebilldetailHistory sbd

WHERE

sb.billType = '42'

AND sb.billState = 1

AND sb.id = sbd.billId

AND sb.workDate <= '2021-02-12 23:59:59'

AND sb.workDate >= '2021-01-19 00:00:00'

GROUP BY

DATE_FORMAT(sb.workDate, '%Y-%m-%d'),

DATE_FORMAT(sb.deliverDate, '%Y-%m-%d'),

sb.otherPartyId,

sb.shopId,

sbd.inHouseId,

sb.supplierId

) inBill

LEFT JOIN jc_shop rdc ON inBill.rdcId = rdc.id

LEFT JOIN jc_shop shop ON inBill.shopId = shop.id

LEFT JOIN jc_house shopHouse ON inBill.inHouseId = shopHouse.id

LEFT JOIN (

SELECT

a.id,

a. CODE,

a. NAME,

b. NAME typeName,

b.id typeId

FROM

jc_supplier a,

jc_supplier_type b

WHERE

a.supplierTypeId = b.id

) supplier ON supplier.id = inBill.supplierId

LEFT JOIN (

SELECT

DATE_FORMAT(sb.workDate, '%Y-%m-%d') AS workDate,

DATE_FORMAT(sb.deliverDate, '%Y-%m-%d') AS deliverDate,

sb.rdcId,

sb.shopId,

sbd.outHouseId shopHouseId,

sb.supplierId,

sum(sbd.noTaxOutMoney) costMoney,

sum(sbd.outMoneyWithTax) costMoneyWithTax,

sum(sbd.noTaxOutMoney) noTaxMoney,

sum(sbd.taxOutMoney) taxMoney,

sum(sbd.outMoneyWithTax) moneyWithTax

FROM

dj_storebillHistory sb,

dj_storebilldetailHistory sbd

WHERE

sb.billType = '43'

AND sb.billState = 1

AND sb.workDate <= '2021-02-12 23:59:59'

AND sb.workDate >= '2021-01-19 00:00:00'

AND sb.id = sbd.billId

GROUP BY

sb.rdcId,

sb.supplierId,

sb.shopId,

sbd.outHouseId

) rtnBIll ON inBill.rdcId = rtnBIll.rdcId

AND inBill.shopId = rtnBIll.shopId

AND inBill.inHouseId = rtnBill.shopHouseId

AND inBill.supplierId = rtnBill.supplierId

UNION ALL

SELECT

inBill.workDate,

inBill.deliverDate AS deliverDate,

rdc.id rdcId,

rdc. CODE rdcCode,

rdc. NAME rdcName,

rdcHouse.id rdcHouseId,

rdcHouse. CODE rdcHouseCode,

rdcHouse. NAME rdcHouseName,

shop.id shopId,

shop. CODE oppoCode,

shop. NAME oppoName,

shop.id oppoId,

shopHouse.id shopHouseId,

shopHouse. CODE oppoHouseCode,

shopHouse. NAME oppoHouseName,

inBill.costMoney - IFNULL(rtnBIll.costMoney, 0) costMoney,

inBill.costMoneyWithTax - IFNULL(rtnBIll.costMoneyWithTax, 0) costMoneyWithTax,

inBill.noTaxMoney - IFNULL(rtnBIll.noTaxoutMoney, 0) noTaxMoney,

inBill.mlMoney - IFNULL(rtnBIll.mlMoney, 0) mlMoney,

CONCAT(

CAST(

ROUND(

CASE

WHEN inBill.noTaxMoney - IFNULL(rtnBIll.noTaxoutMoney, 0) = 0 THEN

0

ELSE

(

inBill.mlMoney - IFNULL(rtnBIll.mlMoney, 0)

) / (

inBill.noTaxMoney - IFNULL(rtnBIll.noTaxoutMoney, 0)

)

END * 100,

2

) AS DECIMAL (13, 4)

) + '',

'%'

) AS grossProTaxes,

inBill.mlMoneyWithTax - IFNULL(rtnBIll.mlMoneyWithTax, 0) mlMoneyWithTax,

inBill.TaxMoney - IFNULL(rtnBIll.taxOutMoney, 0) taxMoney,

inBill.moneyWithTax - IFNULL(rtnBIll.moneyWithTax, 0) moneyWithTax,

supplier.typeName supplierType,

supplier. CODE supplierCode,

supplier. NAME supplierName,

supplier.id supplierId,

supplier.typeId supplierTypeId

FROM

(

SELECT

DATE_FORMAT(sb.workDate, '%Y-%m-%d') AS workDate,

DATE_FORMAT(sb.deliverDate, '%Y-%m-%d') AS deliverDate,

sb.rdcId AS rdcId,

sb.supplierId,

sb.shopId,

sbd.inHouseId shopHouseId,

sbd.shopHouseId rdcHouseId,

sum(sbd.centerCostMoney) costMoney,

sum(sbd.centerCostMoneyWithTax) costMoneyWithTax,

sum(sbd.inMoney) noTaxMoney,

sum(sbd.taxInMoney) taxMoney,

sum(sbd.inMoneyWithTax) moneyWithTax,

sum(

sbd.inMoney - sbd.centerCostMoney

) mlMoney,

sum(

sbd.inMoneyWithTax - sbd.centerCostMoneyWithTax

) mlMoneyWithTax

FROM

dj_storebillHistory sb,

dj_storebilldetailHistory sbd

WHERE

sb.billType = '45'

AND sb.billState = 1

AND sb.id = sbd.billId

AND sb.workDate <= '2021-02-12 23:59:59'

AND sb.workDate >= '2021-01-19 00:00:00'

GROUP BY

DATE_FORMAT(sb.workDate, '%Y-%m-%d'),

DATE_FORMAT(sb.deliverDate, '%Y-%m-%d'),

sb.rdcId,

sb.supplierId,

sb.shopId,

sbd.inHouseId,

sbd.shopHouseId

) inBill

LEFT JOIN jc_shop rdc ON inBill.rdcId = rdc.id

LEFT JOIN jc_shop shop ON inBill.shopId = shop.id

LEFT JOIN jc_house shopHouse ON inBill.shopHouseId = shopHouse.id

LEFT JOIN jc_house rdcHouse ON inBill.rdcHouseId = rdcHouse.id

LEFT JOIN (

SELECT

a.id,

a. CODE,

a. NAME,

b. NAME typeName,

b.id typeId

FROM

jc_supplier a,

jc_supplier_type b

WHERE

a.supplierTypeId = b.id

) supplier ON supplier.id = inBill.supplierId

LEFT JOIN (

SELECT

DATE_FORMAT(sb.workDate, '%Y-%m-%d') AS workDate,

DATE_FORMAT(sb.deliverDate, '%Y-%m-%d') AS deliverDate,

sb.rdcId,

sb.shopId,

sbd.outHouseId shopHouseId,

sbd.shopHouseId rdcHouseId,

sb.supplierId,

sum(sbd.centerCostMoney) costMoney,

sum(sbd.centerCostMoneyWithTax) costMoneyWithTax,

sum(sbd.noTaxOutMoney) noTaxOutMoney,

sum(sbd.taxOutMoney) taxOutMoney,

sum(sbd.outMoneyWithTax) moneyWithTax,

sum(

sbd.noTaxOutMoney - sbd.centerCostMoney

) mlMoney,

sum(

sbd.outMoneyWithTax - sbd.centerCostMoneyWithTax

) mlMoneyWithTax

FROM

dj_storebillHistory sb,

dj_storebilldetailHistory sbd

WHERE

sb.billType = '48'

AND sb.billState = 1

AND sb.workDate <= '2021-02-12 23:59:59'

AND sb.workDate >= '2021-01-19 00:00:00'

AND sb.id = sbd.billId

GROUP BY

sb.rdcId,

sb.supplierId,

sb.shopId,

sbd.outHouseId,

sbd.shopHouseId

) rtnBIll ON inBill.rdcId = rtnBIll.rdcId

AND inBill.shopId = rtnBIll.shopId

AND inBill.shopHouseId = rtnBill.shopHouseId

AND inBill.rdcHouseId = rtnBill.rdcHouseId

AND inBill.supplierId = rtnBill.supplierId

) result

WHERE

1 = 1

) baseTable

WHERE

(

baseTable.rdcId = '4028874361e1be3b0161e1c0466c0003'

)

GROUP BY

workDate,

deliverDate,

rdcCode,

rdcName,

rdcHouseCode,

rdcHouseName,

oppoCode,

oppoName,

oppoHouseCode,

oppoHouseName,

grossProTaxes,

supplierType,

supplierCode,

supplierName