常用sql
select * from P_xinxi order by datediff('h',Ctime,now()) asc)tt
SQL按照日、周、月、年统计数据
表:consume_record
字段:consume (money类型)
date (datetime类型)
请问怎么写四条sql语句分别按日,按周,按月,按季统计消费总量.
如:1月 1200元
2月 3400元
3月 2800元
--按日
select sum(consume),day([date]) from consume_record where year([date]) = '2006' group by day([date])
--按周quarter
select sum(consume),datename(week,[date]) from consume_record where year([date]) = '2006' group by datename(week,[date])
--按月
select sum(consume),month([date]) from consume_record where year([date]) = '2006' group by month([date])
--按季
select sum(consume),datename(quarter,[date]) from consume_record where year([date]) = '2006' group by datename(quarter,[date])
--指定日期你就看上面的例子变通下呀,无非就是一个聚合函数和Group by
select [date],sum(consume) from consume_record where [date] between '2006-06-01' and '2006-07-10' group by [date]
--日期转换参数,值得收藏
select CONVERT(varchar, getdate(), 120 )
2004-09-12 11:06:08
select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')
20040912110608
select CONVERT(varchar(12) , getdate(), 111 )
2004/09/12
select CONVERT(varchar(12) , getdate(), 112 )
20040912
select CONVERT(varchar(12) , getdate(), 102 )
2004.09.12
其它我不常用的日期格式转换方法:
select CONVERT(varchar(12) , getdate(), 101 )
09/12/2004
select CONVERT(varchar(12) , getdate(), 103 )
12/09/2004
select CONVERT(varchar(12) , getdate(), 104 )
12.09.2004
select CONVERT(varchar(12) , getdate(), 105 )
12-09-2004
select CONVERT(varchar(12) , getdate(), 106 )
12 09 2004
select CONVERT(varchar(12) , getdate(), 107 )
09 12, 2004
select CONVERT(varchar(12) , getdate(), 108 )
11:06:08
select CONVERT(varchar(12) , getdate(), 109 )
09 12 2004 1
select CONVERT(varchar(12) , getdate(), 110 )
09-12-2004
select CONVERT(varchar(12) , getdate(), 113 )
12 09 2004 1
select CONVERT(varchar(12) , getdate(), 114 )
11:06:08.177
CONVERT(CHAR(8), GETDATE(), 112) -->转为 yyyyMMdd格式的字符串
其它
Select
CONVERT(varchar, getdate(), 1),--mm/dd/yy
CONVERT(varchar, getdate(), 2),--yy.mm.dd
CONVERT(varchar, getdate(), 3),--dd/mm/yy
CONVERT(varchar, getdate(), 4),--dd.mm.yy
CONVERT(varchar, getdate(), 5),--dd-mm-yy
CONVERT(varchar, getdate(), 10),--mm-dd-yy
CONVERT(varchar, getdate(), 11),--yy/mm/dd
CONVERT(varchar, getdate(), 12),--yymmdd
----带世纪号
CONVERT(varchar, getdate(), 101),--mm/dd/yyyy
CONVERT(varchar, getdate(), 102),--yyyy.mm.dd
CONVERT(varchar, getdate(), 103),--dd/mm/yyyy
CONVERT(varchar, getdate(), 104),--dd.mm.yyyy
CONVERT(varchar, getdate(), 105),--dd-mm-yyyy
CONVERT(varchar, getdate(), 110),--mm-dd-yyyy
CONVERT(varchar, getdate(), 111),--yyyy/mm/dd
CONVERT(varchar, getdate(), 112)--yyyymmdd
GO
Ip查询存储过程
CREATE PROCEDURE [UP_Sel_UZ_IPAddress]
(
@IP NVARCHAR(20)
)
AS
DECLARE @ReturnValue bigint
DECLARE @tip nvarchar(20)
DECLARE @sip1 bigint
DECLARE @sip2 bigint
DECLARE @sip3 bigint
DECLARE @sip4 bigint
DECLARE @in int
SET @tip=@IP
SET @in=charindex('.',@tip)
SET @sip1=substring(@tip,1,(@in-1))
SET @tip=substring(@tip,(@in+1),(LEN(@tip) - @in))
SET @in=charindex('.',@tip)
SET @sip2=substring(@tip,1,(@in-1))
SET @tip=substring(@tip,(@in+1),(LEN(@tip) - @in))
SET @in=charindex('.',@tip)
SET @sip3=substring(@tip,1,(@in-1))
SET @sip4=substring(@tip,(@in+1),3)
SET @ReturnValue=@sip1*256*256*256 + @sip2*256*256 + @sip3*256 +@sip4
SELECT * FROM BBSGOOD_IP WHERE @ReturnValue>=IP1 AND @ReturnValue<=IP2
如:exec UP_Sel_UZ_IPAddress '127.0.0.1'