首页新闻找找看学习计划

sql查询拼接语句

0
悬赏园豆:20 [已关闭问题] 关闭于 2019-01-30 11:45

有一张车辆计量表,字段有客户,车号,计量点,计量日期,重量,物料
想要的结果是根据输入的起止日期和物料得到每一个客户在起止日期内在各个不同计量点的统计信息.总共9个计量点,
结果:
客户A,物料,计量点1{车数,重量合计},计量点2{车数,重量合计}.......计量点9{车数,重量合计}

问题补充:

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

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

wavegui的主页 wavegui | 初学一级 | 园豆:80
提问于:2019-01-30 09:15
< >
分享
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册