原始数据表名:
FILL_TIME | 充值时间 |
---|---|
ACCOUNT_ID | 账号ID |
CARD_TYPE | 充值卡面值类型 |
FILL_TYPE | 充值类型 |
CARD_AMOUNT | 充值卡个数 |
【SQL题目】请按照以下要求写出SQL代码
1、获得每日充值的账号数,及充值金额,要求对账号排重。
代码:
SELECT DATE(FILL_TIME),COUNT(DISTINCT ACCOUNT_ID,FILL_TIME)AS quantity,
SUM(CARD_TYPE*CARD_AMOUNT) AS amount
FROM text_1
GROUP BY DAY(FILL_TIME);
查询结果(部分):
2、获得每日各充值类型的总充值金额
代码:
SELECT DATE(fill_time),CARD_TYPE,SUM(CARD_TYPE*CARD_AMOUNT) AS AMOUNT
FROM text_1
GROUP BY DAY(fill_time),CARD_TYPE;
查询结果(部分):