• 闪存
• 博客
• 发言 小组
• 投递 新闻
• 提问 博问
• 添加 收藏
• 文库

# sql查询拼接语句

0 悬赏园豆：20 [已关闭问题] 关闭于 2019-01-30 11:45 CREATE PROC p_GetPoundStatisticsDetails
(@StarDate datetime,
@EndDate datetime,
@MaterialCode varchar(10))
as
begin
DROP TABLE #TEMPLIST

``````    CREATE TABLE #TEMPlIST(@CustomerName varchar(50) PRIMARY KEY NOT NULL,@MCode varchar(50),@SumCarCount DECIMAL(18,2),@SSumNetWeight DECIMAL(18,2),
@OneCarCount DECIMAL(18,2),@OneNetWeight DECIMAL(18,2),
@TwoCarCount DECIMAL(18,2),@TwoNetWeight DECIMAL(18,2),
@ThreeCarCount DECIMAL(18,2),@ThreeNetWeight DECIMAL(18,2),
@FourCarCount DECIMAL(18,2),@FourNetWeight DECIMAL(18,2),
@FiveCarCount DECIMAL(18,2),@FiveNetWeight DECIMAL(18,2),
@SixCarCount DECIMAL(18,2),@SixNetWeight DECIMAL(18,2),
@SevenCarCount DECIMAL(18,2),@SevenNetWeight DECIMAL(18,2),
@EightCarCount DECIMAL(18,2),@EightNetWeight DECIMAL(18,2),
@NineCarCount DECIMAL(18,2),@NineNetWeight DECIMAL(18,2))

