问题没解决 呀 ,很急

Don_Juan 2008-03-19 06:14:52
表里的数据不单是这些,还有其他 ,只是要列出: 发货单位,发货时间,岩性,型号,类型,总数
要分为,发货单位,发货时间表,(岩性,型号,类型) 三种为一样求一SQL
...全文
99 点赞 收藏 16
写回复
16 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
JiangHongTao 2008-03-20
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
回复
Don_Juan 2008-03-19
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上抄了个,也太详细了,有没有稍微简单点的呀
回复
-狙击手- 2008-03-19
select 发货单位,发货时间表,right(岩性,2) as 岩性,right(型号,4) as  型号,right(类型,4) as 类型,sum(总数) as 总数 
from ta
group by 发货单位,发货时间表,right(岩性,2),right(型号,4),right(类型,4)
回复
Don_Juan 2008-03-19
回楼上的,我要 [岩性/型号/类型 每一行都有这三个,说实话我也不太知道要写成什么样的

有张明细表
序号 发货单位 发货日期 岩性 规格 型号 发货数
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

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

回复
JiangHongTao 2008-03-19
不知道是不是这个意思
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 发货单位,发货时间,类型
回复
huangqing_80 2008-03-19
路过
回复
-晴天 2008-03-19

select 发货单位,发货时间,岩性,型号,类型,
(select sum(总数) from tb where 岩性=a.岩性 and 型号=a.型号 and 类型=a.类型) as 总数
from tb a
回复
Don_Juan 2008-03-19
单位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


回复
Don_Juan 2008-03-19
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 2008-03-19
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
回复
liangCK 2008-03-19
select 发货单位,发货时间表,岩性,型号,类型,sum(总数) as 总数 
from ta
group by 发货单位,发货时间表,岩性,型号,类型
回复
wzy_love_sly 2008-03-19
 select 岩性,型号,类型,sum(数量) as 总数 from tbname group by 岩性,型号,类型

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


回复
liangCK 2008-03-19
像这样.

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 衣服,裤子 名裤
回复
-狙击手- 2008-03-19
select 发货单位,发货时间表,岩性,型号,类型,sum(总数) as 总数 
from ta
group by 发货单位,发货时间表,岩性,型号,类型
回复
liangCK 2008-03-19
贴一下原始数据.和结果数据.
回复
liangCK 2008-03-19
把你的数据贴出来看看.
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-03-19 06:14
社区公告
暂无公告