--> 数据库版本: --> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 --> 测试数据:[TB] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]') AND type in (N'U')) DROP TABLE [TB] GO---->建表 create table [TB]([name] varchar(2),[desc] varchar(3),[numb] int) insert [TB] select 'aa','aa1',23 union all select 'aa','aa2',12 union all select 'bb','aa1',3 union all select 'bb','aa2',13 union all select 'aa','aa3',5 union all select 'bb','aa3',6 GO select * from ( select name,sum(case when [desc] ='aa1' then numb else 0 end ) as aa1 ,sum(case when [desc] ='aa2' then numb else 0 end ) as aa2 from TB where [desc] in ('aa1','aa2') group by name ) t where aa1>aa2--> 查询结果 SELECT * FROM [TB] t where not exists(select name,[desc] ,SUM(numb) as numb from TB where name=t.name group by name,[desc]) --> 删除表格 --DROP TABLE [TB]
--> 数据库版本: --> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 --> 测试数据:[TB] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]') AND type in (N'U')) DROP TABLE [TB] GO---->建表 create table [TB]([name] varchar(2),[desc] varchar(3),[numb] int) insert [TB] select 'aa','aa1',23 union all select 'aa','aa2',12 union all select 'bb','aa1',3 union all select 'bb','aa2',13 union all select 'aa','aa3',5 union all select 'bb','aa3',6 GO--> 查询结果 select * from ( select name,sum(case when [desc] ='aa1' then numb else 0 end ) as aa1 ,sum(case when [desc] ='aa2' then numb else 0 end ) as aa2 from TB where [desc] in ('aa1','aa2') group by name ) t where aa1>aa2--> 删除表格 --DROP TABLE [TB]上面错了,这个才是
SELECT [客户] ,SUM(CASE WHEN [商品号]='商品1' THEN [购买量] ELSE 0 END) ,SUM(CASE WHEN [商品号]='商品2' THEN [购买量] ELSE 0 END) FROM [表名] GROUP BY [客户] HAVING SUM(CASE WHEN [商品号]='商品1' THEN [购买量] ELSE 0 END) >SUM(CASE WHEN [商品号]='商品2' THEN [购买量] ELSE 0 END)
--> 数据库版本: --> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 --> 测试数据:trans IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'trans') AND type in (N'U')) DROP TABLE trans GO---->建表 create table trans([cno] int,[pno] int,[num] int) insert trans select 1,1,12 union all select 1,2,21 union all select 2,1,22 union all select 2,2,10 GO--> 查询结果 select * from ( select [cno],sum(case when [pno] ='1' then [num] else 0 end ) as aa1 ,sum(case when [pno] ='2' then [num] else 0 end ) as aa2 from trans where [pno] in ('1','2') group by [cno] ) t where aa1>aa2--> 删除表格 --DROP TABLE trans
IF OBJECT_ID('trans') IS NOT NULL DROP TABLE trans GO CREATE TABLE trans( cno int ,pno int ,num int ) INSERT INTO trans SELECT 1, 1, 12 UNION ALL SELECT 1, 2, 21 UNION ALL SELECT 2, 1, 22 UNION ALL SELECT 2, 2, 10SELECT cno ,SUM(CASE WHEN pno=1 THEN num ELSE 0 END) ,SUM(CASE WHEN pno=2 THEN num ELSE 0 END) FROM trans GROUP BY cno HAVING SUM(CASE WHEN pno=1 THEN num ELSE 0 END) >SUM(CASE WHEN pno=2 THEN num ELSE 0 END) /* 2 22 10 */
USE testDb1; GO IF OBJECT_ID('dbo.usp_CountCntID') IS NOT NULL DROP PROC dbo.usp_CountCntID; GO CREATE PROC dbo.usp_CountCntID ( @goodsid1 AS int , @goodsid2 AS int ) AS SET NOCOUNT ON; SELECT CustermID ,SUM(CASE WHEN GoodsID=@goodsid1 THEN Count1 ELSE 0 END)as cot1 ,SUM(CASE WHEN GoodsID=@goodsid2 THEN Count1 ELSE 0 END)as cot2 FROM tb_test GROUP BY CustermID HAVING SUM(CASE WHEN GoodsID=@goodsid1 THEN Count1 ELSE 0 END) >SUM(CASE WHEN GoodsID=@goodsid2 THEN Count1 ELSE 0 END) GO
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB]([name] varchar(2),[desc] varchar(3),[numb] int)
insert [TB]
select 'aa','aa1',23 union all
select 'aa','aa2',12 union all
select 'bb','aa1',3 union all
select 'bb','aa2',13 union all
select 'aa','aa3',5 union all
select 'bb','aa3',6
GO
select * from (
select name,sum(case when [desc] ='aa1' then numb else 0 end ) as aa1
,sum(case when [desc] ='aa2' then numb else 0 end ) as aa2
from TB
where [desc] in ('aa1','aa2')
group by name
) t where aa1>aa2--> 查询结果
SELECT * FROM [TB] t
where not exists(select name,[desc] ,SUM(numb) as numb
from TB
where name=t.name
group by name,[desc])
--> 删除表格
--DROP TABLE [TB]
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB]([name] varchar(2),[desc] varchar(3),[numb] int)
insert [TB]
select 'aa','aa1',23 union all
select 'aa','aa2',12 union all
select 'bb','aa1',3 union all
select 'bb','aa2',13 union all
select 'aa','aa3',5 union all
select 'bb','aa3',6
GO--> 查询结果
select * from (
select name,sum(case when [desc] ='aa1' then numb else 0 end ) as aa1
,sum(case when [desc] ='aa2' then numb else 0 end ) as aa2
from TB
where [desc] in ('aa1','aa2')
group by name
) t where aa1>aa2--> 删除表格
--DROP TABLE [TB]上面错了,这个才是
客户号 商品号 购买量
cno pno num 1 1 12
1 2 21
2 1 22
2 2 10
,SUM(CASE WHEN [商品号]='商品1' THEN [购买量] ELSE 0 END)
,SUM(CASE WHEN [商品号]='商品2' THEN [购买量] ELSE 0 END)
FROM [表名]
GROUP BY [客户]
HAVING
SUM(CASE WHEN [商品号]='商品1' THEN [购买量] ELSE 0 END)
>SUM(CASE WHEN [商品号]='商品2' THEN [购买量] ELSE 0 END)
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:trans
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'trans')
AND type in (N'U'))
DROP TABLE trans
GO---->建表
create table trans([cno] int,[pno] int,[num] int)
insert trans
select 1,1,12 union all
select 1,2,21 union all
select 2,1,22 union all
select 2,2,10
GO--> 查询结果
select * from (
select [cno],sum(case when [pno] ='1' then [num] else 0 end ) as aa1
,sum(case when [pno] ='2' then [num] else 0 end ) as aa2
from trans
where [pno] in ('1','2')
group by [cno]
) t where aa1>aa2--> 删除表格
--DROP TABLE trans
GO
CREATE TABLE trans(
cno int
,pno int
,num int
)
INSERT INTO trans
SELECT 1, 1, 12 UNION ALL
SELECT 1, 2, 21 UNION ALL
SELECT 2, 1, 22 UNION ALL
SELECT 2, 2, 10SELECT cno
,SUM(CASE WHEN pno=1 THEN num ELSE 0 END)
,SUM(CASE WHEN pno=2 THEN num ELSE 0 END)
FROM trans
GROUP BY cno
HAVING
SUM(CASE WHEN pno=1 THEN num ELSE 0 END)
>SUM(CASE WHEN pno=2 THEN num ELSE 0 END)
/*
2 22 10
*/
GO
IF OBJECT_ID('dbo.usp_CountCntID') IS NOT NULL
DROP PROC dbo.usp_CountCntID;
GO
CREATE PROC dbo.usp_CountCntID
(
@goodsid1 AS int ,
@goodsid2 AS int
)
AS
SET NOCOUNT ON;
SELECT CustermID
,SUM(CASE WHEN GoodsID=@goodsid1 THEN Count1 ELSE 0 END)as cot1
,SUM(CASE WHEN GoodsID=@goodsid2 THEN Count1 ELSE 0 END)as cot2
FROM tb_test
GROUP BY CustermID
HAVING
SUM(CASE WHEN GoodsID=@goodsid1 THEN Count1 ELSE 0 END)
>SUM(CASE WHEN GoodsID=@goodsid2 THEN Count1 ELSE 0 END)
GO