--1 select * from( select 手机名, sum(case when 机型='值班' then 数量 else 0 end)-sum(case when 机型='翻盖' then 数量 else 0 end) as 数量 from tb group by 手机名) where 数量>0
--2 select 手机名, sum(case when 机型='值班' then 1 else 0 end) as 直板, sum(case when 机型='翻盖' then 1 else 0 end) as 翻盖 from tb group by 手机名
select * from( select 手机名, sum(case when 机型='值班' then 数量 else 0 end)-sum(case when 机型='翻盖' then 数量 else 0 end) as 数量 from tb group by 手机名) a where 数量>0
第一句 select M.手机名 (M.数量1 - M.数量2) as 差额 from ( select 手机名, sum ( case when 机型 = 直板 then 数量1 else 0 end ) as 直板, sum ( case when 机型 = 翻盖 then 数量2 else 0 end ) as 翻盖 from TABLE group by 手机名 ) as M 第二句 select 手机名, sum ( case when 机型 = 直板 then 数量1 else 0 end ) as 直板, sum ( case when 机型 = 翻盖 then 数量2 else 0 end ) as 翻盖 from TABLE group by 手机名
--1 select a.手机名,a.num1 - b.num2 as 差额 from (select 手机名,sum(数量)num1 from tb where 机型 = '直板' group by 手机名)a left join (select 手机名,sum(数量)num2 from tb where 机型 = '翻盖' group by 手机名)b on a.手机名 = b.手机名 where a.num1 > b.num2
--2 select 手机名, sum(case when 机型='值班' then 1 else 0 end) as 直板, sum(case when 机型='翻盖' then 1 else 0 end) as 翻盖 from tb group by 手机名
declare @t table(手机名 varchar(10),机型 varchar(10),数量 int); insert into @t select '手机1','直板',10 union all select '手机1','翻板',5 union all select '手机2','直板',5 union all select '手机2','直板',10 union all select '手机3','翻板',10 ; select * from @t;-- 1. select 手机名,SUM(case 机型 when'直板' then 数量 else -数量 end) as 差额 from @t group by 手机名 having COUNT(1)>1-- 2. select 手机名,[直板] as [直板],[翻板] as [翻盖] from @t pivot (count(数量) for 机型 in ([直板],[翻板])) pvt
declare @t table(手机名 varchar(10),机型 varchar(10),数量 int); insert into @t select '手机1','直板',10 union all select '手机1','翻板',5 union all select '手机2','直板',5 union all select '手机2','直板',10 union all select '手机3','翻板',10 ; select * from @t;-- 1. select 手机名,SUM(case 机型 when'直板' then 数量 else -数量 end) as 差额 from @t group by 手机名 having COUNT(1)>1-- 2. select 手机名,[直板] as [直板],[翻板] as [翻盖] from @t pivot (count(数量) for 机型 in ([直板],[翻板])) pvt
手机名) a where 数量>0 这个a是什么意思?
FROM后一定是个对象,这里是表,那么就要对前边括号里的查询的结果集做一个别名。
use tempdb; /* create table test ( [手机名] nvarchar(10) not null, [机型] nvarchar(10) not null, [数量] int not null ); insert into test([手机名],[机型],[数量]) values ('手机1','直板',10), ('手机1','翻盖',5), ('手机2','直板',5), ('手机2','直板',10), ('手机3','翻盖',10); */ --问题1 select * from ( select [手机名], sum(case when [机型] = '直板' then [数量] else 0 end) - sum(case when [机型] = '翻盖' then [数量] else 0 end) as [差额] from test group by [手机名] ) as t where t.[差额] > 0;--问题2 select t.[手机名], sum( case when [机型] = '直板' then 1 else 0 end) AS [直板], sum( case when [机型] = '翻盖' then 1 else 0 end) AS [翻盖] from test as t group by [手机名];
--1
select * from(
select 手机名,
sum(case when 机型='值班' then 数量 else 0 end)-sum(case when 机型='翻盖' then 数量 else 0 end) as 数量
from tb group by 手机名) where 数量>0
--2
select 手机名,
sum(case when 机型='值班' then 1 else 0 end) as 直板,
sum(case when 机型='翻盖' then 1 else 0 end) as 翻盖
from tb group by 手机名
select * from(
select 手机名,
sum(case when 机型='值班' then 数量 else 0 end)-sum(case when 机型='翻盖' then 数量 else 0 end) as 数量
from tb group by 手机名) a where 数量>0
select M.手机名
(M.数量1 - M.数量2) as 差额
from
(
select 手机名,
sum
(
case
when 机型 = 直板 then 数量1
else 0
end
) as 直板,
sum
(
case
when 机型 = 翻盖 then 数量2
else 0
end
) as 翻盖
from TABLE
group by 手机名
) as M
第二句
select 手机名,
sum
(
case
when 机型 = 直板 then 数量1
else 0
end
) as 直板,
sum
(
case
when 机型 = 翻盖 then 数量2
else 0
end
) as 翻盖
from TABLE
group by 手机名
--1
select a.手机名,a.num1 - b.num2 as 差额
from (select 手机名,sum(数量)num1 from tb where 机型 = '直板' group by 手机名)a
left join
(select 手机名,sum(数量)num2 from tb where 机型 = '翻盖' group by 手机名)b
on a.手机名 = b.手机名
where a.num1 > b.num2
--2
select 手机名,
sum(case when 机型='值班' then 1 else 0 end) as 直板,
sum(case when 机型='翻盖' then 1 else 0 end) as 翻盖
from tb
group by 手机名
insert into @t
select '手机1','直板',10 union all
select '手机1','翻板',5 union all
select '手机2','直板',5 union all
select '手机2','直板',10 union all
select '手机3','翻板',10 ;
select * from @t;-- 1.
select 手机名,SUM(case 机型 when'直板' then 数量 else -数量 end) as 差额 from @t group by 手机名 having COUNT(1)>1-- 2.
select 手机名,[直板] as [直板],[翻板] as [翻盖] from @t
pivot (count(数量) for 机型 in ([直板],[翻板])) pvt
declare @t table(手机名 varchar(10),机型 varchar(10),数量 int);
insert into @t
select '手机1','直板',10 union all
select '手机1','翻板',5 union all
select '手机2','直板',5 union all
select '手机2','直板',10 union all
select '手机3','翻板',10 ;
select * from @t;-- 1.
select 手机名,SUM(case 机型 when'直板' then 数量 else -数量 end) as 差额 from @t group by 手机名 having COUNT(1)>1-- 2.
select 手机名,[直板] as [直板],[翻板] as [翻盖] from @t
pivot (count(数量) for 机型 in ([直板],[翻板])) pvt
手机名) a where 数量>0 这个a是什么意思?
FROM后一定是个对象,这里是表,那么就要对前边括号里的查询的结果集做一个别名。
use tempdb;
/*
create table test
(
[手机名] nvarchar(10) not null,
[机型] nvarchar(10) not null,
[数量] int not null
);
insert into test([手机名],[机型],[数量])
values
('手机1','直板',10),
('手机1','翻盖',5),
('手机2','直板',5),
('手机2','直板',10),
('手机3','翻盖',10);
*/
--问题1
select *
from
(
select [手机名],
sum(case when [机型] = '直板' then [数量] else 0 end)
- sum(case when [机型] = '翻盖' then [数量] else 0 end) as [差额]
from test
group by [手机名]
) as t
where t.[差额] > 0;--问题2
select
t.[手机名],
sum(
case
when [机型] = '直板' then 1 else 0
end) AS [直板],
sum(
case
when [机型] = '翻盖' then 1 else 0
end) AS [翻盖]
from test as t
group by [手机名];