嗯,没天理,老大硬是要得到以下的结果SQL

[QiQi_yun]

P:老大以为很容易,项目都变成三次开发,
硬是要得到以下数据
谢谢高手看看,我有一百分哦!
不过有N多问题
(其实我N伤心问别人问题!)

select 
DISTINCT 
a.ProductID ,
a.ProductName,
a.Unit ,a.Spec  ,
sum(a.Quantity ) as '期初数量',
(select DISTINCT count(Quantity) from tb_ParticularDepot where ProductID=a.ProductID and State=0 group by d.ProductID ) as '调拔数量',
sum(b. Amount) as '销售数量',
('期初数量-调拔数量-销售数量')as '期末数量'
from tb_ParticularDepot as a ,
tb_ClientConsume as b
where a.ProductID=b.ItemID
and a.state=0 and a.Quantity>0
order by a.ProductID 
group by a.ProductID,
a.ProductID ,
a.ProductName ,
a.Unit,
a.Spec 

问题一:子查询不对

(select DISTINCT count(Quantity) from tb_ParticularDepot where ProductID=a.ProductID and State=0 group by d.ProductID ) as '调拔数量',

问题二:期末数量不知道如何得到?通过现在查询出来的数据

('期初数量-调拔数量-销售数量')as '期末数量'

问题三:分组不正确

group by a.ProductID,
a.ProductID ,
a.ProductName ,
a.Unit,
a.Spec 

问题四:两个表之间没有主外键关系,只是数据相同(不知描述是否正确?)

where a.ProductID=b.ItemID

问题五:如果得到数据,数据的准确性如何?

[-晴天]

(select DISTINCT count(Quantity) from tb_ParticularDepot where ProductID=a.ProductID and State=0 group by d.ProductID ) as '调拔数量',
--还得改,不然分组统计不对
-->
sum(select 1 from tb_ParticularDepot where ProductID=a.ProductID and State=0 group by d.ProductID ) as '调拔数量',
--不知道这样行不行.

[等不到来世]

[Quote=引用 10 楼 dobear_0922 的回复:]
引用 2 楼 dawugui 的回复:
你给出表结构,测试数据,计算方法,结果

同意
[/Quote]
.

[fcuandy]

没业务需求描述和表，都是白说。

[zfh198601]

你老大有问题

[QiQi_yun]

第一个表结构

CREATE TABLE [dbo].[tb_ClientConsume](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[LinkCode] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[ManualNo] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[MemberID] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Consumer] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[BlockID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CardFaceID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[SortType] [int] NULL,
	[ItemID] [int] NULL,
	[ItemName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[StandardPrice] [float] NULL,
	[PreferentialPrice] [float] NULL,
	[Discount] [float] NULL,
	[Amount] [int] NULL,
	[ProductMoney] [float] NULL CONSTRAINT [de_ProductMoney]  DEFAULT ((0)),
	[ServiceItemMoney] [float] NULL CONSTRAINT [de_ServiceItemMoney]  DEFAULT ((0)),
	[ServiceCardMoney] [float] NULL CONSTRAINT [de_ServiceCardMoney]  DEFAULT ((0)),
	[TotalMoney] [float] NULL,
	[Encash] [float] NULL,
	[CardDeduct] [float] NULL,
	[ServiceCardID] [int] NULL,
	[ServiceCardName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Operator] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ConsumeDate] [datetime] NULL,
	[Employee] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Department] [int] NULL,
	[IsPresent] [bit] NULL,
	[IsTime] [bit] NULL,
	[CreditMoney] [float] NULL,
	[BossCreditMoney] [float] NULL,
	[PayMentMoney] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[IsCash] [bit] NULL,
	[Remark] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_tb_ClientConsume] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否赠送' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_ClientConsume', @level2type=N'COLUMN', @level2name=N'IsPresent'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否计次' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_ClientConsume', @level2type=N'COLUMN', @level2name=N'IsTime'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'挂账' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_ClientConsume', @level2type=N'COLUMN', @level2name=N'CreditMoney'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'挂房账' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_ClientConsume', @level2type=N'COLUMN', @level2name=N'BossCreditMoney'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'付款方式及金额' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_ClientConsume', @level2type=N'COLUMN', @level2name=N'PayMentMoney'

