计算总数的问题

helinhai 2010-04-08 04:39:47



s_ParterCategory	s_sonCategory	 Brand_name	SKU_name	    Specifications	  	PIMcount   FK_Iid 

头发护理用品	洗发水	 	  飘柔    	收款机（OA）	15ml	 	2123         1

头发护理用品	洗发水	 	  飘柔    	收款机（OA）	15ml	 	212          1

口腔护理用品	牙膏	 	  佳洁士   	收款机（OA2）	15ml	 	212          2

select FK_Iid ,s_ParterCategory,s_sonCategory,

sum(case when
s_ParterCategory='头发护理用品' and ( Brand_name='飘柔' or Brand_name='海飞丝' OR Brand_name ='潘婷' or Brand_name='沙宣' or Brand_name ='伊卡璐' OR Brand_name='威娜') then 1
when s_ParterCategory='口腔护理用品' and (Brand_name ='佳洁士' )then 1

else 0 end) pgSku_name

from F_Commodity group by FK_Iid ,s_sonCategory,s_ParterCategory

得到的答案是

1 口腔护理用品洗发水 2
2 口腔护理用品牙膏 1

这个是我的SQL 有点问题第一条pgSku_name 要是1 因为 FK_Iid =1 的五个字段 s_ParterCategory s_sonCategory Brand_name SKU_name Specifications 都是相同的所以只能算一个

按照我的SQL 改进一下判断五个是否相同相同的话就算一个

distinct 的我不知道怎么写到我的case when 里面去

...全文

163 18 打赏收藏转发到动态举报

写回复

用AI写文章

18 条回复

切换为时间正序

请发表友善的回复…

发表回复

helinhai 2010-04-08

打赏
举报

[Quote=引用 12 楼 kerafan 的回复:]
SQL code

