34,838
社区成员




select case when (GROUPING(tb.comName)=1) then '发货公司合计' else isnull(comName,'') end as comName,
case when (GROUPING(tb.outDate)=1) then '发货日期合计' else isnull(outDate,'') end as outDate ,
case when (GROUPING(tb.lithology)=1) then '岩性合计' else isnull(lithology,'') end as lithology ,
case when (GROUPING(tb.spec)=1) then '规格合计' else isnull(spec,'') end as spec ,
case when (GROUPING(tb.type)=1) then '型号合计' else isnull(type,'') end as type ,
sum(num)
from (select c.Apellation as comName, convert(varchar(10),a.outDate,120) as outDate,l.lithologyName as lithology,s.specName as spec,t.typeName as type,a.consignmentNum as num from CZ_ImportMaterials a inner join CZ_Lithology l on a.lithology=l.id inner join CZ_Spec s on a.spec=s.id inner join CZ_Type t on t.id=a.type inner join CT_Clientinfo c on c.id=a.outCompany) as tb
group by comName,outDate,lithology,spec,type with cube order by outDate
having GROUPING(tb.comName)+GROUPING(tb.outDate)+GROUPING(tb.lithology)+GROUPING(tb.spec)+GROUPING(tb.type)>0
select 发货单位,发货时间表,right(岩性,2) as 岩性,right(型号,4) as 型号,right(类型,4) as 类型,sum(总数) as 总数
from ta
group by 发货单位,发货时间表,right(岩性,2),right(型号,4),right(类型,4)
select 发货单位,发货时间,岩性 as [岩性/型号/类型],sum(总数) from tb group by 发货单位,发货时间,岩性
union all
select 发货单位,发货时间,型号 as [岩性/型号/类型],sum(总数) from tb group by 发货单位,发货时间,型号
union all
select 发货单位,发货时间,类型 as [岩性/型号/类型],sum(总数) from tb group by 发货单位,发货时间,类型
select 发货单位,发货时间,岩性,型号,类型,
(select sum(总数) from tb where 岩性=a.岩性 and 型号=a.型号 and 类型=a.类型) as 总数
from tb a
select 发货单位,发货时间表,岩性,型号,类型,sum(总数) as 总数
from ta
group by 发货单位,发货时间表,岩性,型号,类型
select 岩性,型号,类型,sum(数量) as 总数 from tbname group by 岩性,型号,类型
发货单位,发货时间,岩性,型号,类型,总数
岩性,型号,类型都一样算一种产品
-
有点不通,一种岩性,型号,类型
会有多个发货单位,发货时间
怎么选择?
像这样.
tba
ID classid name
1 1,2,3 西服
2 2,3 中山装
3 1,3 名裤
tbb
id classname
1 衣服
2 上衣
3 裤子
我得的结果是
id classname name
1 衣服,上衣,裤子 西服
2 上衣,裤子 中山装
3 衣服,裤子 名裤
select 发货单位,发货时间表,岩性,型号,类型,sum(总数) as 总数
from ta
group by 发货单位,发货时间表,岩性,型号,类型