sql

合并不同行数的两个表格的数（A表里的一个值a，与另B表的行数同步，即新表行数与B一样，由a+B表查出的各行组成）：

以下三种效果一样，第三种效果最佳：

1.select sV,PD,Sh,Pro
from
(Select sV From Sy where sN='Cur' ) t1,
(Select PD,Sh,Pro From P ) t2

2.select * from (Select  sV From Sywhere sN = 'Cur' )  left join    (Select PD, Sh, Pro From   P) on 1 = 1

3.select sV,PD,Sh,Pro From Sy    left join P on 1=1  where sN = 'Cur'

 

4.利用存储过程据一条记录插入不同记录的数据

 1 create procedure upAddRecord
 2 as
 3 declare @t int
 4 set @t=1
 5 
 6 while (@t<=100)
 7 begin
 8 insert into [DATABaseName].[dbo].[table_name] ([column1], [column2],[column3],[column4],[column5],[column6],[column7],[column8],[column9],[column10]) 
 9 VALUES ('testB'+convert(varchar(20),@t), 'testB'+convert(varchar(20),@t),GETDATE()-@t,'01','wu','test','test2','test3',1,GETDATE()) 
10 set @t=@t+1
11 end
12 go
13 DROP PROCEDURE upAddRecord
14 select * from table_name where [column6]='test' order by [column3] desc

 5.查看是否存在相同数据的列的sql

1 Select 列名,COUNT(列名)
2  FROM  表名
3  GROUP BY 列名
4  HAVING COUNT(列名) >1

 6.在做Sql Server开发的时候有时需要获取表中今天、昨天、本周、上周、本月、上月等数据，这时候就需要使用DATEDIFF()函数及GetDate()函数了。

 1 DATEDIFF ( datepart , startdate , enddate )
 2 释义：计算时间差
 3 datepare值：year | quarter | month | week | day | hour | minute | second | millisecond
 4 startdate：开始日期
 5 enddate ：结束日期
 6 GetDate()
 7 释义：获取当前的系统日期
 8 
 9 下面例子中表名为tablename,条件字段名为inputdate
10 查询今天
11 
12 SELECT * FROM tablename where DATEDIFF(day,inputdate,GETDATE())=0
13 查询昨天
14 
15 SELECT * FROM tablename where DATEDIFF(day,inputdate,GETDATE())=1
16 查询本周
17 
18 SELECT * FROM tablename where  datediff(week,inputdate,getdate())=0
19 查询上周
20 
21 SELECT * FROM tablename where  datediff(week,inputdate,getdate())=1
22 查询本月
23 
24 SELECT * FROM tablename where DATEDIFF(month,inputdate,GETDATE())=0
25 查询上月
26 
27 SELECT * FROM tablename where DATEDIFF(month,inputdate,GETDATE())=1

 按小时分段统计

1 DECLARE @startTime datetime = '2017-09-01 12:13:00'
2 DECLARE @split INT=300 --分段统计（秒）
3 SELECT COUNT(*) AS 数量,DATEADD(s,DATEDIFF(s, '1970-01-01 00:00:00', [Time])/@split*@split, '1970-01-01 00:00:00') AS Time 
4 FROM DateUpload 
5 WHERE Time >= @startTime 
6 GROUP BY DATEDIFF(s, '1970-01-01 00:00:00', [TGSJ])/@split
7 ORDER BY 数量 desc;

选择后面30秒的数据

1 1 SELECT TOP (10) *  FROM DateUpload 
2 　　　　　　WHERE (IsUpLoad = 0 and id is not null and Time<DATEADD(SS,-30,GETDATE()) and Type in (0,1)) ORDER BY Time DESC

 

参考：http://www.cnblogs.com/mayi1/p/5946536.html

posted @ 2017-02-22 16:07  wx_h13813744  阅读(165)  评论(3)  编辑  收藏  举报