SQL 笔记

 --查询某一列在哪个表里

 SELECT  name ,

         object_id ,

         type ,

         type_desc

 FROM    sys.objects

 WHERE   object_id IN ( SELECT   object_id

                        FROM     sys.columns

                        WHERE    name = '列名' )

下面是关于用SQL对日期的一些查询 和统计，主要是在项目中有遇到 按照 日、周、月、年来统计数据，特此记录一下:

1、根据当前日期获取之前一个月的数据

--根据当前日期 往前推一个月

WITH WeekDate AS (

    SELECT CAST(DATEADD(DAY,-31,GETDATE()) AS DATETIME) AS riqi

    UNION ALL

    SELECT riqi + 1 FROM WeekDate WHERE riqi+1<GETDATE()

)

SELECT CONVERT(CHAR(8),a.riqi,112) FROM WeekDate a

2、根据当前日期 获取当前月的每一天的数据

--根据当前日期 获取当前月 每一天

WITH WeekDate AS (

    select   dateadd(d,-day(GETDATE())+1,GETDATE())  AS riqi

    UNION ALL

    SELECT riqi + 1 FROM WeekDate WHERE riqi+1<=(select   dateadd(d,-day(GETDATE()),dateadd(m,1,GETDATE())))

)

SELECT CONVERT(CHAR(8),a.riqi,112) AS 日 FROM WeekDate a

3、根据当前时间获取本周日期

--根据当前时间获取本周日期 每一天

WITH WeekDate AS (

    SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)  AS riqi

    UNION ALL

    SELECT riqi + 1 FROM WeekDate WHERE riqi+1<=(SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 6))

)

SELECT CONVERT(CHAR(8),a.riqi,112) AS 日 FROM WeekDate a

 4、将一串编号显示成一个字段 用逗号分隔 

SELECT  STUFF(( SELECT  ',' + CONVERT(VARCHAR(500), RoleID) FROM SysRole tt FOR XML PATH('') ), 1, 1, '') AS RoleID

SELECT  RoleID FROM dbo.SysRole

效果如下：

 5、其他

--当前时间 获取本周第一天 周一

SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

--当前时间 获取本周最后一天 周日

SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 6)

---当月第一天

select   CONVERT(CHAR(8),dateadd(d,-day(getdate())+1,getdate())  ,112)

---当月最后一天

select   CONVERT(CHAR(8),dateadd(d,-day(getdate()),dateadd(m,1,getdate())),112)

SELECT YEAR(GETDATE())      //2016

SELECT MONTH(GETDATE())  //11

SELECT DAY(GETDATE())       //27

以后再有收集到的再慢慢更