数据库表格式
水费
4
4
4
5
5
6
这个字段 我想请教一下```这个字段合计的话`! 怎么样写才能得出`4+5+6`=15`不重复加~呢`
sql`select 语句
如果用SUM(水费),就会有重复````~!烦啊``~`
水费
4
4
4
5
5
6
这个字段 我想请教一下```这个字段合计的话`! 怎么样写才能得出`4+5+6`=15`不重复加~呢`
sql`select 语句
如果用SUM(水费),就会有重复````~!烦啊``~`
select sum(distinct 水费) from 表名
-- Author: happyflsytone
-- Date:2008-10-20 17:50:21
-------------------------------------- Test Data: ta
IF OBJECT_ID('ta') IS NOT NULL
DROP TABLE ta
Go
CREATE TABLE ta(水费 INT)
Go
INSERT INTO ta
SELECT 4 UNION ALL
SELECT 4 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 5 UNION ALL
SELECT 6
GO
--Start
SELECT sum(distinct 水费)
FROM ta
--Result:
/*-----------
15(1 行受影响)*/
--End
DROP TABLE ta
Go
CREATE TABLE ta(a int,水费 INT)
Go
INSERT INTO ta
SELECT 1,4 UNION ALL
SELECT 1,4 UNION ALL
SELECT 2,4 UNION ALL
SELECT 2,5 UNION ALL
SELECT 3,5 UNION ALL
SELECT 4,6
GO
--Start
SELECT a,sum(distinct 水费)
FROM ta group by a
declare @T table ([ID] varchar(2),[费用] int)
insert into @T
select '01',5 union all
select '01',5 union all
select '01',5 union all
select '02',4 union all
select '02',4 union all
select '03',6 union all
select '03',6select * from @T
--Code
select sum(cc) from
(select [id],MIN(费用) as cc from @T group by ID) tselect sum(费用) from
(select distinct [id],费用 from @T) t--Result
/*
15
*/
insert into @tb
select '01',5 union all
select '01',5 union all
select '01',5 union all
select '02',4 union all
select '02',4 union all
select '03',6 union all
select '03',6select [ID],sum(distinct [水费]) as '水费' from @tb
group by [ID]ID 水费
---- -----------
01 5
02 4
03 6(所影响的行数为 3 行)
insert into @tb
select '01',5 union all
select '01',5 union all
select '01',5 union all
select '02',4 union all
select '02',4 union all
select '03',6 union all
select '03',6select sum([水费]) as '水费' from
(select [ID],sum(distinct [水费]) as '水费' from @tb
group by [ID]) a水费
-----------
15(所影响的行数为 1 行)
ID 费用
01 5
01 5
01 5
02 4
02 4
03 6
03 6 不能用`ID group by ``````前提条件是`不能合并到一起`也能计算费用`