--表結構
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tTable]
GOCREATE TABLE [dbo].[tTable] (
[fPFgCode] [varchar] (30) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[fFgCode] [varchar] (30) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[fup] [decimal](18, 2) NULL
) ON [PRIMARY]
GOALTER TABLE [dbo].[tTable] WITH NOCHECK ADD
CONSTRAINT [PK_tTable] PRIMARY KEY CLUSTERED
(
[fPFgCode],
[fFgCode]
) ON [PRIMARY]
GO
數據
fPfgCode fFgCode fup
A PA 100.00
A PB NULL
A PC 200.00
B PB NULL
B PC 200.00
C PA 100.00
C PC 200.00結果
fPfgCode fFgCode
也就是以fPfgCode分組,當組有任一條記錄的fup為null,則此fPfgCode之和為0,否則為SUM之和要求:
1.一條SQL語句實現
2.不能用子查詢
A 0
B 0
C 300
fPfgCode fFgCode
A 0
B 0
C 300
from talbename where fup is null
union all
select fPfgCode,sum(fup)
from talbename a
where not exists(select 1 from talbename where fPfgCode = a.fPfgCode and fup
is null)
select fPfgCode,isnull(sum(fup),0)
from tablename
group by fPfgCode
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-12 16:14:39
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86)
-- May 26 2009 14:24:20
-- 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]([fPfgCode] varchar(1),[fFgCode] varchar(2),[fup] numeric(5,2))
insert [tb]
select 'A','PA',100.00 union all
select 'A','PB',null union all
select 'A','PC',200.00 union all
select 'B','PB',null union all
select 'B','PC',200.00 union all
select 'C','PA',100.00 union all
select 'C','PC',200.00
--------------开始查询--------------------------
select fPfgCode,isnull(fup,0) from tb where fup is null
union all
select
fPfgCode,sum(fup)
from
tb t
where
not exists(select 1 from tb where fPfgCode = t.fPfgCode and fup is null)
group by
fPfgCode----------------结果----------------------------
/* fPfgCode
-------- ---------------------------------------
A 0.00
B 0.00
C 300.00(3 行受影响)*/
CREATE TABLE [dbo].[tTable] (
[fPFgCode] [varchar] (30) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[fFgCode] [varchar] (30) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[fup] [decimal](18, 2) NULL
) ON [PRIMARY]
GO
insert into [tTable] ( [fPFgCode], [fFgCode], [fup] )
select 'A','PA', 100.00
union all
select 'A','PB',NULL
union all
select 'A','PC',200.00
union all
select 'B','PB',NULL
union all
select 'B','PC',200.00
union all
select 'C','PA',100.00
union all
select 'C','PC',200.00
select distinct [fPFgCode],isnull(fup,0)
from [tTable] where fup is null
union all
select [fPFgCode],sum(fup)
from [tTable] a
where not exists(select 1 from [tTable] where [fPFgCode] = a.[fPFgCode] and fup
is null)
group by [fPFgCode]fPFgCode
------------------------------ ---------------------------------------
A 0.00
B 0.00
C 300.00(3 行受影响)
/*------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-12 16:16:36
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)------------------------------------------------------------------*/
--> 生成测试数据表:tbIF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([fPfgCode] NVARCHAR(10),[fFgCode] NVARCHAR(10),[fup] dec(18,2))
INSERT [tb]
SELECT 'A','PA','100.00' UNION ALL
SELECT 'A','PB',NULL UNION ALL
SELECT 'A','PC','200.00' UNION ALL
SELECT 'B','PB',NULL UNION ALL
SELECT 'B','PC','200.00' UNION ALL
SELECT 'C','PA','100.00' UNION ALL
SELECT 'C','PC','200.00'
GO
--SELECT * FROM [tb]-->SQL查询如下:
select a.[fPfgCode],SUM(isnull(a.[fup],0)*B.fup) fup
from tb a
join (select [fPfgCode],MIN(case when fup is null then 0 else 1 end) fup from tb group by [fPfgCode]) b
on a.[fPfgCode]=b.[fPfgCode]
group by a.[fPfgCode]
/*
fPfgCode fup
---------- ---------------------------------------
A 0.00
B 0.00
C 300.00(3 行受影响)
*/这不算是子查询吧
樓上兩況弟用的子查詢或是多條語句
三樓的不是我想要的結果
-- Author : htl258(Tony)
-- Date : 2010-04-12 16:16:36
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)------------------------------------------------------------------*/
--> 生成测试数据表:tbIF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([fPfgCode] NVARCHAR(10),[fFgCode] NVARCHAR(10),[fup] dec(18,2))
INSERT [tb]
SELECT 'A','PA','100.00' UNION ALL
SELECT 'A','PB',NULL UNION ALL
SELECT 'A','PC','200.00' UNION ALL
SELECT 'B','PB',NULL UNION ALL
SELECT 'B','PC','200.00' UNION ALL
SELECT 'C','PA','100.00' UNION ALL
SELECT 'C','PC','200.00'
GO
--SELECT * FROM [tb]-->SQL查询如下:
select [fPfgCode],SUM(isnull([fup],0))*MIN(case when [fup] IS null then 0 else 1 end) FUP
from tb
group by [fPfgCode]
/*
fPfgCode FUP
---------- ---------------------------------------
A 0.00
B 0.00
C 300.00(3 行受影响)
*/楼主,这就是你要的。
from [tTable]
group by [fPfgCode]fPfgCode
------------------------------ ---------------------------------------
A 0.00
B 0.00
C 300.00
警告: 聚合或其他 SET 操作消除了空值。(3 行受影响)
INSERT INTO tTable
SELECT 'A','PA',100.00 UNION ALL
SELECT 'A','PB',null UNION ALL
SELECT 'A','PC',200.00 UNION ALL
SELECT 'B','PB',null UNION ALL
SELECT 'B','PC',200.00 UNION ALL
SELECT 'C','PA',100.00 UNION ALL
SELECT 'C','PC',200.00
GO
--查询所有数据
SELECT * FROM tTable
--显示记录
----------------------------------
fPFgCode fFgCode fup
1 A PA 100.00
2 A PB NULL
3 A PC 200.00
4 B PB NULL
5 B PC 200.00
6 C PA 100.00
7 C PC 200.00
----------------------------------
--Code
SELECT DISTINCT fPFgCode,ISNULL(fup,0) as fup
FROM tTable WHERE fup is null
UNION ALL
SELECT fPFgCode,SUM(fup) as fup
FROM tTable A
WHERE NOT EXISTS (SELECT fPFgCode FROM tTable WHERE fPFgCode=A.fPFgCode AND fup is null)
GROUP BY fPFgCode
INSERT INTO tTable
SELECT 'A','PA',100.00 UNION ALL
SELECT 'A','PB',null UNION ALL
SELECT 'A','PC',200.00 UNION ALL
SELECT 'B','PB',null UNION ALL
SELECT 'B','PC',200.00 UNION ALL
SELECT 'C','PA',100.00 UNION ALL
SELECT 'C','PC',200.00
GO
--查询所有数据
SELECT * FROM tTable
--显示记录
----------------------------------
fPFgCode fFgCode fup
1 A PA 100.00
2 A PB NULL
3 A PC 200.00
4 B PB NULL
5 B PC 200.00
6 C PA 100.00
7 C PC 200.00
----------------------------------
--Code
SELECT DISTINCT fPFgCode,ISNULL(fup,0) as fup
FROM tTable WHERE fup is null
UNION ALL
SELECT fPFgCode,SUM(fup) as fup
FROM tTable A
WHERE NOT EXISTS (SELECT fPFgCode FROM tTable WHERE fPFgCode=A.fPFgCode AND fup is null)
GROUP BY fPFgCode
DROP TABLE [tb]
GO
CREATE TABLE [tb]([fPfgCode] NVARCHAR(10),[fFgCode] NVARCHAR(10),[fup] dec(18,2))
INSERT [tb]
SELECT 'A','PA','100.00' UNION ALL
SELECT 'A','PB',NULL UNION ALL
SELECT 'A','PC','200.00' UNION ALL
SELECT 'B','PB',NULL UNION ALL
SELECT 'B','PC','200.00' UNION ALL
SELECT 'C','PA','100.00' UNION ALL
SELECT 'C','PC','200.00'
GO
select [fPfgCode],case when count(*)=count([fup]) then sum([fup]) else 0 end
from [tb]
group by [fPfgCode]