第二个表结构

CREATE TABLE [dbo].[tb_ParticularDepot](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[NumberId] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[SupplierID] [int] NULL,
	[RegimentationID] [int] NULL,
	[ProductID] [int] NULL,
	[ProductName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Spec] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Unit] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Quantity] [int] NULL,
	[Price] [float] NULL,
	[TotalPrice] [float] NULL,
	[DepotID] [int] NULL,
	[DepotName] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[UDepotID] [int] NULL,
	[State] [int] NULL,
	[Operate] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[StockDate] [datetime] NULL,
	[DeptID] [int] NULL,
	[DeptName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Remark] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_tb_ParticularDepot_1] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

[lgxyz]

LZ的表达方式有待，提高。

[QiQi_yun]

[Quote=引用 15 楼 qianjin036a 的回复:]
SQL code(select DISTINCT count(Quantity) from tb_ParticularDepot where ProductID=a.ProductID and State=0 group by d.ProductID ) as '调拔数量',
--还得改,不然分组统计不对
-->
sum(select 1 from tb_ParticularDepot where ProductID=a.ProductID and State=0 group by d.ProductID ) as '调拔数量',
--不知道这样行不行.
[/Quote]
不对,数据不准确!

[wgzaaa]

安慰下，静下心来，就当提高你还是有收获的。

[QiQi_yun]

[Quote=引用 20 楼 lgxyz 的回复:]
LZ的表达方式有待，提高。
[/Quote]
其实我语文成绩很好的...

[QiQi_yun]

[Quote=引用 22 楼 wgzaaa 的回复:]
安慰下，静下心来，就当提高你还是有收获的。
[/Quote]
谢谢,过一下自己再看看认真想一下,
实在不行,去拍老大的桌子!

[utpcb]

[Quote=引用 23 楼 QiQi_yun 的回复:]
引用 20 楼 lgxyz 的回复:
LZ的表达方式有待，提高。

其实我语文成绩很好的...
[/Quote]
我们看来不是很好哈哈！

[QiQi_yun]

[Quote=引用 25 楼 utpcb 的回复:]
引用 23 楼 QiQi_yun 的回复:
引用 20 楼 lgxyz 的回复:
LZ的表达方式有待，提高。

其实我语文成绩很好的...

我们看来不是很好哈哈！
[/Quote]
嗯诺
我以后多加练习发贴

[a29dmj58k4]

路过

[tcxx2008]

看几了眼,头晕了,也支持下

[blueidea123]

呵呵，没天理啦，把老大over啦。

[QiQi_yun]

做个视图

[QiQi_yun]

select a.ProductID ,
a.ProductName,
a.Unit ,a.Spec  ,
sum(a.Quantity) as q1 ,
sum(b.Amount) as q3,
(select sum(Quantity) from tb_ParticularDepot as d where  d.ProductID = a.ProductID and State=0 group by d.ProductID)as q2
from tb_ParticularDepot as a ,
tb_ClientConsume as b
where a.ProductID=b.ItemID
and a.state=0 and a.Quantity>0
group by 
a.ProductID ,
a.ProductName,
a.Unit ,
a.Spec

[ilovewalk]

你第一个是想得到这样的吧:
SELECT COUNT(DISTINCT QUANTITY) FROM...

[QiQi_yun]

切都是

order by a.ProductID 

搞的错!汗犯了一超级错误

正确位置

select a.ProductID ,
a.ProductName,
a.Unit ,a.Spec  ,
sum(a.Quantity) as '期初数量' ,
sum(b.Amount) as '销售数量',
(select sum(Quantity) from tb_ParticularDepot as d where  d.ProductID = a.ProductID and State=0 group by d.ProductID)as '调拔数量'
from tb_ParticularDepot as a ,
tb_ClientConsume as b
where a.ProductID=b.ItemID
and a.state=0 and a.Quantity>0
group by 
a.ProductID ,
a.ProductName,
a.Unit ,
a.Spec
order by a.ProductID 

结论不可将

order by 放在 group by之前 

[结果]
不知道我这种结论对不对?

[fancydong]

不知道LZ要什么