SELECT
a.tID as saleTime,
RIGHT(
CONVERT(VARCHAR (13), B.operDate, 121),
2
) as aaa,
SUM(
CASE
WHEN B.sellWay = "A"
THEN B.saleAmt
WHEN B.sellWay = "B"
THEN 0- B.saleAmt
ELSE 0
END
) AS sellAmt,
COUNT(
DISTINCT
CASE
WHEN (
B.sellway = "A"
AND isnull(B.RtnFlowNo, "") = ""
)
THEN B.flowno
ELSE NULL
END
) AS sellTime,
(
CASE
WHEN COUNT(
DISTINCT
CASE
WHEN (
B.sellway = "A"
AND isnull(B.RtnFlowNo, "") = ""
)
THEN B.flowno
ELSE NULL
END
) = 0
THEN 0
ELSE SUM(
CASE
WHEN B.sellWay = "A"
THEN B.saleAmt
WHEN B.sellWay = "B"
THEN 0- B.saleAmt
ELSE 0
END
) / COUNT(
DISTINCT
CASE
WHEN (
B.sellway = "A"
AND isnull(B.RtnFlowNo, "") = ""
)
THEN B.flowno
ELSE NULL
END
)
END
) AS avAmt,
SUM(
CASE
WHEN B.sellWay = "A"
THEN B.custNumber
WHEN B.sellWay = "B"
THEN - B.custNumber
ELSE 0
END
) AS custNumber,
(
CASE
WHEN SUM(
CASE
WHEN B.sellWay = "A"
THEN B.custNumber
WHEN B.sellWay = "B"
THEN - B.custNumber
ELSE 0
END
) = 0
THEN 0
ELSE SUM(
CASE
WHEN B.sellWay = "A"
THEN B.saleAmt
WHEN B.sellWay = "B"
THEN 0- B.saleAmt
ELSE 0
END
) / SUM(
CASE
WHEN B.sellWay = "A"
THEN B.custNumber
WHEN B.sellWay = "B"
THEN - B.custNumber
ELSE 0
END
)
END
) AS avCustAmt
FROM
ts_time A
LEFT JOIN pos_tranpay B
ON --CONVERT(DATETIME, B.operDate)
B.operDate BETWEEN "2016-09-20 0:0:0.000"
AND "2016-10-09 23:59:59.900"
AND B.WarehouseID = "00"
AND A.tID = RIGHT(
CONVERT(VARCHAR (13), B.operDate, 121),
2
)
GROUP BY A.tID,B.operDate
ORDER BY A.tID
结果:
也就是说把时间戳的类型转换为了varchar,假如语句复制到mysql,
这段语句应该怎么样修改?