表结构如下别名 名称 数量 序号
CREATE TABLE tb( TName varchar(20), Name (20) varchar(20), num float, xh int)
go
insert tb SELECT 'A' ,'A',30,1
UNION ALL SELECT 'ZR-A','A',30,2
UNION ALL SELECT 'C', 'C',30,3
UNION ALL SELECT 'C', 'C',20,3
UNION ALL SELECT 'C', 'C',40,3
UNION ALL SELECT 'C', 'C',50,4
UNION ALL SELECT 'C', 'C',60,4
insert tb SELECT 'B' ,'B',70,5
UNION ALL SELECT 'ZC-B','B',80,6
希望得到的结果是,排序结果如下表
Name num
A 60 序号1-2的合计数,因为名称一样
C 90 -序号3的合计数
C 110 --序号4的合计数
B 150 --序号5-6的合计数 ,因为名称一样
CREATE TABLE tb( TName varchar(20), Name (20) varchar(20), num float, xh int)
go
insert tb SELECT 'A' ,'A',30,1
UNION ALL SELECT 'ZR-A','A',30,2
UNION ALL SELECT 'C', 'C',30,3
UNION ALL SELECT 'C', 'C',20,3
UNION ALL SELECT 'C', 'C',40,3
UNION ALL SELECT 'C', 'C',50,4
UNION ALL SELECT 'C', 'C',60,4
insert tb SELECT 'B' ,'B',70,5
UNION ALL SELECT 'ZC-B','B',80,6
希望得到的结果是,排序结果如下表
Name num
A 60 序号1-2的合计数,因为名称一样
C 90 -序号3的合计数
C 110 --序号4的合计数
B 150 --序号5-6的合计数 ,因为名称一样
无论是在sql 2000,还是在 sql 2005 中,都没有提供字符串的聚合函数,
所以,当我们在处理下列要求时,会比较麻烦:
有表tb, 如下:
id value
----- ------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
需要得到结果:
id values
------ -----------
1 aa,bb
2 aaa,bbb,ccc
即, group by id, 求 value 的和(字符串相加)1. 旧的解决方法-- 1. 创建处理函数
CREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r = ''
SELECT @r = @r + ',' + value
FROM tb
WHERE id=@id
RETURN STUFF(@r, 1, 1, '')
END
GO
-- 调用函数SELECt id, values=dbo.f_str(id)
FROM tb
GROUP BY id
from tb
group by name,xh
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( TName varchar(20), Name varchar(20), num float, xh int)
go
insert tb SELECT 'A' ,'A',30,1
UNION ALL SELECT 'ZR-A','A',30,2
UNION ALL SELECT 'C', 'C',30,3
UNION ALL SELECT 'C', 'C',20,3
UNION ALL SELECT 'C', 'C',40,3
UNION ALL SELECT 'C', 'C',50,4
UNION ALL SELECT 'C', 'C',60,4
insert tb SELECT 'B' ,'B',70,5
UNION ALL SELECT 'ZC-B','B',80,6
go
select
name=RIGHT(TName,1),
[SUM]=SUM(num)
from tb k
where exists(select * from tb where LEN(TName)>1 and k.Name=name )
group by RIGHT(TName,1)
union
select
name,
SUM(num)
from tb k
where not exists(select * from tb where LEN(TName)>1 and k.Name=name )
group by Name,xh
order by [sum]name SUM
-------------------- ----------------------
A 60
C 90
C 110
B 150
SELECT * FROM (
SELECT [NAME],SUM(NUM) 'TOTAL' FROM TB T1
WHERE EXISTS( SELECT 1 FROM TB T2 WHERE T2.[NAME]=T1.[NAME] AND T2.TNAME<>T1.TNAME)
GROUP BY [NAME]
UNION ALL
SELECT [NAME],SUM(NUM) 'TOTAL' FROM TB T1
WHERE NOT EXISTS( SELECT 1 FROM TB T2 WHERE T2.[NAME]=T1.[NAME] AND T2.TNAME<>T1.TNAME)
GROUP BY [NAME],XH
) T ORDER BY [NAME]
go
insert tb SELECT 'A' ,'A',30,1
UNION ALL SELECT 'ZR-A','A',30,2
UNION ALL SELECT 'C', 'C',30,3
UNION ALL SELECT 'C', 'C',20,3
UNION ALL SELECT 'C', 'C',40,3
UNION ALL SELECT 'C', 'C',50,4
UNION ALL SELECT 'C', 'C',60,4
insert tb SELECT 'B' ,'B',70,5
UNION ALL SELECT 'ZC-B','B',80,6
goselect name,num= sum(num)
from (
select Name,num,case when (select count(1) from tb where xh = a.xh and name = a.Name and TName= a.TName)>1 then xh else 0 end as xh from tb a
) as t
group by name,xh
--结果
name num
-------------------- ----------------------
A 60
B 150
C 90
C 110(4 行受影响)
这个有问题的,因为NAME有可能是VV,而TNAME是YJV, NAME和TNAME之前没有必然关联的。
希望得到的结果是,排序结果如下表
Name num
A 60 序号1-2的合计数,因为名称一样
C 90 -序号3的合计数
C 110 --序号4的合计数
B 150 --序号5-6的合计数 ,因为名称一样
select name,num= sum(num)
from (
select Name,num,case when (select count(1) from tb
where xh = a.xh and name = a.Name and TName= a.TName)>1 then xh else 0 end as xh
from tb a
) as t
group by name,xh
order by numname num
-------------------- ----------------------
A 60
C 90
C 110
B 150
这句话不好改,请楼上的来改
这句话放在ELSE里面老是变成NULL值了,单独运行又没问题