重复数据统计问题.
我现在得到的结果是:
SHOPID PRODUCTSNAME PRODUCTSPRICE PRODUCTS
Y001 AAA 50 1
Y001 AAA 50 1
Y002 AAA 50 1
Y002 BBB 100 1
Y002 BBB 100 1
Y003 BBB 100 1
Y003 AAA 50 1
我想要这样的结果:
SHOPID PRODUCTSNAME PRODUCTSPRICE PRODUCTS
Y001 AAA 50 2
Y002 AAA 50 1
BBB 100 2
Y003 AAA 50 1
BBB 100 1
谢谢各位高手帮帮忙!!!
[解决办法]
假设你上面的是表A:
select SHOPID, PRODUCTSNAME, PRODUCTSPRICE, sum(PRODUCTS) from A group by SHOPID, PRODUCTSNAME, PRODUCTSPRICE
[解决办法]
楼上正解。
select SHOPID, PRODUCTSNAME, PRODUCTSPRICE, sum(convert(int,PRODUCTS)) AS PRODUCTS_SUM from A group by SHOPID, PRODUCTSNAME, PRODUCTSPRICE
[解决办法]
select SHOPID, PRODUCTSNAME, PRODUCTSPRICE, sum(PRODUCTS) from tablename
group by SHOPID, PRODUCTSNAME, PRODUCTSPRICE
可以得到
SHOPID PRODUCTSNAME PRODUCTSPRICE PRODUCTS
Y001 AAA 50 2
Y002 AAA 50 1
Y002 BBB 100 2
Y003 AAA 50 1
Y003 BBB 100 1
这样的效果，然后在程序中通过合并SHOPID列相邻相同数据的方法来实现你要的效果。
[解决办法]
if object_id( 'tempdb..#tmp ') is not null
drop table #tmp
GO
declare @t table(SHOPID varchar(10), PRODUCTSNAME varchar(10), PRODUCTSPRICE int)
insert @t
select 'Y001 ', 'AAA ', 50 union all
select 'Y001 ', 'AAA ', 50 union all
select 'Y002 ', 'AAA ', 50 union all
select 'Y002 ', 'BBB ', 100 union all
select 'Y002 ', 'BBB ', 100 union all
select 'Y003 ', 'BBB ', 100 union all
select 'Y003 ', 'AAA ', 50
----统计重复的数量
select id = identity(int,1,1), /*用于只显示第一个SHOPID*/
SHOPID,PRODUCTSNAME,PRODUCTSPRICE,PRODUCTS = COUNT(1)
into #tmp
from @t group by SHOPID,PRODUCTSNAME,PRODUCTSPRICE
order by SHOPID,PRODUCTSNAME
----SHOPID相同的只显示第一个SHOPID
select SHOPID =
case
when not exists(select 1 from #tmp where SHOPID = t.SHOPID and id < t.id)
then SHOPID
else ' '
end,
PRODUCTSNAME,PRODUCTSPRICE,PRODUCTS from #tmp as t
/*结果:
SHOPID PRODUCTSNAME PRODUCTSPRICE PRODUCTS
---------------------------------------------------
Y001 AAA 50 2
Y002 AAA 50 1
BBB 100 2
Y003 AAA 50 1
BBB 100 1
*/
[解决办法]
create table test(SHOPID varchar(20),PRODUCTSNAME varchar(20),PRODUCTSPRICE int,PRODUCTS int)
insert test select 'Y001 ', 'AAA ',50,1
union all select 'Y001 ', 'AAA ',50,1
union all select 'Y002 ', 'AAA ',50,1
union all select 'Y002 ', 'BBB ',100,1
union all select 'Y002 ', 'BBB ',100,1
union all select 'Y003 ', 'BBB ',100,1
union all select 'Y003 ', 'AAA ',50,1
set nocount on
select SHOPID=case when not exists(select 1 from test where SHOPID=a.SHOPID and PRODUCTSNAME <a.PRODUCTSNAME) then SHOPID
else ' ' end,
PRODUCTSNAME,PRODUCTSPRICE,PRODUCTS=sum(PRODUCTS) from test a
group by SHOPID,PRODUCTSNAME,PRODUCTSPRICE
drop table test
/*
SHOPID PRODUCTSNAME PRODUCTSPRICE PRODUCTS
-------------------- -------------------- ------------- -----------
Y001 AAA 50 2
Y002 AAA 50 1
BBB 100 2
Y003 AAA 50 1
BBB 100 1
*/
