22,207
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE [dbo].[订单](
[ID] [int] IDENTITY(1,1) NOT NULL,
[单号] [nchar](10) NULL,
) ON [PRIMARY]
CREATE TABLE [dbo].[订单分录](
[ID] [int] IDENTITY(1,1) NOT NULL,
[单号] [nchar](10) NULL,
[营业点] [nchar](10) NULL,
[商品分类] [nchar](10) NULL,
[商品名] [nchar](10) NULL,
) ON [PRIMARY]
CREATE TABLE [dbo].[收款分录](
[ID] [int] IDENTITY(1,1) NOT NULL,
[单号] [nchar](10) NULL,
[付款方式] [nchar](10) NULL,
[金额] [nchar](10) NULL,
) ON [PRIMARY]
INSERT INTO [订单] ([单号])
VALUES ('2001')
INSERT INTO [订单分录] ([单号],[营业点],[商品分类] ,[商品名])
VALUES (2001,'1号','服装','上衣')
INSERT INTO [订单分录] ([单号],[营业点],[商品分类] ,[商品名])
VALUES (2001,'1号','服装','裤子')
INSERT INTO [订单分录] ([单号],[营业点],[商品分类] ,[商品名])
VALUES (2001,'1号','服装','帽子')
INSERT INTO [收款分录] ([单号],[付款方式] ,[金额])
VALUES (2001,'现金',50)
INSERT INTO [收款分录] ([单号],[付款方式] ,[金额])
VALUES (2001,'现金',82)
select --distinct
a.单号,a.商品分类,a.商品名,b.付款方式,b.金额
from
订单分录 a --inner join 订单 c on a.单号=c.单号
left join 收款分录 b on a.单号=b.单号 and
delete from [订单分录]
INSERT INTO [订单分录] ([单号],[营业点],[商品分类] ,[商品名])
VALUES (2001,'1号','服装','上衣')
INSERT INTO [订单分录] ([单号],[营业点],[商品分类] ,[商品名])
VALUES (2001,'1号','服装','裤子')
INSERT INTO [订单分录] ([单号],[营业点],[商品分类] ,[商品名])
VALUES (2001,'1号','服装','帽子')
select a.[营业点],b.[付款方式],sum(b.[金额]) [总金额]
from [订单] a join [收款分录] b on a.[营业点] = b.[营业点]
group by a.[营业点],b.[付款方式]
SELECT a.营业点,a.商品分类,SUM(CAST(b.金额 AS int)) 汇总金额 FROM [订单分录] a,[收款分录] b WHERE a.ID=b.ID GROUP BY a.营业点,a.商品分类