sum(ifnull(tax_price)*ifnull(num),1) quaneMoney
时间: 2024-05-13 16:18:56 浏览: 10
这段代码似乎有语法错误。可以参考以下代码:
```
SELECT SUM(IFNULL(tax_price, 0) * IFNULL(num, 0)) AS quaneMoney FROM your_table_name;
```
这段代码的作用是计算 `tax_price` 与 `num` 的乘积之和,并将结果命名为 `quaneMoney`。其中,`IFNULL` 函数用于处理 `tax_price` 或 `num` 为空值的情况,避免出现乘积时出现错误。
相关问题
sum(ifnull(tax_price*ifnull(num,1),0))jiesuanMoneyRmb
这是一个SQL语句中的表达式,作用是求解税后价格和数量的乘积之和。具体来说,ifnull函数用于判断参数是否为NULL,如果是则返回第二个参数指定的默认值,否则返回原值。在这个表达式中,ifnull(tax_price*ifnull(num,1),0)会先将num值转换为1(如果num为NULL则转换为1),然后计算税后价格和数量的乘积,最后将所有乘积求和得到jiesuanMoneyRmb。
优化这段:SQL:SELECT CONCAT( HOUR ( mail_send_time ) DIV 1 * 1, ':00-', HOUR ( mail_send_time ) DIV 1 * 1, ':59' ) AS timeInterval, IFNULL( COUNT( * ), 0 ) AS total, IFNULL( SUM( contact_account_type = '微信' ), 0 ) AS wxTotal, IFNULL( SUM( contact_account_type = 'QQ' ), 0 ) AS qqTotal, IFNULL( SUM( local_action = '发送' AND contact_account_type = '微信' ), 0 ) AS wxSend, IFNULL( SUM( local_action = '接收' AND contact_account_type = '微信'), 0 ) AS wxReceive , IFNULL( SUM( local_action = '发送' AND contact_account_type = 'QQ'), 0 ) AS qqSend, IFNULL( SUM( local_action = '接收' AND contact_account_type = 'QQ'), 0 ) AS qqReceive FROM dwd_t_friend_talk WHERE person_no = '653129200105072624' AND contact_account_type IN ( '微信', 'QQ' ) AND friend_account <> '' AND contact_account_type <> '' AND mail_send_time IS NOT NULL AND mail_send_time <> '' GROUP BY HOUR ( mail_send_time ) DIV 1;其中的contact_account_type和local_action不用汉子,用码值代替,现在有一张dwd_t_code_value表存储码值和中文描述,改造这条SQL,将汉字替换为码值
优化后的SQL语句如下:
```
SELECT
CONCAT(HOUR(mail_send_time) DIV 1 * 1, ':00-', HOUR(mail_send_time) DIV 1 * 1, ':59') AS timeInterval,
IFNULL(COUNT(*), 0) AS total,
IFNULL(SUM(contact_account_type = (SELECT code_value FROM dwd_t_code_value WHERE code_type='contact_type' AND name='微信')), 0) AS wxTotal,
IFNULL(SUM(contact_account_type = (SELECT code_value FROM dwd_t_code_value WHERE code_type='contact_type' AND name='QQ')), 0) AS qqTotal,
IFNULL(SUM(local_action = (SELECT code_value FROM dwd_t_code_value WHERE code_type='local_action' AND name='发送') AND contact_account_type = (SELECT code_value FROM dwd_t_code_value WHERE code_type='contact_type' AND name='微信')), 0) AS wxSend,
IFNULL(SUM(local_action = (SELECT code_value FROM dwd_t_code_value WHERE code_type='local_action' AND name='接收') AND contact_account_type = (SELECT code_value FROM dwd_t_code_value WHERE code_type='contact_type' AND name='微信')), 0) AS wxReceive,
IFNULL(SUM(local_action = (SELECT code_value FROM dwd_t_code_value WHERE code_type='local_action' AND name='发送') AND contact_account_type = (SELECT code_value FROM dwd_t_code_value WHERE code_type='contact_type' AND name='QQ')), 0) AS qqSend,
IFNULL(SUM(local_action = (SELECT code_value FROM dwd_t_code_value WHERE code_type='local_action' AND name='接收') AND contact_account_type = (SELECT code_value FROM dwd_t_code_value WHERE code_type='contact_type' AND name='QQ')), 0) AS qqReceive
FROM
dwd_t_friend_talk
WHERE
person_no = '653129200105072624'
AND contact_account_type IN (SELECT code_value FROM dwd_t_code_value WHERE code_type='contact_type' AND name IN ('微信', 'QQ'))
AND friend_account <> ''
AND contact_account_type <> ''
AND mail_send_time IS NOT NULL
AND mail_send_time <> ''
GROUP BY
HOUR(mail_send_time) DIV 1;
```
改造后的SQL语句使用了子查询,通过dwd_t_code_value表获取contact_account_type和local_action字段的码值,从而将原本的汉字替换为了码值。