如何为分组构建复合字段（即2个字段） [英] how do build a composite field for grouping (ie 2 fields)
本文介绍了如何为分组构建复合字段（即2个字段）的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
需要< B > join < / B > 和< B > 组< / B > < B > ms sql server存储过程< / B >
即
1. AIDNO <
2. <跨越ss =code-attribute> ALOANUMBER<
来自 2 tables ＃< B > VLOANDED < / B > 和＃< B > VTRANS < / B >
INSERT INTO #VLOANDED
SELECT A.IDNO AS AIDNO，
A.LOANUMBER AS ALOANUMBER，
C.CUSTOMER AS ACUSTOMER，
A.PERIOD AS APERIOD，
A.YEAR1 AS AYEAR1，
A.PRINR_PMT AS APRINR_PMT，
A.INTR_PMT AS AINTR_PMT，
A.TX_TYPE AS ATX_TYPE，
A.PAY_AMT AS APAY_AMT，
A.TX_DATE AS ATX_DATE，
A.TY_DATE AS ATY_DATE，
A.AC_BALANCE AS AAC_BALANCE，
A.INT_NO AS AINT_NO，
C.BANKCD AS ABANKCD，
C.BANKNAME AS ABANKNAME
。从LOANDED A JOIN贷款C
ON A.IDNO = C.IDNO和A.LOANUMBER = C.LOANUMBER
WHERE A.YEAR1 * 1200 + A.PERIOD < = @ CYEAR * 1200 + @ CMONTH
INSERT INTO #VTRANS
SELECT LOAN_TRANS.IDNO AS BIDNO ，
< span class =code-attribute> LOAN_TRANS.CUSTOMER AS BCUSTOMER ，
< span class =code-attribute> LOAN_TRANS.ID_CODE AS BLOANUMBER，
< span class =code-attribute> LOAN_TRANS.TRANS_NO AS BTRANS_NO ，
< span class =code-attribute> LOAN_TRANS.PERIOD AS BPERIOD，
LOAN_TRANS.YEAR1 < span class =code-attribute> AS BYEAR1，
LOAN_TRANS.AMOUNT AS BAMOUNT，
LOAN_TRANS.TRANS_DATE AS BTRANS_DATE，
LOAN_TRANS.TRANS_TYPE AS BTANS_TYPE，
L. OAN_TRANS.DESCPN < span class =code-attribute> AS BDESCPN
< span class =code-attribute> FROM LOAN_TRANS
WHERE LOAN_TRANS.YEAR1 * 1200 + PERIOD< = @ CYEAR * 1200 + @ CMONTH
已尝试 此
SELECT ＃VLOANDED.AIDNO ，
#VLOANDED.ALOANUMBER ，
< span class =code-attribute> SUM（＃VLOANDED.APRINR_PMT） AS SUM_PRIN，
SUM（＃VLOANDED.AINTR_PMT） AS SUM_INT ，
SUM（#VTRANS.BAMOUNT < span class =code-attribute> AS SUM_AMT
< span class =code-attribute> FROM ＃VLOANDED JOIN #VTRANS
ON ＃VLOANDED.AIDNO = ＃VTRANS.BIDNO AND < span class =code-attribute> ＃VLOANDED.ALOANUMBER = ＃VTRANS.BLOANUMBER
GROUP ON （＃VLOANDED.AIDNO +＃VLOANDED.ALOANUMBER）
请 帮助 正确 group ing GROUP ON （＃VLOANDED.AIDNO +＃VLOANDED.ALOANUMBER）
so no space in （＃VLOANDED.AIDNO +＃VLOANDED.ALOANUMBER）
< span class =code-attribute> in short 如何 做 你 trim group 2 字段。
谢谢
解决方案
这似乎是你的另一个问题的前哨。
同样答案：
您只需要修剪两件。
SELECT AIDNO，RTRIM（LTRIM（ALOANNUMBER））+ RTRIM（LTRIM（AIDNO） ） AS LOAM
FROM VLOANS
尝试：
SELECT
＃VLOANDED.AIDNO，
＃VLOANDED.AL OANUMBER，
SUM（＃VLOANDED.APRINR_PMT） AS SUM_PRIN，
SUM（＃VLOANDED.AINTR_PMT） AS SUM_INT，
SUM（#VTRANS.BAMOUNT） AS SUM_AMT
FROM
#VLOANDED
JOIN #VTRANS
ON #VLOANDED.AIDNO =＃VTRANS.BIDNO
AND ＃VLOANDED.ALOANUMBER =＃VTRANS.BLOANUMBER
GROUP BY
＃VLOANDED.AIDNO，
＃VLOANDED.ALOANUMBER
;
Need to <B>join </B> and <B>group </B>on 2 fields in a <B>ms sql server stored procedure</B>
ie
1. AIDNO<
2. ALOANUMBER<
from 2 tables #<B>VLOANDED </B> and #<B>VTRANS</B>
INSERT INTO #VLOANDED
SELECT A.IDNO AS AIDNO ,
A.LOANUMBER AS ALOANUMBER ,
C.CUSTOMER AS ACUSTOMER ,
A.PERIOD AS APERIOD ,
A.YEAR1 AS AYEAR1 ,
A.PRINR_PMT AS APRINR_PMT,
A.INTR_PMT AS AINTR_PMT,
A.TX_TYPE AS ATX_TYPE ,
A.PAY_AMT AS APAY_AMT,
A.TX_DATE AS ATX_DATE,
A.TY_DATE AS ATY_DATE ,
A.AC_BALANCE AS AAC_BALANCE,
A.INT_NO AS AINT_NO,
C.BANKCD AS ABANKCD,
C.BANKNAME AS ABANKNAME
FROM LOANDED A JOIN LOANS C
ON A.IDNO = C.IDNO AND A.LOANUMBER = C.LOANUMBER
WHERE A.YEAR1*1200+A.PERIOD<=@CYEAR*1200+@CMONTH
INSERT INTO #VTRANS
SELECT LOAN_TRANS.IDNO AS BIDNO ,
LOAN_TRANS.CUSTOMER AS BCUSTOMER ,
LOAN_TRANS.ID_CODE AS BLOANUMBER,
LOAN_TRANS.TRANS_NO AS BTRANS_NO ,
LOAN_TRANS.PERIOD AS BPERIOD,
LOAN_TRANS.YEAR1 AS BYEAR1,
LOAN_TRANS.AMOUNT AS BAMOUNT,
LOAN_TRANS.TRANS_DATE AS BTRANS_DATE,
LOAN_TRANS.TRANS_TYPE AS BTANS_TYPE,
LOAN_TRANS.DESCPN AS BDESCPN
FROM LOAN_TRANS
WHERE LOAN_TRANS.YEAR1*1200+PERIOD<=@CYEAR*1200+@CMONTH
Tried this
SELECT #VLOANDED.AIDNO ,
#VLOANDED.ALOANUMBER ,
SUM(#VLOANDED.APRINR_PMT) AS SUM_PRIN,
SUM(#VLOANDED.AINTR_PMT) AS SUM_INT ,
SUM(#VTRANS.BAMOUNT AS SUM_AMT
FROM #VLOANDED JOIN #VTRANS
ON #VLOANDED.AIDNO =#VTRANS.BIDNO AND #VLOANDED.ALOANUMBER =#VTRANS.BLOANUMBER
GROUP ON (#VLOANDED.AIDNO+#VLOANDED.ALOANUMBER)
Please help correct the grouping GROUP ON (#VLOANDED.AIDNO+#VLOANDED.ALOANUMBER)
so that there is no space in between (#VLOANDED.AIDNO+#VLOANDED.ALOANUMBER)
in short how do you trim the group with 2 fields.
Thanks 解决方案
This appears to be a prepost of your other question.
Same answer:
You just need to trim both pieces.
SELECT AIDNO, RTRIM(LTRIM(ALOANNUMBER)) + RTRIM(LTRIM(AIDNO)) AS LOAM FROM VLOANS
Try:
SELECT #VLOANDED.AIDNO, #VLOANDED.ALOANUMBER, SUM(#VLOANDED.APRINR_PMT) AS SUM_PRIN, SUM(#VLOANDED.AINTR_PMT) AS SUM_INT, SUM(#VTRANS.BAMOUNT) AS SUM_AMT FROM #VLOANDED JOIN #VTRANS ON #VLOANDED.AIDNO = #VTRANS.BIDNO AND #VLOANDED.ALOANUMBER = #VTRANS.BLOANUMBER GROUP BY #VLOANDED.AIDNO, #VLOANDED.ALOANUMBER ;
这篇关于如何为分组构建复合字段（即2个字段）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