--按道理应该是一个count、一个count的算，然后连接在一起
--方法比较笨，等高手贴简便方法
if OBJECT_ID('F_Commodity') is not null
drop table F_Commodity
go
create table F_Commodity (
s_ParterCategory nvarchar(10)……
[/Quote]

不过按照你这样来我想我建一个视图可能更好吧。谢谢你了！

helinhai 2010-04-08

打赏
举报

[Quote=引用 12 楼 kerafan 的回复:]
SQL code

--按道理应该是一个count、一个count的算，然后连接在一起
--方法比较笨，等高手贴简便方法
if OBJECT_ID('F_Commodity') is not null
drop table F_Commodity
go
create table F_Commodity (
s_ParterCategory nvarchar(10)……
[/Quote]

恩这个是我想要的。但是太耗费性能了吧!

helinhai 2010-04-08

打赏
举报

[Quote=引用 15 楼 ldslove 的回复:]
引用 7 楼 helinhai 的回复:
select FK_Iid ,s_ParterCategory,s_sonCategory,
COUNT(distinct Brand_name) as brandCount,
count(distinct SKU_name ) SkuCount,
sum(case when
s_ParterCategory='头发护理用品' and ( Bra……
[/Quote]

你这里用了where 还是不对吧比如COUNT(distinct Brand_name) as brandCount 我不用任何的条件的只有pgSku_name 才用条件的你这个等于全部都用了条件！

东那个升 2010-04-08

打赏
举报

[Quote=引用 7 楼 helinhai 的回复:]
select FK_Iid ,s_ParterCategory,s_sonCategory,
COUNT(distinct Brand_name) as brandCount,
count(distinct SKU_name ) SkuCount,
sum(case when
s_ParterCategory='头发护理用品' and ( Brand_name='飘柔'……
[/Quote]

select FK_Iid ,s_ParterCategory,s_sonCategory,  COUNT(distinct Brand_name) as brandCount,  

  count(distinct SKU_name ) SkuCount,COUNT(distinct Brand_name+SKU_name+Specifications) as pgSku_name,SUM(PIMcount) as pgPimcount

from F_Commodity

where Brand_name in( '飘柔','海飞丝','潘婷','沙宣','伊卡璐','威娜','佳洁士')

group by FK_Iid ,s_sonCategory,s_ParterCategory





     FK_Iid s_ParterCategory                                                                                     s_sonCategory                                                                                         brandCount    SkuCount  pgSku_name  pgPimcount

----------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ----------- ----------- ----------- -----------

          1 头发护理用品                                                                                               洗发水                                                                                                            1           1           1        2335

          2 口腔护理用品                                                                                               牙膏                                                                                                             1           1           1         212



(2 行受影响)

东那个升 2010-04-08

打赏
举报

select FK_Iid ,s_ParterCategory,s_sonCategory,COUNT(distinct Brand_name+SKU_name+Specifications) as pgSku_name

from F_Commodity

where Brand_name in( '飘柔','海飞丝','潘婷','沙宣','伊卡璐','威娜','佳洁士')

group by FK_Iid ,s_sonCategory,s_ParterCategory

东那个升 2010-04-08

打赏
举报

select FK_Iid ,s_ParterCategory,s_sonCategory,COUNT(distinct Brand_name+SKU_name+Specifications) as pgSku_name

from F_Commodity

group by FK_Iid ,s_sonCategory,s_ParterCategory



     FK_Iid s_ParterCategory                                                                                     s_sonCategory                                                                                         pgSku_name

----------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- -----------

          1 头发护理用品                                                                                               洗发水                                                                                                            1

          2 口腔护理用品                                                                                               牙膏                                                                                                             1



(2 行受影响)

老黎 2010-04-08

打赏
举报



--按道理应该是一个count、一个count的算，然后连接在一起

--方法比较笨，等高手贴简便方法

if OBJECT_ID('F_Commodity') is not null

    drop table F_Commodity

go

create table F_Commodity (

    s_ParterCategory  nvarchar(10) 

    ,s_sonCategory  nvarchar(10)

    ,Brand_name  nvarchar(10)

    ,SKU_name nvarchar(10)

    ,Specifications nvarchar(10)

    ,PIMcount int

    ,FK_Iid int

)

go

insert F_Commodity 

select '头发护理用品','洗发水','飘柔','收款机（OA）','15ml',2123,1 union all

select '头发护理用品','洗发水','飘柔','收款机（OA）','15ml',212,1  union all

select '口腔护理用品','牙膏','佳洁士','收款机（OA2）','15ml',212,2

go

select 

    a.*, b.pgSku_name

from 

(

select 

  FK_Iid

  ,s_ParterCategory

  ,s_sonCategory

  ,brandCount = count(distinct Brand_name)

  ,SkuCount = count(distinct SKU_name),

  sum(case when (s_ParterCategory='头发护理用品' and Brand_name in ('飘柔','海飞丝','潘婷','沙宣','伊卡璐','威娜'))

                  or (s_ParterCategory='口腔护理用品' and Brand_name ='佳洁士')

               then PIMcount

          else 0 

     end) pgPimcount

from 

    F_Commodity 

group by 

    FK_Iid ,s_sonCategory,s_ParterCategory

) a

left join

(

select FK_Iid, s_ParterCategory, s_sonCategory,

       sum(case when (s_ParterCategory='头发护理用品' and Brand_name in ('飘柔','海飞丝','潘婷','沙宣','伊卡璐','威娜'))

                      or(s_ParterCategory='口腔护理用品' and Brand_name ='佳洁士')

                      then 1

                else 0 

           end) pgSku_name  

from 

(select distinct 

    s_ParterCategory,s_sonCategory,Brand_name,SKU_name,Specifications,FK_Iid

 from 

    F_Commodity

)a

group by 

    FK_Iid ,s_sonCategory,s_ParterCategory

) b

on a.FK_Iid = b.FK_Iid and a.s_sonCategory = b.s_sonCategory and a.s_ParterCategory = b.s_ParterCategory



--结果

/*

FK_Iid      s_ParterCategory s_sonCategory brandCount  SkuCount    pgPimcount  pgSku_name

----------- ---------------- ------------- ----------- ----------- ----------- -----------

1           头发护理用品           洗发水           1           1           2335        1

2           口腔护理用品           牙膏            1           1           212         1



(2 行受影响)

*/

jwwyqs 2010-04-08

打赏
举报

帮顶吧

老黎 2010-04-08

打赏
举报

为什么要在一起呢？
SQL无法在一个select中
按照3个字段group by
同时又按照4个字段group by

helinhai 2010-04-08

打赏
举报

沉了。最后顶一次。。

helinhai 2010-04-08

打赏
举报

顶啊。咋办？？

helinhai 2010-04-08

打赏
举报

select FK_Iid ,s_ParterCategory,s_sonCategory,
COUNT(distinct Brand_name) as brandCount,
count(distinct SKU_name ) SkuCount,
sum(case when
s_ParterCategory='头发护理用品' and ( Brand_name='飘柔' or Brand_name='海飞丝' OR Brand_name ='潘婷' or Brand_name='沙宣' or Brand_name ='伊卡璐' OR Brand_name='威娜') then 1
when s_ParterCategory='口腔护理用品' and (Brand_name ='佳洁士' )then 1

else 0 end) pgSku_name ,
sum(case when
s_ParterCategory='头发护理用品' and (Brand_name='飘柔' or sku_name='海飞丝' OR SKU_name ='潘婷' or Brand_name='沙宣'
or Brand_name ='伊卡璐' OR Brand_name='威娜') then PIMcount
when s_ParterCategory='口腔护理用品' and (Brand_name ='佳洁士' )then PIMcount

else 0 end)
pgPimcount

from F_Commodity group by FK_Iid ,s_sonCategory,s_ParterCategory

brandCount ， SkuCount ， pgPimcount 按我的算 pgSku_name按上面的要求算

怎么办呢？

feixianxxx 2010-04-08

打赏
举报

[Quote=引用 5 楼 helinhai 的回复:]
因为我的计算列不是就计算这一个的如果加了 from
(select distinct
s_ParterCategory,s_sonCategory,Brand_name,SKU_name,Specifications,PIMcount,FK_Iid
from
F_Commodity
)a
的话影响了我其他的列统计我的其他计算列不要5个相同的可能2个就可以了！
[/Quote]
。。数据说话

helinhai 2010-04-08

打赏
举报

因为我的计算列不是就计算这一个的如果加了 from
(select distinct
s_ParterCategory,s_sonCategory,Brand_name,SKU_name,Specifications,PIMcount,FK_Iid
from
F_Commodity
)a
的话影响了我其他的列统计我的其他计算列不要5个相同的可能2个就可以了！

feixianxxx 2010-04-08

打赏
举报

/*

	*************************************

	*   T-MAC 小编                      *

	*        -->努力成长中              *

	*        -->梦想DBA                 *

	*************************************

*/

if OBJECT_ID('F_Commodity') is not null

	drop table F_Commodity

go

create table F_Commodity (s_ParterCategory  varchar(100) ,s_sonCategory  varchar(100), Brand_name  varchar(100), SKU_name varchar(100),Specifications varchar(10),PIMcount int,FK_Iid int)

insert F_Commodity select 

'头发护理用品',    '洗发水',           '飘柔',        '收款机（OA）' ,   '15ml' ,        2123     ,    1 union  select 

'头发护理用品',    '洗发水',           '飘柔',        '收款机（OA）' ,  '15ml'  ,       212       ,   1  union  select 

'口腔护理用品',    '牙膏'  ,         '佳洁士',       '收款机（OA2）' ,   '15ml' ,        212      ,    2

go

select FK_Iid ,s_ParterCategory,s_sonCategory,

       sum(case when s_ParterCategory='头发护理用品' and (Brand_name='飘柔' or Brand_name='海飞丝' OR Brand_name ='潘婷' or Brand_name='沙宣' or Brand_name ='伊卡璐' OR Brand_name='威娜') then 1

                when s_ParterCategory='口腔护理用品' and (Brand_name ='佳洁士' )then 1

                else 0 end) pgSku_name  

from 

(select distinct 

    s_ParterCategory,s_sonCategory,Brand_name,SKU_name,Specifications,FK_Iid

 from 

    F_Commodity

)a

group by 

    FK_Iid ,s_sonCategory,s_ParterCategory

helinhai 2010-04-08

打赏
举报

哎。还是没有解决

老黎 2010-04-08

打赏
举报



select FK_Iid 

       ,s_ParterCategory

       ,s_sonCategory,

       sum(case when s_ParterCategory='头发护理用品' and (Brand_name='飘柔' or Brand_name='海飞丝' OR Brand_name ='潘婷' or Brand_name='沙宣' or Brand_name ='伊卡璐' OR Brand_name='威娜') then 1

                when s_ParterCategory='口腔护理用品' and (Brand_name ='佳洁士' )then 1

                else 0 end) pgSku_name  



from 

(select distinct 

    s_ParterCategory,s_sonCategory,Brand_name,SKU_name,Specifications,PIMcount,FK_Iid

 from 

    F_Commodity

)a

group by 

    FK_Iid ,s_sonCategory,s_ParterCategory