关于对字段不同类型求和的问题

2009-09-11 06:05:42 332 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

避坑指南：为什么你的conda环境装不上Mamba？从CUDA到ABI的深度排查本文深入解析conda环境中安装Mamba架构时常见的CUDA版本冲突、ABI不匹配等问题，提供从基础检查到深度排查的系统化解决方案。针对`causal-conv1d`和`mamba-ssm`安装失败场景，详细讲解PyTorch版本绑定、编译器工具链兼容性等关键技术要点，并给出预编译wheel选择、动态链接库配置等实用修复方案。阅读详情

有一张表，类似如下：
id name type date
1 aaa 01 2009-9-2
2 bbb 01 2009-9-3
3 aaa 01 2009-9-3
4 aaa 02 2009-9-3
5 aaa 02 2009-9-3

我如何根据日期查出:
name count(type1) count(type2)
aaa 2 2
bbb 1 0

自己写的语句比较繁琐，求比较简洁的写法，多谢~！

...全文

计算机网络自顶向下（第八版）第一章课后习题电路交换网络具有以下优点：资源独占性，在建立连接时，电路交换网络为通信双方独占一条物理路径，不会受到其他用户的影响。低时延，由于建立了专用路径，数据传输时延较小，适合对时延敏感的应用，如实时语音和视频通信。数据链路层：提供节点之间可靠的数据传输，主要任务是错误检测和纠正，以及数据帧的传输和访问控制。移动蜂窝网络：如4G LTE和5G，通过移动基站提供的无线信号实现移动设备的互联网接入。网络层：负责数据包的路由和转发，主要任务是通过网络选择最佳路径，进行流量控制和拥塞控制。手机，服务器，路由器，电脑；阅读详情

ivws_19

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

qiqi860819 2009-09-11

打赏
举报

想问下sum(1)或sum(0),是什么意思？是计算的列索引么？不明白，还请问大家下

cxmcxm 2009-09-11

打赏
举报

select name,sum(case when type='01' then 1 else 0 end) type1,sum(case when type='02' then 1 else 0 end) type2

from 表 

where date between '2009-9-2' and '2009-9-3'

group by name

xuejiecn 2009-09-11

打赏
举报



select name,sum(case when [type]='01' then 1 else 0 end),sum(case when [type]='02' then 1 else 0 end)

from TB group by name

华夏小卒 2009-09-11

打赏
举报



select name,sum(case when [type]='01' then 1 else 0 end),sum(case when [type]='02' then 1 else 0 end)

from TB group by name

--小F-- 2009-09-11

打赏
举报

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

-- Author  :fredrickhu(小F，向高手学习)

-- Date    :2009-09-11 20:19:22

-- Version:

--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 

--	Nov 24 2008 13:01:59 

--	Copyright (c) 1988-2005 Microsoft Corporation

--	Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

--

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

--> 测试数据：[tb]

if object_id('[tb]') is not null drop table [tb]

go 

create table [tb]([id] int,[name] varchar(3),[type] varchar(2),[date] datetime)

insert [tb]

select 1,'aaa','01','2009-9-2' union all

select 2,'bbb','01','2009-9-3' union all

select 3,'aaa','01','2009-9-3' union all

select 4,'aaa','02','2009-9-3' union all

select 5,'aaa','02','2009-9-3'

--------------开始查询--------------------------

select name,

  type1=sum(case type when '01' then 1 else 0 end),

  type2=sum(case type when '02' then 1 else 0 end)

from 

  tb 

group by 

  [name]

order by 

  [name]





----------------结果----------------------------

/* name type1       type2

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

aaa  2           2

bbb  1           0



(2 行受影响)



*/

ChinaJiaBing 2009-09-11

打赏
举报





declare @tb table (id int ,name nvarchar(10),type int,date datetime)

insert into @tb select 1,'aaa','01','2009-9-2'

      union all select 2,'bbb','01','2009-9-3'

      union all select 3,'aaa','01','2009-9-3'

      union all select 4,'aaa','02','2009-9-3'

      union all select 5,'aaa','02','2009-9-3'

select name ,count1=sum(case when type=1 then 1 else 0 end),              

             count2=sum(case when type=2 then 1 else 0 end)

from @tb group by name

      





name       count1      count2

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

aaa        2           2

bbb        1           0



(2 行受影响)

soft_wsx 2009-09-11

打赏
举报

     if object_id('[TB]') is not null drop table [TB]

      go

create table [TB]([id] int,[name] varchar(3),[type] varchar(2),[date] datetime)

insert [TB]

select 1,'aaa','01','2009-9-2' union all

select 2,'bbb','01','2009-9-3' union all

select 3,'aaa','01','2009-9-3' union all

select 4,'aaa','02','2009-9-3' union all

select 5,'aaa','02','2009-9-3'





select name,COUNT(distinct(date)) as type1,

            sum(case type when '02' then 1 else 0 end)  as type2

          from tb a 

            where date between '2009-9-1' and '2009-9-30'

          group by name

/*

name	type1	type2

aaa	2	2

bbb	1	0

*/

怎么都没有加日期

sdhdy 2009-09-11

打赏
举报

select name,

type1=sum(case type when '01' then 1 else 0 end),

type2=sum(case type when '02' then 1 else 0 end)

from tb 

group by name

order by name

SQL77 2009-09-11

打赏
举报

CASE WHEN

jiangshun 2009-09-11

打赏
举报



--> 测试数据：[TB]

--> 我的淘宝：http://shop36766744.taobao.com/ 

if object_id('[TB]') is not null drop table [TB]

create table [TB]([id] int,[name] varchar(3),[type] varchar(2),[date] datetime)

insert [TB]

select 1,'aaa','01','2009-9-2' union all

select 2,'bbb','01','2009-9-3' union all

select 3,'aaa','01','2009-9-3' union all

select 4,'aaa','02','2009-9-3' union all

select 5,'aaa','02','2009-9-3'

select name,sum(case when [type]='01' then 1 else 0 end),sum(case when [type]='02' then 1 else 0 end)

from TB group by name



/*

name             

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

aaa  2           2

bbb  1           0



(2 行受影响)



*/



drop table [TB]

soft_wsx 2009-09-11

打赏
举报

select name,count(distinct(type1)) as type1,count(distinct(type2)) as type2

  from tb group by name

关于对字段不同类型求和的问题

相关推荐