34,594
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE [dbo].[Data_biao] (
[DJ_Date] datetime NULL DEFAULT (getdate()) ,
[DJ_Number] varchar(15) NOT NULL ,
[CheckDate] varchar(10) NULL ,
[GxID] varchar(10) NULL ,
[Info1] varchar(20) NULL ,
[Info2] varchar(20) NULL ,
[Info3] varchar(20) NULL ,
[Info4] varchar(20) NULL ,
[BZ] varchar(20) NULL ,
[LB] varchar(10) NULL ,
[JH_Money] decimal(18,2) NULL ,
[SJ_Money] decimal(18,2) NULL
)
GO
-- ----------------------------
-- Records of Data_biao
-- ----------------------------
INSERT INTO [dbo].[Data_biao] ([DJ_Date], [DJ_Number], [CheckDate], [GxID], [Info1], [Info2], [Info3], [Info4], [BZ], [LB], [JH_Money], [SJ_Money]) VALUES (N'2017-09-28 22:46:33.143', N'CK20170001', N'2017-09-28', N'001001', N'1', N'2', null, null, null, N'出库', N'176990.00', null)
GO
GO
INSERT INTO [dbo].[Data_biao] ([DJ_Date], [DJ_Number], [CheckDate], [GxID], [Info1], [Info2], [Info3], [Info4], [BZ], [LB], [JH_Money], [SJ_Money]) VALUES (N'2017-09-28 22:45:17.163', N'RK20170001', N'2017-09-28', N'001', N'1', N'2', N'', null, N'3', N'入库', N'120900.00', N'65900.00')
GO
GO
INSERT INTO [dbo].[Data_biao] ([DJ_Date], [DJ_Number], [CheckDate], [GxID], [Info1], [Info2], [Info3], [Info4], [BZ], [LB], [JH_Money], [SJ_Money]) VALUES (N'2017-09-28 22:45:54.500', N'RK20170002', N'2017-09-28', N'001', N'2', N'3', N'', null, N'4', N'入库', N'37400.00', N'12400.00')
GO
GO
-- ----------------------------
-- Indexes structure for table Data_biao
-- ----------------------------
CREATE INDEX [_WA_Sys_GxID_1FCDBCEB] ON [dbo].[Data_biao]
([GxID] ASC)
GO
CREATE INDEX [_WA_Sys_LB_1FCDBCEB] ON [dbo].[Data_biao]
([LB] ASC)
GO
CREATE INDEX [_WA_Sys_DJ_Date_1FCDBCEB] ON [dbo].[Data_biao]
([DJ_Date] ASC)
GO
CREATE INDEX [_WA_Sys_CheckDate_1FCDBCEB] ON [dbo].[Data_biao]
([CheckDate] ASC)
GO
-- ----------------------------
-- Primary Key structure for table Data_biao
-- ----------------------------
ALTER TABLE [dbo].[Data_biao] ADD PRIMARY KEY ([DJ_Number])
GO
CREATE TABLE [dbo].[Data_Log] (
[DJ_Number] varchar(15) NULL ,
[CL_Code] varchar(20) NULL ,
[SL] decimal(18,4) NULL ,
[SJ_Price] decimal(18,6) NULL ,
[SJ_Money] decimal(18,2) NULL ,
[JH_Price] decimal(18,6) NULL ,
[JH_Money] decimal(18,2) NULL ,
[SX] int NULL ,
[BZ] varchar(20) NULL ,
[Info] varchar(20) NULL ,
[ID] int NOT NULL IDENTITY(1,1)
)
GO
-- ----------------------------
-- Records of Data_Log
-- ----------------------------
SET IDENTITY_INSERT [dbo].[Data_Log] ON
GO
INSERT INTO [dbo].[Data_Log] ([DJ_Number], [CL_Code], [SL], [SJ_Price], [SJ_Money], [JH_Price], [JH_Money], [SX], [BZ], [Info], [ID]) VALUES (N'RK20170001', N'0101110155012', N'10.0000', N'2000.000000', N'20000.00', N'2500.000000', N'25000.00', N'1', null, null, N'80')
GO
GO
INSERT INTO [dbo].[Data_Log] ([DJ_Number], [CL_Code], [SL], [SJ_Price], [SJ_Money], [JH_Price], [JH_Money], [SX], [BZ], [Info], [ID]) VALUES (N'RK20170001', N'0101110171038', N'20.0000', N'1740.000000', N'34800.00', N'2740.000000', N'54800.00', N'2', null, null, N'81')
GO
GO
INSERT INTO [dbo].[Data_Log] ([DJ_Number], [CL_Code], [SL], [SJ_Price], [SJ_Money], [JH_Price], [JH_Money], [SX], [BZ], [Info], [ID]) VALUES (N'RK20170001', N'0101110175043', N'30.0000', N'370.000000', N'11100.00', N'1370.000000', N'41100.00', N'3', null, null, N'82')
GO
GO
INSERT INTO [dbo].[Data_Log] ([DJ_Number], [CL_Code], [SL], [SJ_Price], [SJ_Money], [JH_Price], [JH_Money], [SX], [BZ], [Info], [ID]) VALUES (N'RK20170002', N'0101110171038', N'10.0000', N'740.000000', N'7400.00', N'2740.000000', N'27400.00', N'1', null, null, N'83')
GO
GO
INSERT INTO [dbo].[Data_Log] ([DJ_Number], [CL_Code], [SL], [SJ_Price], [SJ_Money], [JH_Price], [JH_Money], [SX], [BZ], [Info], [ID]) VALUES (N'RK20170002', N'0000000000002', N'10.0000', N'500.000000', N'5000.00', N'1000.000000', N'10000.00', N'2', null, null, N'84')
GO
GO
INSERT INTO [dbo].[Data_Log] ([DJ_Number], [CL_Code], [SL], [SJ_Price], [SJ_Money], [JH_Price], [JH_Money], [SX], [BZ], [Info], [ID]) VALUES (N'CK20170001', N'0101110155012', N'11.0000', null, null, N'2500.000000', N'27500.00', N'1', null, null, N'85')
GO
GO
INSERT INTO [dbo].[Data_Log] ([DJ_Number], [CL_Code], [SL], [SJ_Price], [SJ_Money], [JH_Price], [JH_Money], [SX], [BZ], [Info], [ID]) VALUES (N'CK20170001', N'0101110171038', N'22.0000', null, null, N'2740.000000', N'60280.00', N'2', null, null, N'86')
GO
GO
CREATE TABLE [dbo].[CL_Dict] (
[Cl_Code] varchar(20) NOT NULL ,
[Name] varchar(40) NULL ,
[Type] varchar(40) NULL ,
[Dw] varchar(15) NULL ,
[LB_Code] varchar(15) NOT NULL ,
[InputPY] varchar(10) NULL ,
[Price] decimal(18,6) NULL DEFAULT (0) ,
[QCSl] decimal(18,4) NULL DEFAULT (0) ,
[QCje] decimal(18,2) NULL DEFAULT (0) ,
[Bz] varchar(20) NULL ,
[Rksl] decimal(18,4) NULL DEFAULT (0) ,
[Cksl] decimal(18,4) NULL DEFAULT (0)
)
-- ----------------------------
-- Records of CL_Dict
-- ----------------------------
INSERT INTO [dbo].[CL_Dict] ([Cl_Code], [Name], [Type], [Dw], [LB_Code], [InputPY], [Price], [QCSl], [QCje], [Bz], [Rksl], [Cksl]) VALUES (N'0000000000002', N'多用', N'test', N'克', N'002', null, N'1000.000000', N'10.0000', N'10000.00', null, N'10.0000', N'44.0000')
GO
GO
INSERT INTO [dbo].[CL_Dict] ([Cl_Code], [Name], [Type], [Dw], [LB_Code], [InputPY], [Price], [QCSl], [QCje], [Bz], [Rksl], [Cksl]) VALUES (N'0101110155012', N'轻轨', N'50-55Q 12kg', N'吨', N'001', N'', N'2500.000000', N'10.0000', N'25000.00', N'', N'10.0000', N'11.0000')
GO
GO
INSERT INTO [dbo].[CL_Dict] ([Cl_Code], [Name], [Type], [Dw], [LB_Code], [InputPY], [Price], [QCSl], [QCje], [Bz], [Rksl], [Cksl]) VALUES (N'0101110171038', N'重轨', N'U71-74 38kg', N'吨', N'001', N'', N'2740.000000', N'1.0000', N'2740.00', null, N'30.0000', N'22.0000')
GO
GO
INSERT INTO [dbo].[CL_Dict] ([Cl_Code], [Name], [Type], [Dw], [LB_Code], [InputPY], [Price], [QCSl], [QCje], [Bz], [Rksl], [Cksl]) VALUES (N'0101110175043', N'复用轨', N'43kg', N'吨', N'001', N'', N'1370.000000', N'5.0000', N'6850.00', null, N'30.0000', N'33.0000')
GO
GO
-- ----------------------------
-- Indexes structure for table CL_Dict
-- ----------------------------
CREATE INDEX [_WA_Sys_LB_Code_1DE57479] ON [dbo].[CL_Dict]
([LB_Code] ASC)
GO
CREATE INDEX [_WA_Sys_Type_76CBA758] ON [dbo].[CL_Dict]
([Type] ASC)
GO
CREATE INDEX [_WA_Sys_QCSl_76CBA758] ON [dbo].[CL_Dict]
([QCSl] ASC)
GO
CREATE INDEX [_WA_Sys_QCje_76CBA758] ON [dbo].[CL_Dict]
([QCje] ASC)
GO
-- ----------------------------
-- Primary Key structure for table CL_Dict
-- ----------------------------
ALTER TABLE [dbo].[CL_Dict] ADD PRIMARY KEY ([Cl_Code], [LB_Code])
GO
CREATE TABLE [dbo].[CLLB_Dict] (
[LB_Code] varchar(15) NOT NULL ,
[Name] varchar(24) NULL
)
GO
-- ----------------------------
-- Records of CLLB_Dict
-- ----------------------------
INSERT INTO [dbo].[CLLB_Dict] ([LB_Code], [Name]) VALUES (N'005', N'二类材料')
GO
GO
INSERT INTO [dbo].[CLLB_Dict] ([LB_Code], [Name]) VALUES (N'010', N'护品')
GO
GO
INSERT INTO [dbo].[CLLB_Dict] ([LB_Code], [Name]) VALUES (N'007', N'火工类')
GO
GO
INSERT INTO [dbo].[CLLB_Dict] ([LB_Code], [Name]) VALUES (N'012', N'矿粉')
GO
GO
INSERT INTO [dbo].[CLLB_Dict] ([LB_Code], [Name]) VALUES (N'006', N'木材类')
GO
GO
INSERT INTO [dbo].[CLLB_Dict] ([LB_Code], [Name]) VALUES (N'011', N'配件')
GO
GO
INSERT INTO [dbo].[CLLB_Dict] ([LB_Code], [Name]) VALUES (N'009', N'三类材料')
GO
GO
INSERT INTO [dbo].[CLLB_Dict] ([LB_Code], [Name]) VALUES (N'004', N'一类电线电缆')
GO
GO
INSERT INTO [dbo].[CLLB_Dict] ([LB_Code], [Name]) VALUES (N'002', N'一类非金属')
GO
GO
INSERT INTO [dbo].[CLLB_Dict] ([LB_Code], [Name]) VALUES (N'003', N'一类机电产品')
GO
GO
INSERT INTO [dbo].[CLLB_Dict] ([LB_Code], [Name]) VALUES (N'001', N'一类金属')
GO
GO
INSERT INTO [dbo].[CLLB_Dict] ([LB_Code], [Name]) VALUES (N'008', N'支护坑带')
GO
GO
INSERT INTO [dbo].[CLLB_Dict] ([LB_Code], [Name]) VALUES (N'013', N'租赁坑带')
GO
GO
-- ----------------------------
-- Indexes structure for table CLLB_Dict
-- ----------------------------
CREATE INDEX [_WA_Sys_Name_22AA2996] ON [dbo].[CLLB_Dict]
([Name] ASC)
GO
SELECT
a.lb_code,
SUM (b.qcje) AS 期初金额,
SUM (CASE WHEN lb = '入库' THEN 1 ELSE 0 END) AS 入库单据张数,
SUM (CASE WHEN lb = '入库' THEN c.jh_money ELSE 0 END) AS 入库金额,
SUM (CASE WHEN lb = '出库' THEN 1 ELSE 0 END) AS 出库单据张数,
SUM (CASE WHEN lb = '出库' THEN c.jh_money ELSE 0 eND) AS 出库金额
FROM
cllb_dict a
left JOIN cl_dict b ON a.lb_code = b.lb_code
LEFT JOIN data_log c ON c.cl_code = b.cl_code
LEFT JOIN data_biao d ON d.dj_number = c.dj_number
and (
(lb = '入库' OR lb = '出库')
AND dj_date BETWEEN '2017-9-01 00:00:00'
AND '2017-9-30 23:59:59'
)
GROUP BY
a.lb_code
ORDER BY
a.lb_code
;WITH cte AS (
SELECT distinct a.LB_Code,b.qcje,b.Cl_Code,
c.DJ_Number,c.JH_Money,
d.lb
FROM CLLB_Dict a
INNER JOIN CL_Dict b ON a.LB_Code=b.LB_Code
INNER JOIN Data_Log c ON b.Cl_Code=c.CL_Code
INNER JOIN Data_biao d ON c.DJ_Number=d.DJ_Number
),
cte2 AS(
SELECT DISTINCT lb_code,cl_code,qcje FROM cte
),
cte3 AS(
SELECT distinct lb_code,lb,DJ_Number,sum(JH_Money) OVER(PARTITION BY lb_code,lb) AS summoney FROM cte
),
cte4 AS (
SELECT distinct lb_code,lb,
count(1) over(partition by lb_code,lb) AS 单据张数,
summoney AS 金额
FROM cte3
),
cte5 AS (
SELECT DISTINCT LB_Code,SUM(qcje) AS qcje FROM cte2
GROUP BY lb_code
),
cte6 AS(
SELECT a.*,b.qcje FROM cte4 a
INNER JOIN cte5 b ON a.lb_code=b.lb_code
)
SELECT lb_code,
qcje AS 期初金额,
MAX(CASE lb WHEN '入库' THEN 单据张数 ELSE 0 END) 入库单据张数,
MAX(CASE lb WHEN '入库' THEN 金额 ELSE 0 END) 入库金额,
MAX(CASE lb WHEN '出库' THEN 单据张数 ELSE 0 END) 出库单据张数,
MAX(CASE lb WHEN '出库' THEN 金额 ELSE 0 END) 出库金额
FROM cte6
GROUP BY
lb_code,
qcje
SELECT
a.lb_code,a.name,
(select sum( QCje) from CL_Dict where LB_Code = a.LB_Code) AS 期初金额,
SUM (CASE WHEN lb = '入库' THEN 1 ELSE 0 END) AS 入库单据张数,
SUM (CASE WHEN lb = '入库' THEN c.jh_money ELSE 0 END) AS 入库金额,
SUM (CASE WHEN lb = '出库' THEN 1 ELSE 0 END) AS 出库单据张数,
SUM (CASE WHEN lb = '出库' THEN c.jh_money ELSE 0 eND) AS 出库金额,
(select sum( QCje) from CL_Dict where LB_Code = a.LB_Code)+SUM (CASE WHEN lb = '入库' THEN c.jh_money ELSE 0 END)-SUM (CASE WHEN lb = '出库' THEN c.jh_money ELSE 0 eND) as 期未金额
FROM
cllb_dict a
left JOIN cl_dict b ON a.lb_code = b.lb_code
LEFT JOIN data_log c ON c.cl_code = b.cl_code
LEFT JOIN data_biao d ON d.dj_number = c.dj_number
and (
(lb = '入库' OR lb = '出库')
AND dj_date BETWEEN '2017-9-01 00:00:00'
AND '2017-9-30 23:59:59'
)
GROUP BY
a.lb_code,a.name
ORDER BY
a.lb_code
CREATE TABLE [dbo].[Data_Log] (
[DJ_Number] varchar(15) NULL ,
[CL_Code] varchar(20) NULL ,
[SL] decimal(18,4) NULL ,
[SJ_Price] decimal(18,6) NULL ,
[SJ_Money] decimal(18,2) NULL ,
[JH_Price] decimal(18,6) NULL ,
[JH_Money] decimal(18,2) NULL ,
[SX] int NULL ,
[BZ] varchar(20) NULL ,
[Info] varchar(20) NULL ,
[ID] int NOT NULL IDENTITY(1,1)
)
GO
-- ----------------------------
-- Records of Data_Log
-- ----------------------------
SET IDENTITY_INSERT [dbo].[Data_Log] ON
GO
INSERT INTO [dbo].[Data_Log] ([DJ_Number], [CL_Code], [SL], [SJ_Price], [SJ_Money], [JH_Price], [JH_Money], [SX], [BZ], [Info], [ID]) VALUES (N'RK20170001', N'0101110155012', N'10.0000', N'2000.000000', N'20000.00', N'2500.000000', N'25000.00', N'1', null, null, N'80')
GO
GO
INSERT INTO [dbo].[Data_Log] ([DJ_Number], [CL_Code], [SL], [SJ_Price], [SJ_Money], [JH_Price], [JH_Money], [SX], [BZ], [Info], [ID]) VALUES (N'RK20170001', N'0101110171038', N'20.0000', N'1740.000000', N'34800.00', N'2740.000000', N'54800.00', N'2', null, null, N'81')
GO
GO
INSERT INTO [dbo].[Data_Log] ([DJ_Number], [CL_Code], [SL], [SJ_Price], [SJ_Money], [JH_Price], [JH_Money], [SX], [BZ], [Info], [ID]) VALUES (N'RK20170001', N'0101110175043', N'30.0000', N'370.000000', N'11100.00', N'1370.000000', N'41100.00', N'3', null, null, N'82')
GO
GO
INSERT INTO [dbo].[Data_Log] ([DJ_Number], [CL_Code], [SL], [SJ_Price], [SJ_Money], [JH_Price], [JH_Money], [SX], [BZ], [Info], [ID]) VALUES (N'RK20170002', N'0101110171038', N'10.0000', N'740.000000', N'7400.00', N'2740.000000', N'27400.00', N'1', null, null, N'83')
GO
GO
INSERT INTO [dbo].[Data_Log] ([DJ_Number], [CL_Code], [SL], [SJ_Price], [SJ_Money], [JH_Price], [JH_Money], [SX], [BZ], [Info], [ID]) VALUES (N'RK20170002', N'0000000000002', N'10.0000', N'500.000000', N'5000.00', N'1000.000000', N'10000.00', N'2', null, null, N'84')
GO
GO
INSERT INTO [dbo].[Data_Log] ([DJ_Number], [CL_Code], [SL], [SJ_Price], [SJ_Money], [JH_Price], [JH_Money], [SX], [BZ], [Info], [ID]) VALUES (N'CK20170001', N'0101110155012', N'11.0000', null, null, N'2500.000000', N'27500.00', N'1', null, null, N'85')
GO
GO
INSERT INTO [dbo].[Data_Log] ([DJ_Number], [CL_Code], [SL], [SJ_Price], [SJ_Money], [JH_Price], [JH_Money], [SX], [BZ], [Info], [ID]) VALUES (N'CK20170001', N'0101110171038', N'22.0000', null, null, N'2740.000000', N'60280.00', N'2', null, null, N'86')
GO
GO
INSERT INTO [dbo].[Data_Log] ([DJ_Number], [CL_Code], [SL], [SJ_Price], [SJ_Money], [JH_Price], [JH_Money], [SX], [BZ], [Info], [ID]) VALUES (N'CK20170001', N'0101110175043', N'33.0000', null, null, N'1370.000000', N'45210.00', N'3', null, null, N'87')
GO
GO
INSERT INTO [dbo].[Data_Log] ([DJ_Number], [CL_Code], [SL], [SJ_Price], [SJ_Money], [JH_Price], [JH_Money], [SX], [BZ], [Info], [ID]) VALUES (N'CK20170001', N'0000000000002', N'44.0000', null, null, N'1000.000000', N'44000.00', N'4', null, null, N'88')
GO
GO
SET IDENTITY_INSERT [dbo].[Data_Log] OFF
GO
-- ----------------------------
-- Indexes structure for table Data_Log
-- ----------------------------
CREATE INDEX [_WA_Sys_DJ_Number_22AA2996] ON [dbo].[Data_Log]
([DJ_Number] ASC)
GO
CREATE INDEX [_WA_Sys_CL_Code_22AA2996] ON [dbo].[Data_Log]
([CL_Code] ASC)
GO
-- ----------------------------
-- Primary Key structure for table Data_Log
-- ----------------------------
ALTER TABLE [dbo].[Data_Log] ADD PRIMARY KEY ([ID])
GO
SELECT A.LB_CODE,
SUM(CASE WHEN RN=1 THEN QCje ELSE 0 END) AS QCJE,
SUM(入库金额) AS 入库金额,
SUM(出库金额) AS 出库金额,
COUNT(DISTINCT(CASE WHEN LB='入库' THEN DJ_NUMBER ELSE NULL END)) AS 入库单据张数,
COUNT(DISTINCT(CASE WHEN LB='出库' THEN DJ_NUMBER ELSE NULL END)) AS 出库单据张数
FROM
(SELECT A.*,B.Cl_Code,B.QCje,C.DJ_Number,C.LB,C.出库金额,C.入库金额,
ROW_NUMBER() OVER (PARTITION BY A.LB_CODE,B.CL_CODE ORDER BY GETDATE()) AS RN
FROM CLLB_DICT A
JOIN CL_DICT B ON A.LB_CODE=B.LB_CODE
JOIN (SELECT CL_CODE,
A.DJ_NUMBER,
MAX(LB) AS LB,
SUM(CASE WHEN LB='入库' THEN B.JH_MONEY ELSE 0 END) AS 入库金额,
SUM(CASE WHEN LB='出库' THEN B.JH_MONEY ELSE 0 END) AS 出库金额
FROM DATA_BIAO A
JOIN DATA_LOG B ON A.DJ_NUMBER=B.DJ_NUMBER
WHERE (lb = '入库' OR lb = '出库')
AND dj_date BETWEEN '2017-9-01 00:00:00'
AND '2017-9-30 23:59:59'
GROUP BY CL_CODE,A.DJ_NUMBER) AS C ON B.CL_CODE=C.CL_CODE) AS A
GROUP BY A.LB_CODE
SELECT
a.lb_code,a.name,
(select sum( QCje) from CL_Dict where LB_Code = a.LB_Code) AS 期初金额,
SUM (CASE WHEN lb = '入库' THEN 1 ELSE 0 END) AS 入库单据张数,
SUM (CASE WHEN lb = '入库' THEN c.jh_money ELSE 0 END) AS 入库金额,
SUM (CASE WHEN lb = '出库' THEN 1 ELSE 0 END) AS 出库单据张数,
SUM (CASE WHEN lb = '出库' THEN c.jh_money ELSE 0 eND) AS 出库金额,
(select sum( QCje) from CL_Dict where LB_Code = a.LB_Code)+SUM (CASE WHEN lb = '入库' THEN c.jh_money ELSE 0 END)-SUM (CASE WHEN lb = '出库' THEN c.jh_money ELSE 0 eND) as 期未金额
FROM
cllb_dict a
left JOIN cl_dict b ON a.lb_code = b.lb_code
LEFT JOIN data_log c ON c.cl_code = b.cl_code
LEFT JOIN data_biao d ON d.dj_number = c.dj_number
and (
(lb = '入库' OR lb = '出库')
AND dj_date BETWEEN '2017-9-01 00:00:00'
AND '2017-9-30 23:59:59'
)
GROUP BY
a.lb_code,a.name
ORDER BY
a.lb_code
[/quote]
你上面自己的写法,期初金额也是不对的,还有单据张数也不对,逻辑绕来绕去代码怎么写都是这样,多不了多少,也少不了多少