SQL 按月统计

SQL 按月统计,DISTINCT去重 日统计

SELECT SUM(psar_bank_pos_recon_money) AS total_amount,DATENAME(YEAR,psar_data) AS year, DATENAME(MONTH,psar_data) AS month 
       FROM PAY_SYSTEM_ACCOUNT_RECONCILIATION WHERE psar_data>=@begin_month AND psar_data<@end_month
       GROUP BY DATENAME(YEAR,psar_data),DATENAME(MONTH,psar_data) ORDER BY DATENAME(YEAR,psar_data) ASC,DATENAME(MONTH,psar_data) ASC

注:DATENAME(YEAR,psar_data) 获取指定字段的年份,还有其他可选参数

DISTINCT去重 日统计

SELECT COUNT(*) AS count, COUNT(DISTINCT lci_erector_openid) AS lci_ere_count,
    COUNT(DISTINCT tc_cu_shop_id) AS shop_count,
    DATEPART(YEAR,lci_erector_code_scantime) AS lciYear,
    DATEPART(MONTH,lci_erector_code_scantime) AS lciMonth,
    DATEPART(DAY,lci_erector_code_scantime) AS lciDay
    FROM dbo.LOTTERY_CODE_INFO INNER JOIN dbo.TC_CUSTOMERS ON lci_erector_openid=tc_cu_weixin_open_id
    WHERE lci_erector_code_scantime>=@begin_date AND lci_erector_code_scantime<@end_date
    AND (tc_cu_address LIKE '%'+@address+'%' OR @address='全国')
    AND lci_type in (40,140)
    GROUP BY DATEPART(YEAR,lci_erector_code_scantime),
    DATEPART(MONTH,lci_erector_code_scantime),DATEPART(DAY,lci_erector_code_scantime)
    ORDER BY DATEPART(YEAR,lci_erector_code_scantime),
    DATEPART(MONTH,lci_erector_code_scantime),DATEPART(DAY,lci_erector_code_scantime)

 关键点:  

COUNT(DISTINCT lci_erector_openid)