Sql语句按月统计与销售公司组合的综合统计, 希望大家可以给小弟我帮助,
Sql语句按月统计与销售公司组合的综合统计, 希望大家可以给我帮助,在线等。。。
use master
Create database [Test_DB]
go
USE [Test_DB]
GO
/****** 对象: Table [dbo].[business_SaleDirectOrderDetail]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--单位表
CREATE TABLE [dbo].[bs_CompanyInfo](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CustomerID] [int] NULL,
[CompanyCode] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_bs_CompanyInfo_CompanyCode] DEFAULT (''),
[SimpleName] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[FullName] [nvarchar](200) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_bs_CompanyInfo] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bs_CompanyInfo', @level2type=N'COLUMN',@level2name=N'CustomerID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'公司编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bs_CompanyInfo', @level2type=N'COLUMN',@level2name=N'CompanyCode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'公司简称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bs_CompanyInfo', @level2type=N'COLUMN',@level2name=N'SimpleName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'公司全称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bs_CompanyInfo', @level2type=N'COLUMN',@level2name=N'FullName'
go
--插入数据
insert into [bs_CompanyInfo] values(1,'No001','test1','Test1 Company')
insert into [bs_CompanyInfo] values(1,'No002','test2','Test2 Company')
insert into [bs_CompanyInfo] values(1,'No003','test3','Test3 Company')
--产品表
CREATE TABLE [dbo].[bs_Products](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CategoryID] [int] NULL,
[TrademarkID] [int] NULL,
[CatenaID] [int] NULL,
[Barcode] [nvarchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[ProductCode] [nvarchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[Name_CHS] [nvarchar](100) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_bs_Products] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'所属大类ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bs_Products', @level2type=N'COLUMN',@level2name=N'CategoryID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'所属品牌ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bs_Products', @level2type=N'COLUMN',@level2name=N'TrademarkID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'产品系列ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bs_Products', @level2type=N'COLUMN',@level2name=N'CatenaID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'产品编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bs_Products', @level2type=N'COLUMN',@level2name=N'ProductCode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'中文名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bs_Products', @level2type=N'COLUMN',@level2name=N'Name_CHS'
--插入数据
insert into [bs_Products] values(1,1,1,'N0001','No000001','产品一')
insert into [bs_Products] values(2,2,1,'N0002','No000002','产品二')
insert into [bs_Products] values(1,1,1,'N0003','No000003','产品三')
insert into [bs_Products] values(1,1,1,'N0004','No000004','产品四')
insert into [bs_Products] values(1,1,1,'N0005','No000005','产品五')
insert into [bs_Products] values(2,2,1,'N0006','No000006','产品六')
insert into [bs_Products] values(1,1,1,'N0007','No000007','产品七')
insert into [bs_Products] values(1,1,1,'N0008','No000008','产品八')
GO
--出库单
CREATE TABLE [dbo].[business_SaleDirectOrder](
[OrderCode] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Sale_OrderCode] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[CompanyId] [int] NULL,
[RegionID] [int] NULL,
[WarehouseId] [int] NULL,
[LargeCategoryId] [int] NULL,