1.公司一个报表查询业务,sql如下:
SELECT tend.sellId, au.name AS userName, SUM(sellTotalPrice) AS sellTotalPrice
, SUM(refundTotalPrice) AS refundTotalPrice, SUM(sellTotalPrice_ali) AS sellTotalPrice_ali
, SUM(refundTotalPrice_ali) AS refundTotalPrice_ali, SUM(sellTotalPrice_wx) AS sellTotalPrice_wx
, SUM(refundTotalPrice_wx) AS refundTotalPrice_wx, SUM(sellTotalPrice_union) AS sellTotalPrice_union
, SUM(refundTotalPrice_union) AS refundTotalPrice_union
FROM (
SELECT tol.*
, CASE
WHEN orderUserId IS NOT NULL THEN orderUserId
ELSE createUser
END AS sellId
FROM (
(SELECT ttt.orderUserId, ttt.sellTotalPrice, ttt.sellTotalPrice_ali, ttt.sellTotalPrice_union, ttt.sellTotalPrice_wx
, tp.*
FROM (
SELECT orderUserId, payWay, SUM(sellTotalPrice) AS sellTotalPrice
, SUM(sellTotalPrice_ali) AS sellTotalPrice_ali, SUM(sellTotalPrice_union) AS sellTotalPrice_union
, SUM(sellTotalPrice_wx) AS sellTotalPrice_wx
FROM (
(SELECT accounter AS orderUserId, payWay, payTotalSum
, CASE
WHEN payWay = 1 THEN payTotalSum
ELSE 0
END AS sellTotalPrice
, CASE
WHEN payWay = 15 THEN payTotalSum
ELSE 0
END AS sellTotalPrice_ali
, CASE
WHEN payWay = 16 THEN payTotalSum
ELSE 0
END AS sellTotalPrice_union
, CASE
WHEN payWay = 14 THEN payTotalSum
ELSE 0
END AS sellTotalPrice_wx
FROM (
SELECT accounter, payWay, SUM(payTotal) AS payTotalSum
FROM t_order
WHERE (accountTime >= NULL
AND accountTime <= NULL
AND orderChannel = 2
AND accountState = 1
AND payStatus = 1
AND isDelete = 0)
GROUP BY accounter, payWay
) tod)
UNION ALL
(SELECT accounter AS orderUserId, payWay, payTotalSum
, CASE
WHEN payWay = 1 THEN payTotalSum
ELSE 0
END AS sellTotalPrice
, CASE
WHEN payWay = 15 THEN payTotalSum
ELSE 0
END AS sellTotalPrice_ali
, CASE
WHEN payWay = 16 THEN payTotalSum
ELSE 0
END AS sellTotalPrice_union
, CASE
WHEN payWay = 14 THEN payTotalSum
ELSE 0
END AS sellTotalPrice_wx
FROM (
SELECT accounter, payWay, SUM(payTotal) AS payTotalSum
FROM t_history_order
WHERE (accountTime >= NULL
AND accountTime <= NULL
AND orderChannel = 2
AND accountState = 1
AND payStatus = 1
AND isDelete = 0)
GROUP BY accounter, payWay
) tod)
) tt
GROUP BY orderUserId, payWay
) ttt
LEFT JOIN (
SELECT createUser, payWay, actualPriceSum, SUM(refundTotalPrice) AS refundTotalPrice
, SUM(refundTotalPrice_ali) AS refundTotalPrice_ali, SUM(refundTotalPrice_union) AS refundTotalPrice_union
, SUM(refundTotalPrice_wx) AS refundTotalPrice_wx
FROM (
SELECT createUser, payWay, actualPriceSum
, CASE
WHEN payWay = 1 THEN actualPriceSum
ELSE 0
END AS refundTotalPrice
, CASE
WHEN payWay = 15 THEN actualPriceSum
ELSE 0
END AS refundTotalPrice_ali
, CASE
WHEN payWay = 16 THEN actualPriceSum
ELSE 0
END AS refundTotalPrice_union
, CASE
WHEN payWay = 14 THEN actualPriceSum
ELSE 0
END AS refundTotalPrice_wx
FROM (
SELECT createUser, payWay, SUM(actualPriceSum) AS actualPriceSum
FROM (
(SELECT t.payWay, tod.accounter AS createUser, SUM(tod.actualPrice) AS actualPriceSum
FROM (
SELECT orderId, accounter, actualPrice
FROM t_order_detail
WHERE (accountTime >= NULL
AND accountTime <= NULL
AND accountState = 1
AND isDelete = 0
AND refundStatus = 5)
) tod, t_order t
WHERE (t.orderChannel = 2
AND tod.orderId = t.id
AND t.isDelete = 0)
GROUP BY tod.accounter, t.payWay)
UNION ALL
(SELECT t.payWay, tod.accounter AS createUser, SUM(tod.actualPrice) AS actualPriceSum
FROM (
SELECT orderId, accounter, actualPrice
FROM t_order_detail_sessions
WHERE (accountTime >= NULL
AND accountTime <= NULL
AND accountState = 1
AND isDelete = 0
AND refundStatus = 5)
) tod, t_order t
WHERE (t.orderChannel = 2
AND tod.orderId = t.id
AND t.isDelete = 0)
GROUP BY tod.accounter, t.payWay)
UNION ALL
(SELECT t.payWay, tod.accounter AS createUser, SUM(tod.actualPrice) AS actualPriceSum
FROM (
SELECT orderId, accounter, actualPrice
FROM t_order_detail_long
WHERE (accountTime >= NULL
AND accountTime <= NULL
AND accountState = 1
AND isDelete = 0
AND refundStatus = 5)
) tod, t_order t
WHERE (t.orderChannel = 2
AND tod.orderId = t.id
AND t.isDelete = 0)
GROUP BY tod.accounter, t.payWay)
UNION ALL
(SELECT t.payWay, tod.accounter AS createUser, SUM(tod.actualPrice) AS actualPriceSum
FROM (
SELECT orderId, accounter, actualPrice
FROM t_history_order_detail
WHERE (accountTime >= NULL
AND accountTime <= NULL
AND accountState = 1
AND isDelete = 0
AND refundStatus = 5)
) tod, t_history_order t
WHERE (t.orderChannel = 2
AND tod.orderId = t.id
AND t.isDelete = 0)
GROUP BY tod.accounter, t.payWay)
UNION ALL
(SELECT t.payWay, tod.accounter AS createUser, SUM(tod.actualPrice) AS actualPriceSum
FROM (
SELECT orderId, accounter, actualPrice
FROM t_history_order_detail_sessions
WHERE (accountTime >= NULL
AND accountTime <= NULL
AND accountState = 1
AND isDelete = 0
AND refundStatus = 5)
) tod, t_history_order t
WHERE (t.orderChannel = 2
AND tod.orderId = t.id
AND t.isDelete = 0)
GROUP BY tod.accounter, t.payWay)
UNION ALL
(SELECT t.payWay, tod.accounter AS createUser, SUM(tod.actualPrice) AS actualPriceSum
FROM (
SELECT orderId, accounter, actualPrice
FROM t_history_order_detail_long
WHERE (accountTime >= NULL
AND accountTime <= NULL
AND accountState = 1
AND isDelete = 0
AND refundStatus = 5)
) tod, t_history_order t
WHERE (t.orderChannel = 2
AND tod.orderId = t.id
AND t.isDelete = 0)
GROUP BY tod.accounter, t.payWay)
) tdall
GROUP BY createUser, payWay
) tr
) trr
GROUP BY createUser, payWay, actualPriceSum
) tp
ON ttt.orderUserId = tp.createUser
AND ttt.payWay = tp.payWay)
UNION
(SELECT ttt.orderUserId, ttt.sellTotalPrice, ttt.sellTotalPrice_ali, ttt.sellTotalPrice_union, ttt.sellTotalPrice_wx
, tp.*
FROM (
SELECT orderUserId, payWay, SUM(sellTotalPrice) AS sellTotalPrice
, SUM(sellTotalPrice_ali) AS sellTotalPrice_ali, SUM(sellTotalPrice_union) AS sellTotalPrice_union
, SUM(sellTotalPrice_wx) AS sellTotalPrice_wx
FROM (
(SELECT accounter AS orderUserId, payWay, payTotalSum
, CASE
WHEN payWay = 1 THEN payTotalSum
ELSE 0
END AS sellTotalPrice
, CASE
WHEN payWay = 15 THEN payTotalSum
ELSE 0
END AS sellTotalPrice_ali
, CASE
WHEN payWay = 16 THEN payTotalSum
ELSE 0
END AS sellTotalPrice_union
, CASE
WHEN payWay = 14 THEN payTotalSum
ELSE 0
END AS sellTotalPrice_wx
FROM (
SELECT accounter, payWay, SUM(payTotal) AS payTotalSum
FROM t_order
WHERE (accountTime >= NULL
AND accountTime <= NULL
AND orderChannel = 2
AND accountState = 1
AND payStatus = 1
AND isDelete = 0)
GROUP BY accounter, payWay
) tod)
UNION ALL
(SELECT accounter AS orderUserId, payWay, payTotalSum
, CASE
WHEN payWay = 1 THEN payTotalSum
ELSE 0
END AS sellTotalPrice
, CASE
WHEN payWay = 15 THEN payTotalSum
ELSE 0
END AS sellTotalPrice_ali
, CASE
WHEN payWay = 16 THEN payTotalSum
ELSE 0
END AS sellTotalPrice_union
, CASE
WHEN payWay = 14 THEN payTotalSum
ELSE 0
END AS sellTotalPrice_wx
FROM (
SELECT accounter, payWay, SUM(payTotal) AS payTotalSum
FROM t_history_order
WHERE (accountTime >= NULL
AND accountTime <= NULL
AND orderChannel = 2
AND accountState = 1
AND payStatus = 1
AND isDelete = 0)
GROUP BY accounter, payWay
) tod)
) tt
GROUP BY orderUserId, payWay
) ttt
RIGHT JOIN (
SELECT createUser, payWay, actualPriceSum, SUM(refundTotalPrice) AS refundTotalPrice
, SUM(refundTotalPrice_ali) AS refundTotalPrice_ali, SUM(refundTotalPrice_union) AS refundTotalPrice_union
, SUM(refundTotalPrice_wx) AS refundTotalPrice_wx
FROM (
SELECT createUser, payWay, actualPriceSum
, CASE
WHEN payWay = 1 THEN actualPriceSum
ELSE 0
END AS refundTotalPrice
, CASE
WHEN payWay = 15 THEN actualPriceSum
ELSE 0
END AS refundTotalPrice_ali
, CASE
WHEN payWay = 16 THEN actualPriceSum
ELSE 0
END AS refundTotalPrice_union
, CASE
WHEN payWay = 14 THEN actualPriceSum
ELSE 0
END AS refundTotalPrice_wx
FROM (
SELECT createUser, payWay, SUM(actualPriceSum) AS actualPriceSum
FROM (
(SELECT t.payWay, tod.accounter AS createUser, SUM(tod.actualPrice) AS actualPriceSum
FROM (
SELECT orderId, accounter, actualPrice
FROM t_order_detail
WHERE (accountTime >= NULL
AND accountTime <= NULL
AND accountState = 1
AND isDelete = 0
AND refundStatus = 5)
) tod, t_order t
WHERE (t.orderChannel = 2
AND tod.orderId = t.id
AND t.isDelete = 0)
GROUP BY tod.accounter, t.payWay)
UNION ALL
(SELECT t.payWay, tod.accounter AS createUser, SUM(tod.actualPrice) AS actualPriceSum
FROM (
SELECT orderId, accounter, actualPrice
FROM t_order_detail_sessions
WHERE (accountTime >= NULL
AND accountTime <= NULL
AND accountState = 1
AND isDelete = 0
AND refundStatus = 5)
) tod, t_order t
WHERE (t.orderChannel = 2
AND tod.orderId = t.id
AND t.isDelete = 0)
GROUP BY tod.accounter, t.payWay)
UNION ALL
(SELECT t.payWay, tod.accounter AS createUser, SUM(tod.actualPrice) AS actualPriceSum
FROM (
SELECT orderId, accounter, actualPrice
FROM t_order_detail_long
WHERE (accountTime >= NULL
AND accountTime <= NULL
AND accountState = 1
AND isDelete = 0
AND refundStatus = 5)
) tod, t_order t
WHERE (t.orderChannel = 2
AND tod.orderId = t.id
AND t.isDelete = 0)
GROUP BY tod.accounter, t.payWay)
UNION ALL
(SELECT t.payWay, tod.accounter AS createUser, SUM(tod.actualPrice) AS actualPriceSum
FROM (
SELECT orderId, accounter, actualPrice
FROM t_history_order_detail
WHERE (accountTime >= NULL
AND accountTime <= NULL
AND accountState = 1
AND isDelete = 0
AND refundStatus = 5)
) tod, t_history_order t
WHERE (t.orderChannel = 2
AND tod.orderId = t.id
AND t.isDelete = 0)
GROUP BY tod.accounter, t.payWay)
UNION ALL
(SELECT t.payWay, tod.accounter AS createUser, SUM(tod.actualPrice) AS actualPriceSum
FROM (
SELECT orderId, accounter, actualPrice
FROM t_history_order_detail_sessions
WHERE (accountTime >= NULL
AND accountTime <= NULL
AND accountState = 1
AND isDelete = 0
AND refundStatus = 5)
) tod, t_history_order t
WHERE (t.orderChannel = 2
AND tod.orderId = t.id
AND t.isDelete = 0)
GROUP BY tod.accounter, t.payWay)
UNION ALL
(SELECT t.payWay, tod.accounter AS createUser, SUM(tod.actualPrice) AS actualPriceSum
FROM (
SELECT orderId, accounter, actualPrice
FROM t_history_order_detail_long
WHERE (accountTime >= NULL
AND accountTime <= NULL
AND accountState = 1
AND isDelete = 0
AND refundStatus = 5)
) tod, t_history_order t
WHERE (t.orderChannel = 2
AND tod.orderId = t.id
AND t.isDelete = 0)
GROUP BY tod.accounter, t.payWay)
) tdall
GROUP BY createUser, payWay
) tr
) trr
GROUP BY createUser, payWay, actualPriceSum
) tp
ON ttt.orderUserId = tp.createUser
AND ttt.payWay = tp.payWay)
) tol
) tend, admin_user au
WHERE tend.sellId = au.id
GROUP BY tend.sellId, au.name
2.不说sql性能怎么样,能写出这么长的还没错的sql我真的很佩服
3.这种长sql要怎么优化,怎么分解呢,要吐血,求大神指点啊