select a.水果,b.count(*),c.count(*),d.count(*) from table1 a left outer join table1 b on b.水果=a.水果 left outer join table1 c on c.水果=a.水果 left outer join table1 d on d.水果=a.水果 where c.状态='烂的' and d.状态='没烂' group by a.水果
create table #t (水果 varchar(100),状态 varchar(100)) go insert into #t(水果,状态) select '苹果','烂的' union all select '橘子','没烂' union all select '苹果','烂的' union all select '橘子','烂的'select * from #t select 水果,cast(sum(1) as varchar(100)) + '[个]' as 个数, cast(sum(case when 状态='烂的' then 1 else 0 end) as varchar(100)) + '[个]' as 烂的, cast(sum(case when 状态='没烂' then 1 else 0 end) as varchar(100)) + '[个]' as 没烂 from #t group by 水果drop table #t
declare @t table(水果 varchar(10),状态 varchar(10))insert into @t select '苹果','烂的' union all select '橘子','没烂' union all select '苹果','烂的' union all select '橘子','烂的'select 水果, 个数=count(*), 烂的=sum(case 状态 when '没烂' then 0 else 1 end), 没烂=sum(case 状态 when '烂的' then 0 else 1 end) from @t group by 水果
我想问一下 select 水果,cast(sum(1) as varchar(100)) + '[个]' as 个数, 中的sum(1) "1"是什么意思? 我是菜鸟 请告诉我 谢谢
select 水果,cast(sum(1) as varchar(100)) + '[个]' as 个数, 中的sum(1) "1"是什么意思 符合条件的每一行计数1
from table1 a left outer join table1 b on b.水果=a.水果
left outer join table1 c on c.水果=a.水果
left outer join table1 d on d.水果=a.水果
where c.状态='烂的' and d.状态='没烂'
group by a.水果
create table #t (水果 varchar(100),状态 varchar(100))
go
insert into #t(水果,状态)
select '苹果','烂的' union all
select '橘子','没烂' union all
select '苹果','烂的' union all
select '橘子','烂的'select * from #t
select 水果,cast(sum(1) as varchar(100)) + '[个]' as 个数,
cast(sum(case when 状态='烂的' then 1 else 0 end) as varchar(100)) + '[个]' as 烂的,
cast(sum(case when 状态='没烂' then 1 else 0 end) as varchar(100)) + '[个]' as 没烂
from #t
group by 水果drop table #t
union all select '橘子','没烂'
union all select '苹果','烂的'
union all select '橘子','烂的'select 水果,
个数=count(*),
烂的=sum(case 状态 when '没烂' then 0 else 1 end),
没烂=sum(case 状态 when '烂的' then 0 else 1 end)
from @t
group by 水果
符合条件的每一行计数1