mysql随手记

-- ROUND截取小数点2位

ROUND(SUM(pay_mony),2) 

-- IFNULL判断是否为空，并替换为空数据

IFNULL(totalmoney,0)

-- date_format(date ,'%Y-%m-%d' )

 

SELECT
asset_state `name`,
COUNT(1) `value`,
ROUND(SUM(use_area),2) data2
FROM
sta_asset_management
WHERE
asset_state IS NOT NULL
AND asset_state != ''
GROUP BY
asset_state
ORDER BY
`value` DESC;

 

 

SELECT
ROUND(SUM(contract_price), 2) zje,
ROUND(SUM(total_amount_paid), 2) yzfje,
ROUND(SUM(contract_price) - SUM(total_amount_paid), 2) wzfje,
ROUND(SUM(amount_project_change), 2) bgje
FROM
bus_rural_small_projects
WHERE
township_id = '9';

 

SELECT 'zje' payStatus, ROUND(SUM(contract_price), 2) payMoney FROM bus_rural_small_projects
WHERE township_id = '9'
UNION ALL
SELECT 'yzfje' payStatus, ROUND(SUM(total_amount_paid), 2) payMoney FROM bus_rural_small_projects
WHERE township_id = '9'
UNION ALL
SELECT 'wzfje' payStatus, ROUND(SUM(contract_price) - SUM(total_amount_paid), 2) payMoney FROM bus_rural_small_projects
WHERE township_id = '9'
UNION ALL
SELECT 'bgje' payStatus, ROUND(SUM(amount_project_change), 2) payMoney FROM bus_rural_small_projects
WHERE township_id = '9'