SQL 笔记

Posted on | Post modified: | Visitors: 
1
2
3
4
5
6
7
8
9
--查询某一列在哪个表里
SELECT  name ,
        object_id ,
        type ,
        type_desc
FROM    sys.objects
WHERE   object_id IN ( SELECT   object_id
                       FROM     sys.columns
                       WHERE    name = '列名' )

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

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

1
2
3
4
5
6
7
--根据当前日期 往前推一个月
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、根据当前日期 获取当前月的每一天的数据

1
2
3
4
5
6
7
--根据当前日期 获取当前月 每一天
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、根据当前时间获取本周日期

1
2
3
4
5
6
7
--根据当前时间获取本周日期 每一天
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、将一串编号显示成一个字段 用逗号分隔

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

SELECT  RoleID FROM dbo.SysRole

效果如下：

效果图

5、其他

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--当前时间 获取本周第一天 周一
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

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