格式：IF(Condition,A,B)
意义：当Condition为TRUE时，返回A；当Condition为FALSE时，返回B。
作用：作为条件语句使用。
例子
SELECT
d.item_variation_sku AS SKU,
SUM(
IF(
d.add_time BETWEEN '2017-03-01' AND '2017-03-04',
d.item_amount,
0
)
) AS sku_count,
SUM(
IF(
d.add_time BETWEEN '2017-02-27' AND '2017-03-01',
d.item_amount,
0
)
) AS compare_sku_count,
ROUND(
SUM(
IF(
d.add_time BETWEEN '2017-03-01' AND '2017-03-04',
d.item_amount * d.item_price_after_discount / d.local_rite,
0
)
),
2
) AS Money,
ROUND(
SUM(
IF(
d.add_time BETWEEN '2017-02-27' AND '2017-03-01',
d.item_amount * d.item_price_after_discount / d.local_rite,
0
)
),
2
) AS Compare_Money,
SUM(
IF(
d.add_time BETWEEN '2017-03-01' AND '2017-03-04',
d.item_amount,
0
)
) - SUM(
IF(
d.add_time BETWEEN '2017-02-27' AND '2017-03-01',
d.item_amount,
0
)
) AS diff_sku_count,
ROUND(
SUM(
IF(
d.add_time BETWEEN '2017-03-01' AND '2017-03-04',
d.item_amount * d.item_price_after_discount / d.local_rite,
0
)
),
2
) - ROUND(
SUM(
IF(
d.add_time BETWEEN '2017-02-27' AND '2017-03-01',
d.item_amount * d.item_price_after_discount / d.local_rite,
0
)
),
2
) AS diff_money
FROM
(
SELECT
id,
order_table_id,
`item_variation_sku`,
item_amount,
item_price_after_discount,
`local_rite`,
`add_time`
FROM
orders_detail1702
UNION ALL
SELECT
id,
order_table_id,
`item_variation_sku`,
item_amount,
item_price_after_discount,
`local_rite`,
`add_time`
FROM
orders_detail1703
) AS d,
(
SELECT
id,
account_id
FROM
orders_table1702
UNION ALL
SELECT
id,
account_id
FROM
orders_table1703
) AS t,
shopee_admin.accounts AS a
WHERE
d.order_table_id = t.id AND t.account_id = a.id AND d.add_time BETWEEN '2017-02-27' AND '2017-03-04'
GROUP BY
d.item_variation_sku
网友评论