SQL简单的日报和月报

--320, SQL SERVER  日报
--查询2009-01-01当天客户A1,A2,A3的订单数量
SELECT Cust_Name
     , CONVERT(CHAR(10), Order_Date, 120) Order_Date
     , SUM(Qty) Qty
FROM Orders_Big
WHERE 1=1
     AND Cust_Name IN ('A1', 'A2', 'A3')
     AND Order_Date >= '2009-01-01' 
     AND Order_Date<'2009-01-02'
     /*AND Order_Date  -- BETWEEN...AND相当于>=和<=
            BETWEEN '2009-01-01'
                 AND '2009-01-02'*/
GROUP BY Cust_Name
     , CONVERT(CHAR(10), Order_Date, 120)
ORDER BY 2, 1


--321. ORACLE 日报
--查询2009-01-01当天客户A1,A2,A3的订单数量
SELECT Cust_Name
  , TO_CHAR(Order_Date, 'YYYY-MM-DD') Order_Date --方法一:转换成字符串类型
  --, TRUNC(Order_Date) Order_Date  --方法二:将时分秒信息截断
  , SUM(Qty) Qty
FROM Orders_Big
WHERE 1=1
     AND Cust_Name IN ('A1', 'A2', 'A3')
     AND Order_Date >= DATE'2009-01-01' 
     AND Order_Date<DATE'2009-01-02'
GROUP BY Cust_Name
       , TO_CHAR(Order_Date, 'YYYY-MM-DD')--方法一
       --, TRUNC(Order_Date)--方法二
ORDER BY 2, 1


------------------------------10.2------------------------------------
--326,SQL SERVER 月报
SELECT Cust_Name
     , CONVERT(CHAR(7), Order_Date, 120) Order_YrMs
     , CAST(CONVERT(CHAR(7), Order_Date, 120) + '-01' AS DATETIME) Order_Date
     , SUM(Qty) Qty
FROM Orders_Big
WHERE 1=1
   AND Cust_Name IN ('A1', 'A2', 'A3')
   AND Order_Date >= '2009-01-01' 
   AND Order_Date<'2009-02-01'
GROUP BY Cust_Name
        , CONVERT(CHAR(7), Order_Date, 120)
ORDER BY 2, 1

--326, ORACLE
SELECT Cust_Name
    , TO_CHAR(Order_Date, 'YYYY-MM') Order_YrMs --方法1
    --, TRUNC(Order_Date, 'MM') Order_Date--方法2
    , SUM(Qty) Qty
FROM Orders_Big
WHERE 1=1
    AND Cust_Name IN ('A1', 'A2', 'A3')
    AND Order_Date >= DATE'2009-01-01' 
    AND Order_Date<DATE'2009-02-01'
GROUP BY Cust_Name
    , TO_CHAR(Order_Date, 'YYYY-MM')--方法1
    --, TRUNC(Order_Date, 'MM')--方法2
ORDER BY 2, 1

转载于:https://www.cnblogs.com/smartsmile/p/6234270.html