问题没解决 呀 ,很急

[Don_Juan]

表里的数据不单是这些,还有其他 ,只是要列出: 发货单位,发货时间,岩性,型号,类型,总数
要分为,发货单位,发货时间表,(岩性,型号,类型) 三种为一样求一SQL

[liangCK]

把你的数据贴出来看看.

[liangCK]

贴一下原始数据.和结果数据.

[-狙击手-]

select 发货单位,发货时间表,岩性,型号,类型,sum(总数) as 总数 
from ta
group by 发货单位,发货时间表,岩性,型号,类型

[liangCK]

像这样.

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     衣服，裤子          名裤

[wzy_love_sly]

 select 岩性,型号,类型,sum(数量) as 总数 from tbname group by 岩性,型号,类型

发货单位,发货时间,岩性,型号,类型,总数
岩性,型号,类型都一样算一种产品
-
有点不通，一种岩性,型号,类型
会有多个发货单位,发货时间
怎么选择？

[liangCK]

select 发货单位,发货时间表,岩性,型号,类型,sum(总数) as 总数 
from ta
group by 发货单位,发货时间表,岩性,型号,类型

[Don_Juan]

CREATE TABLE [CZ_ImportMaterials] (
[id] [varchar] (25) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[contractor] [varchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
[inCompany] [int] NULL ,
[outCompany] [int] NULL ,
[inDate] [datetime] NULL ,
[outDate] [datetime] NULL ,
[Transport] [int] NULL ,
[carrierCompany] [int] NULL ,
[carNO] [varchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
[spec] [int] NULL ,
[type] [int] NULL ,
[variety] [int] NULL ,
[lithology] [int] NULL ,
[MaterialsType] [int] NULL ,
[units] [varchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
[consignmentNum] [float] NULL ,
[price] [decimal](8, 2) NULL ,
[totalPrice] [decimal](16, 2) NULL ,
[realNum] [float] NULL ,
[freight] [decimal](8, 2) NULL ,
[total] [decimal](16, 2) NULL ,
[remark] [text] COLLATE Chinese_PRC_CI_AS NULL ,
[weightman] [int] NULL ,
[principal] [int] NULL ,
[carrier] [varchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK__CZ_ImportMateria__571DF1D5] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY] ,
CONSTRAINT [carrierCompany_Import_PK] FOREIGN KEY
(
[carrierCompany]
) REFERENCES [CZ_Company] (
[id]
),
CONSTRAINT [transport_Import_PK] FOREIGN KEY
(
[Transport]
) REFERENCES [CZ_Transport] (
[id]
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

[Don_Juan]

CREATE TABLE [CZ_Lithology] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[lithologyName] [varchar] (25) COLLATE Chinese_PRC_CI_AS NULL ,
PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO

岩性,型号,类型 都是这样的表

[Don_Juan]

单位A 2008-3-12 岩性A,型号A,类型A 总数为1000
单位A 2008-3-12 岩性A,型号A,类型B 总数为2000
单位A 2008-3-12 岩性A,型号B,类型A 总数为10300
单位B 2008-3-12 岩性A,型号B,类型A 总数为10300
单位C 2008-3-12 岩性A,型号B,类型A 总数为10300
单位A 2008-4-12 岩性A,型号B,类型A 总数为10300

[-晴天]

select 发货单位,发货时间,岩性,型号,类型,
(select sum(总数) from tb where 岩性=a.岩性 and 型号=a.型号 and 类型=a.类型) as 总数
 from tb a

[huangqing_80]

路过

[JiangHongTao]

不知道是不是这个意思

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 发货单位,发货时间,类型

[Don_Juan]

回楼上的,我要 [岩性/型号/类型 每一行都有这三个,说实话我也不太知道要写成什么样的

有张明细表
序号 发货单位 发货日期 岩性 规格 型号 发货数
1 3 2008-03-19 0:00:00 岩性B 货物规格乙 货物型号C 1
2 6 2008-03-19 0:00:00 岩性B 货物规格丙 货物型号B 3
3 4 2008-03-19 0:00:00 岩性C 货物规格丙 货物型号B 3
4 4 2008-03-19 0:00:00 岩性C 货物规格乙 货物型号C 3
5 5 2008-03-19 0:00:00 岩性C 货物规格丙 货物型号B 0
6 5 2008-03-19 0:00:00 岩性D 货物规格丙 货物型号D 0
7 4 岩性D 货物规格乙 货物型号B 0
8 8888 2008-03-19 0:00:00 岩性B 货物规格丙 货物型号C 987
9 8888 2008-03-19 0:00:00 岩性B 货物规格丙 货物型号C 897

要求照着上面的写个汇兑总表

[-狙击手-]

select 发货单位,发货时间表,right(岩性,2) as 岩性,right(型号,4) as  型号,right(类型,4) as 类型,sum(总数) as 总数 
from ta
group by 发货单位,发货时间表,right(岩性,2),right(型号,4),right(类型,4)

[Don_Juan]

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


我从MSDN上抄了个,也太详细了,有没有稍微简单点的呀

[JiangHongTao]

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