sql-我写的或者其他人写的

1.更新数据，补上分工

UPDATE smartoperator RIGHT JOIN smartoperation ON smartoperation.ID=smartoperator.OperationID
SET PositionID =2
-- 表smartposition 
where PositionID is null
and UserID =124
-- 表smartuser
and CreateTime>="2019-03-01 00:00:00" and CreateTime<="2019-04-01 00:00:00" 

 

2.两种方法查看渠道分销顾客消费实缴

SELECT sum(cash+card) as "交钱即返的消费",sum(cash+card+Deposit) as "消费即返的消费",sum(Cash),sum(card),sum(deposit),sum(Coupon) FROM `smartcashier`

JOIN smartdistributioncustomer on smartcashier.CustomerID=smartdistributioncustomer.CustomerID

WHERE  smartcashier.CreateTime BETWEEN "2019-02-28 00:00:00" AND "2019-03-01 00:00:00"

SELECT sum(commission) as "顾客消费实缴" FROM `smartdistributioncommission`
where CreateTime BETWEEN "2019-02-28 00:00:00" AND  "2019-02-28 23:59:59" AND
type=2 AND `Level`=1

 

3.两种写法查看升级花费（第二种是开发写的）

SELECT sum(commission) as "升级花费",sum(amount) as "升级佣金" from smartdistributioncommission
where CreateTime between "2019-02-28 00:00:00" AND "2019-02-28 23:59:59" AND Type=1

SELECT
    date_format(CreateTime, '%Y-%m-%d') AS CreateTime,
    Commission
FROM
    smartdistributioncommission
WHERE
    1 = 1
AND CreateTime >= '2019-02-28'
AND CreateTime < '2019-03-01'
AND Type = 1
ORDER BY
    CreateTime ASC

 

 

---

 

-- 结果：查看最新的5个收费项目的信息
SELECT * FROM
(
SELECT charge.ID,charge.Name,charge.Remark,charge.IsDeal,charge.Mode,charge.PinYin,charge.Size,charge.Code,charge.PrintName,charge.ProductAdd,charge.ProductType,

    CASE 
        WHEN rc.Price IS NOT NULL 
            THEN rc.Price 
        ELSE charge.Price END 
    AS Price,

    CASE 
        WHEN rc.`Status` IS NOT NULL 
            THEN rc.`Status` 
        WHEN product.RetailStatus IS NOT NULL 
            THEN product.RetailStatus 
        WHEN product.RetailStatus IS NULL AND product.RetailStatus=1 AND rc2.`Status` IS NOT NULL 
            THEN rc2.`Status` 
        ELSE charge.`Status` END 
    AS Status,

    unit.Name AS UnitName, category.Name AS CategoryName 

FROM SmartCharge AS charge

    LEFT JOIN SmartProduct AS product ON charge.ID = product.ChargeID 
    INNER JOIN SmartUnit AS unit ON charge.UnitID = unit.ID 
    INNER JOIN SmartChargeCategory AS category ON charge.CategoryID = category.ID 
    LEFT JOIN SmartRetailConfig AS rc ON rc.ChargeID = charge.ID  AND rc.HospitalID = 2 
    LEFT JOIN SmartRetailConfig AS rc2 ON rc2.ProductID = product.ID  AND rc2.HospitalID = 2
)
AS result 

WHERE TRUE AND ((result.ProductType = 1 AND result.Status=1) OR result.ProductType = 0)  AND result.Status = 1 

ORDER BY result.Status DESC,result.ID DESC
LIMIT 0,5

 

转载于:https://www.cnblogs.com/margot921/p/10418040.html