sql查询拼接语句
0
悬赏园豆：20
[已关闭问题]
关闭于 2019-01-30 11:45
有一张车辆计量表，字段有客户，车号，计量点，计量日期，重量，物料
想要的结果是根据输入的起止日期和物料得到每一个客户在起止日期内在各个不同计量点的统计信息．总共９个计量点，
结果：
客户Ａ，物料，计量点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得到，然后这样写能否达到我要的效果
