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问题五:如果得到数据,数据的准确性如何?
硬是要得到以下数据
谢谢高手看看,我有一百分哦!
不过有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问题五:如果得到数据,数据的准确性如何?
问题一:子查询不对 SQL code
(select DISTINCT count(Quantity) from tb_ParticularDepot where ProductID=a.ProductID and State=0 group by d.ProductID ) as '调拔数量',錯誤1: d.ProductID
錯誤2: 子查詢可能返回多條紀錄
改
(select count(Quantity) from tb_ParticularDepot where ProductID=a.ProductID and State=0 group by d.ProductID ) as '调拔数量',
('期初数量-调拔数量-销售数量')as '期末数量'
-------------------------------------------
('期初数量 + 调拔数量-销售数量')as '期末数量'
a.ProductID ,
a.ProductName ,
a.Unit,
a.Spec
-->
group by a.ProductID,
a.ProductName ,
a.Unit,
a.Spec
a.ProductID , 重复了吧
--还得改,不然分组统计不对
-->
sum(select 1 from tb_ParticularDepot where ProductID=a.ProductID and State=0 group by d.ProductID ) as '调拔数量',
--不知道这样行不行.
[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,
[Re] [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,
[Re] [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]
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
SELECT COUNT(DISTINCT QUANTITY) FROM...
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之前 [结果]
不知道我这种结论对不对?