• 最近用的几个sql语句


    都在sqlserver数据库下操作,注意sqlserver与mysql和oracle的语法区别

    用惯了mysql 和oracle,突然改用sqlserver,有诸多的不习惯,诸多的坑爹,好多的坑,一一跳过一遍才知道,头破血流呀!​

    1,建表

    create table stuUnion

    (

     sid int identity primary key,

     cid int,

     id varchar(500)

    )

    2 插入数据​

    insert into stuUnion

    select 1,'a' union

    select 1,'b' union

    select 2,'c' union

    select 2,'d' union

    select 3,'e' union

    select 3,'f' union

    select 3,'g'

    3 标量函数(合并不同记录的相同字段)​

    create function b(@cid int)

    returns varchar(500)

    as

    begin

    declare @s varchar(500)

    select @s=isnull(@s+'','')+rtrim(id)+',' from stuUnion where cid=@cid

    return @s

    end;

    4 显示结果​

    select cid,dbo.b(cid) as id from stuUnion group by cid

    5改造标量函数

    注意点1 参数类型,int 和 String​

              2 ​  where VID like '%'+@VID+'%'

    create function c(@VID nvarchar)

    returns varchar(500)

    as

    begin

    declare @s varchar(500)

    select @s=isnull(@s+'','')+rtrim(items)+' ' from cus_checks where VID like '%'+@VID+'%'

    return @s

    end;

    select VID,dbo.c(VID) as 检查项目 from cus_checks group by VID

    6修改字段​

    sp_rename 'cus_checks.check','items','column'

    7不用标量函数进行查询取值 ​

    select VID,items=STUFF((select DISTINCT ' '+rtrim(items)+' ' from cus_checks where st.VID=VID for XML path('')),1,1,'')  from cus_checks st group by VID

    8 对表数据进行去重处理​

    slect * insert into abc from select DISTINCT VID,Items from cus_checks

    9复制去重后的表​

    select * into aaa from cus_checks where 1=2

    insert into aaa select DISTINCT * from cus_checks

  • 相关阅读:
    git 账号密码
    sql server 备份
    计算经纬度的两点之间的距离
    redis 安装
    webapi 可空参数
    Asp.Net MVC4 使用Unity 实现依赖注入
    sublime主题推荐
    分解质数因子
    如何在sublime+chrome中调试php代码?
    php的mysql语句里变量加不加单引号问题
  • 原文地址:https://www.cnblogs.com/zhaoblog/p/5391952.html
Copyright © 2020-2023  润新知