--建立测试环境
set nocount on
create table test(lsh varchar(20),spmc varchar(20),sl int)
insert into test select '081215111','三星800万像素像机','1'
insert into test select '081215111','三星512内存卡','2'
insert into test select '081215000','三星1000万像素像机','1'
insert into test select '081215000','三星512内存卡','1'
insert into test select '081215222','三星1G内存卡','2'
insert into test select '081215333','三星512内存卡','1'
insert into test select '081215444','三星512内存卡','1'
go
--测试select case when sl>1 then '两个或两个以上' else '一个' end as sldesc,sum(sl)as sl
from (select lsh,sum(sl) as sl from test
where spmc like '三星%内存卡'
group by lsh)a
group by case when sl>1 then '两个或两个以上' else '一个' end --删除测试环境
drop table test
set nocount off/*--
两个或两个以上 4
一个 3
*/
set nocount on
create table test(lsh varchar(20),spmc varchar(20),sl int)
insert into test select '081215111','三星800万像素像机','1'
insert into test select '081215111','三星512内存卡','2'
insert into test select '081215000','三星1000万像素像机','1'
insert into test select '081215000','三星512内存卡','1'
insert into test select '081215222','三星1G内存卡','2'
insert into test select '081215333','三星512内存卡','1'
insert into test select '081215444','三星512内存卡','1'
go
--测试select case when sl>1 then '两个或两个以上' else '一个' end as sldesc,sum(sl)as sl
from (select lsh,sum(sl) as sl from test
where spmc like '三星%内存卡'
group by lsh)a
group by case when sl>1 then '两个或两个以上' else '一个' end --删除测试环境
drop table test
set nocount off/*--
两个或两个以上 4
一个 3
*/
--> (让你望见影子的墙)生成测试数据,时间:2008-12-15
if not object_id('tb') is null
drop table tb
Go
Create table tb([lsh] nvarchar(9),[spmc] nvarchar(11),[sl] int)
Insert tb
select N'081215111',N'三星800万像素像机',1 union all
select N'081215111',N'三星512内存卡',2 union all
select N'081215000',N'三星1000万像素像机',1 union all
select N'081215000',N'三星512内存卡',1 union all
select N'081215222',N'三星1G内存卡',2 union all
select N'081215333',N'三星512内存卡',1 union all
select N'081215444',N'三星512内存卡',1
Go
Select * from tbselect case cnt when 2 then 'LSH有两个或以上的三星512内存卡' else 'LSH只有一个的三星512内存卡' end,
case cnt when 2 then sum (sl) when 1 then sum(sl) end
from ( select lsh,sl=sum(sl) from tb where spmc like '三星%内存卡' group by lsh) t,
( select lsh ,cnt=count(*) from tb group by lsh ) s
where s.lsh=t.lsh
group by cnt
LSH只有一个的三星512内存卡 4
LSH有两个或以上的三星512内存卡 3
insert into test select '081215111','三星800万像素像机','1'
insert into test select '081215111','三星512内存卡','2'
insert into test select '081215000','三星1000万像素像机','1'
insert into test select '081215000','三星512内存卡','1'
insert into test select '081215222','三星1G内存卡','2'
insert into test select '081215333','三星512内存卡','1'
insert into test select '081215444','三星512内存卡','1'
goselect 'LSH有两个或以上的' , sum(sl) from test where charindex('内存卡' , spmc) > 0 and lsh in (select lsh from test group by lsh having count(*) > 1)
union all
select 'LSH只有一个的' , sum(sl) from test where charindex('内存卡' , spmc) > 0 and lsh in (select lsh from test group by lsh having count(*) = 1)drop table test/*
----------------- -----------
LSH有两个或以上的 3
LSH只有一个的 4(所影响的行数为 2 行)
*/
FROM Test01
WHERE ccc = 1SELECT @S2 = COUNT(*) --找2个一上的
FROM TEST01
WHERE ccc > 1SELECT 'LSH有两个或以上的 三星512内存卡', @S1
UNION ALL
SELECT 'LSH只有一个的 三星512内存卡', @S2
--UNION 来合