我有一个 sql 查询,其中结果表的列是一年中的月份:1 月 - 12 月。
DECLARE @Ventas MONEY
declare @i int
declare @ano int
declare @mes int
set @ano = 2016
set @mes = 12
set @i = 1
WHILE @i <= @mes
BEGIN
set @Ventas = (SELECT SUM(LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT) BALANCE
FROM LEDGER_ACCOUNT AS LAC, LEDGER_ACCOUNT_BALANCE AS LAB
WHERE YEAR(LAB.POSTING_DATE)= @ano
and MONTH(LAB.POSTING_DATE)= @i
and LAC.ACCOUNT_ID = LAB.ACCOUNT_ID
AND LAB.CURRENCY_ID = 'NIO'
AND (LAC.ACCOUNT_ID LIKE '43' + '%'))
SELECT '43' CUENTA, 'Ventas' DESCRIPTION, @Ventas BALANCE , @Ventas/@Ventas PER
set @i = @i + 1;
END;
它把我扔了十二个月,但在单独的表中,我希望它们连接在一行中:
在它们出现在一行之前,我做了另一个,但是我在“PER”列上遇到了问题(我无法安装它):
SELECT '43' CUENTA, 'Ventas' DESCRIP ,
SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 1 THEN
LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) ENERO,
SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 2 THEN
LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) FEBRERO ,
SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 3 THEN
LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) MARZO ,
SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 4 THEN
LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) ABRIL ,
SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 5 THEN
LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) MAYO ,
SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 6 THEN
LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) JUNIO ,
SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 7 THEN
LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) JULIO ,
SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 8 THEN
LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) AGOSTO ,
SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 9 THEN
LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) SEPTIEMBRE ,
SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 10 THEN
LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) OCTUBRE ,
SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 11 THEN
LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) NOVIEMBRE ,
SUM(CASE MONTH(LAB.POSTING_DATE) WHEN 12 THEN
LAB.CREDIT_AMOUNT - LAB.DEBIT_AMOUNT ELSE 0 END) DICIEMBRE
FROM LEDGER_ACCOUNT AS LAC, LEDGER_ACCOUNT_BALANCE AS LAB
WHERE YEAR(LAB.POSTING_DATE)= @ano
and LAC.ACCOUNT_ID = LAB.ACCOUNT_ID
AND LAB.CURRENCY_ID = 'NIO'
AND (LAC.ACCOUNT_ID LIKE '43' + '%')
我得到这样的东西,我需要一年中每个月的 PER(销售额/销售额)列,我们是 100,这只是一个例子,还有更多 PER 需要计算……销售额/销售额只是一个异常的例子。
一种简单的方法,并且允许您在一个查询中完成所有操作,是使用公共表表达式和您已有的 SQL: