SQL数据库汇总和日期

    技术2022-05-12  39

    --========================================--类型转换函数cast和convert--========================================--定义变量declare @a int--赋值set @a = 9--类型转换print cast(@a as decimal(10,5))

    ---declare @d datetimeset @d = '2008-3-3'select cast(@d as varchar(20))select convert(varchar(20),@d,20)

    --字符串函数 print ascii('a')print char(97)

    select lower(au_lname + ' . '+ au_fname) from dbo.authorsselect upper(au_lname + ' . '+ au_fname) from dbo.authors

    select right('abcde',3)

    select charindex('abc','qqabcde')

    select reverse('abcdef')

    select len('abcdef')

    --日期函数select getdate()

    select  year('2008-3-3')select  month('2008-3-3')select  day('2008-3-3')

    select datediff(day,'2008-3-3','2008-5-1')

    --select datepart(dw,'2008-3-3')select datename(dw,'2008-3-3')

    --例:按图书类别分组统计图书的平均价格select type,price from titles

    select type,avg(price) from titlesgroup by type

    --where 和 havingselect type,avg(price) from titleswhere price >19group by type

    select type,avg(price) from titlesgroup by typehaving avg(price) >19

    --汇总--例:统计出所有书的总价格和总的预付款项select sum(price) ,sum(advance) from titles--明细+汇总select title,price ,advance from titlescompute sum(price) ,sum(advance)--分类汇总select type,price ,advance from titlesorder by typecompute sum(price) ,sum(advance) by type

     

     

    ---------------------------------------------------------------------------------------------

     


    最新回复(0)