sql查询拼接语句

2019-08-09 17:08:42

有一张车辆计量表，字段有客户，车号，计量点，计量日期，重量，物料

想要的结果是根据输入的起止日期和物料得到每一个客户在起止日期内在各个不同计量点的统计信息．总共９个计量点，

结果：

客户Ａ，物料，计量点1{车数，重量合计},计量点2{车数，重量合计}.......计量点9{车数，重量合计}

(@StarDate datetime,

@EndDate datetime,

@MaterialCode varchar(10))

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

现在问题是在循环之前如何把@CustomerId得到，然后这样写能否达到我要的效果

分享好友

分享这个小栈给你的朋友们，一起进步吧。

SQL Server专区

创建时间：2020-05-14 14:17:02

SQL Server 是Microsoft 公司推出的关系型数据库管理系统

展开

订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅：虚拟交易，一经交易不退款；若特殊情况，可3日内客服咨询

• 专区发布评论属默认订阅所评论专区（除付费小栈外）

技术专家

查看更多

小雨滴
专家