DECLARE @CUC int
DECLARE @Flag int
set @Flag=0
SELECT @CUC=COUNT(*) FROM Base_Customers WHERE DeleteMark=1 AND IsFinancial=1
FOR( @Flag < @CUC)
BEGIN
INSERT INTO #TEMPLIST(CustomerName,MaterialName,SCCount,SNW,ACCount,ANW,BCCOUNT,BNW,CCCOUNT,CNW,DCCOUNT,DNW,ECCOUNT,ENW,FCCOUNT,FNW,GCCOUNT,GNW,HCCOUNT,HNW,JCCOUNT,JNW)
SELECT dbo.f_GetCustomerNameByCustomerId(CustomerId) as CustomerName,dbo.f_GetMaterialNameByMaterialCode(MaterialCode) as MaterialName,
(SELECT Count(CarNumber) FROM Base_PoundRecord WHERE CRState IN(8,9) AND (SaveDate BetWeen @StarDate+' 08:00:00' and @EndDate+' 07:59:59')) as SumCarCount,
(SELECT ISNULL(SUM(NETWEIGHT/1000),0) FROM Base_PoundRecord WHERE CRState IN(8,9) AND (SaveDate BetWeen @StarDate+' 08:00:00' and @EndDate+' 07:59:59')) as SumNetWeight,
(SELECT Count(CarNumber) FROM Base_PoundRecord WHERE CRState IN(8,9) AND (SaveDate BetWeen @StarDate+' 08:00:00' and @EndDate+' 07:59:59') AND PoundName='1号磅') as OneCarCount,
(SELECT ISNULL(SUM(NETWEIGHT/1000),0) FROM Base_PoundRecord WHERE CRState IN(8,9) AND (SaveDate BetWeen @StarDate+' 08:00:00' and @EndDate+' 07:59:59') AND PoundName='1号磅') as OneNetWeight,
(SELECT Count(CarNumber) FROM Base_PoundRecord WHERE CRState IN(8,9) AND (SaveDate BetWeen @StarDate+' 08:00:00' and @EndDate+' 07:59:59') AND PoundName='2号磅') as TwoCarCount,
(SELECT ISNULL(SUM(NETWEIGHT/1000),0) FROM Base_PoundRecord WHERE CRState IN(8,9) AND (SaveDate BetWeen @StarDate+' 08:00:00' and @EndDate+' 07:59:59') AND PoundName='2号磅') as TwoNetWeight,
(SELECT Count(CarNumber) FROM Base_PoundRecord WHERE CRState IN(8,9) AND (SaveDate BetWeen @StarDate+' 08:00:00' and @EndDate+' 07:59:59') AND PoundName='3号磅') as ThreeCarCount,
(SELECT ISNULL(SUM(NETWEIGHT/1000),0) FROM Base_PoundRecord WHERE CRState IN(8,9) AND (SaveDate BetWeen @StarDate+' 08:00:00' and @EndDate+' 07:59:59') AND PoundName='3号磅') as ThreeNetWeight,
(SELECT Count(CarNumber) FROM Base_PoundRecord WHERE CRState IN(8,9) AND (SaveDate BetWeen @StarDate+' 08:00:00' and @EndDate+' 07:59:59') AND PoundName='4号磅') as FourCarCount,
(SELECT ISNULL(SUM(NETWEIGHT/1000),0) FROM Base_PoundRecord WHERE CRState IN(8,9) AND (SaveDate BetWeen @StarDate+' 08:00:00' and @EndDate+' 07:59:59') AND PoundName='4号磅') as FourNetWeight,
(SELECT Count(CarNumber) FROM Base_PoundRecord WHERE CRState IN(8,9) AND (SaveDate BetWeen @StarDate+' 08:00:00' and @EndDate+' 07:59:59') AND PoundName='5号磅') as FiveCarCount,
(SELECT ISNULL(SUM(NETWEIGHT/1000),0) FROM Base_PoundRecord WHERE CRState IN(8,9) AND (SaveDate BetWeen @StarDate+' 08:00:00' and @EndDate+' 07:59:59') AND PoundName='5号磅') as FiveNetWeight,
(SELECT Count(CarNumber) FROM Base_PoundRecord WHERE CRState IN(8,9) AND (SaveDate BetWeen @StarDate+' 08:00:00' and @EndDate+' 07:59:59') AND PoundName='6号磅') as SixCarCount,
(SELECT ISNULL(SUM(NETWEIGHT/1000),0) FROM Base_PoundRecord WHERE CRState IN(8,9) AND (SaveDate BetWeen @StarDate+' 08:00:00' and @EndDate+' 07:59:59') AND PoundName='6号磅') as SixNetWeight,
(SELECT Count(CarNumber) FROM Base_PoundRecord WHERE CRState IN(8,9) AND (SaveDate BetWeen @StarDate+' 08:00:00' and @EndDate+' 07:59:59') AND PoundName='7号磅') as SevenCarCount,
(SELECT ISNULL(SUM(NETWEIGHT/1000),0) FROM Base_PoundRecord WHERE CRState IN(8,9) AND (SaveDate BetWeen @StarDate+' 08:00:00' and @EndDate+' 07:59:59') AND PoundName='7号磅') as SevenNetWeight,
(SELECT Count(CarNumber) FROM Base_PoundRecord WHERE CRState IN(8,9) AND (SaveDate BetWeen @StarDate+' 08:00:00' and @EndDate+' 07:59:59') AND PoundName='8号磅') as EightCarCount,
(SELECT ISNULL(SUM(NETWEIGHT/1000),0) FROM Base_PoundRecord WHERE CRState IN(8,9) AND (SaveDate BetWeen @StarDate+' 08:00:00' and @EndDate+' 07:59:59') AND PoundName='8号磅') as EightNetWeight,
(SELECT Count(CarNumber) FROM Base_PoundRecord WHERE CRState IN(8,9) AND (SaveDate BetWeen @StarDate+' 08:00:00' and @EndDate+' 07:59:59') AND PoundName='9号磅') as NineCarCount,
(SELECT ISNULL(SUM(NETWEIGHT/1000),0) FROM Base_PoundRecord WHERE CRState IN(8,9) AND (SaveDate BetWeen @StarDate+' 08:00:00' and @EndDate+' 07:59:59') AND PoundName='9号磅') as NineNetWeight
FROM Base_PoundRecord
WHERE CustomerId=@CustomerId AND MaterialCode=@MaterialCode
group by CustomerId,MaterialCode
SET @A=@A+1
END
SELECT * FROM #TEMPLIST
DROP TABLE #TEMPLIST
END``````

您需要登录以后才能回答，未注册用户请先注册