代码人生的小狗窝

一行行枯燥的代码,却描绘出人生的点点滴滴

您现在的位置是:首页>_C#

小弟我有一个SQL语句有关问题?查询的

发布时间:2019-09-20浏览(1168)

    我有一个SQL语句问题?查询的
    如下表结果 

    id name status
    1 xxxx 1
    2 yyyy 2
    3 zzzz 3
    4 ffff 1
    5 erer 1
    6 gffg 3
    7 xdfd 1
    8 fdvd 1
    9 ffff 2
    10 rttr 1

    我想要这样的结果 

    Sum S1 S2 S3
    10 6 2 2


    ------解决方案--------------------
    select a.k1, b.k2,c.k3
    from 
    (select count(id) as k1 from test where TS = "1") a,
    (select count(id) as k2 from test where TS ="2") b,
    (select count(id) as k3 from test where TS ="2") c;

    个人感觉这样的写法很不好。不知道有没有更优雅的办法。
    ------解决方案--------------------
    写了半小时,没有写出来,要自动判断status数量,有要sum ,简单的结果,不会写
    顶一下
    ------解决方案--------------------
    方法1,固定S1,S2,S3个数

    SQL code
    
    create table #t (id int identity,name varchar(10),status int)
    
    insert into #t(name,status)values('xxxx',1)
    insert into #t(name,status)values('yyyy',2)
    insert into #t(name,status)values('zzzz',3)
    insert into #t(name,status)values('ffff',1)
    insert into #t(name,status)values('erer',1)
    insert into #t(name,status)values('gffg',3)
    insert into #t(name,status)values('xdfd',1)
    insert into #t(name,status)values('fdvd',1)
    insert into #t(name,status)values('ffff',2)
    insert into #t(name,status)values('rttr',1)
    
    select count(id) as 'SUM'
    , sum(case status when 1 then 1 else 0 end) as 'S1'
    , sum(case status when 2 then 1 else 0 end) as 'S2'
    , sum(case status when 3 then 1 else 0 end) as 'S3'
    from #t
    
    ------解决方案--------------------
    探讨
    方法2 更标准的做法


    SQL codedeclare @sql nvarchar(4000)
    select @sql='select count(id) as ''SUM'''
    select @sql = @sql+
    ', sum(case status when '''+ Convert(varchar(2), status) +''' then 1 else 0 end) [S'+ Convert(varchar(2), status) +']'
    from (select distinct status from #t) a
    select @sql = @sql + ' from #t'
    exec sp_executesql @sql

    ------解决方案--------------------
    SQL code
    create table #t (id int identity,name varchar(10),status int)
    
    insert into #t(name,status)values('xxxx',1)
    insert into #t(name,status)values('yyyy',2)
    insert into #t(name,status)values('zzzz',3)
    insert into #t(name,status)values('ffff',1)
    insert into #t(name,status)values('erer',1)
    insert into #t(name,status)values('gffg',3)
    insert into #t(name,status)values('xdfd',1)
    insert into #t(name,status)values('fdvd',1)
    insert into #t(name,status)values('ffff',2)
    insert into #t(name,status)values('rttr',1)
    --静态
    select count(1) as '总数',
    sum(case when status=1 then 1 else 0 end) as s1,
    sum(case when status=2 then 1 else 0 end) as s2,
    sum(case when status=3 then 1 else 0 end) as s3
    from #t
    --动态
    declare @sql varchar(8000)
    select @sql=isnull(@sql+',','')+'sum(case when status='''+ltrim(status)+''' then 1 else 0 end) as [s'+ltrim(status)+']'
    from (select distinct status from #t)tp
    exec('select count(1) as ''总数'','+@sql+' from #t')