在查询中外部添加一个列 [英] Adding one more column externally in query

问题描述

我有以下查询：

WITH ctablee(mon, [<=15], [<=18], [<=20], [>20])
 AS 
(SELECT     *
 FROM         (SELECT     CONVERT(CHAR(4), [Data OUT (No Val#Vuoto)], 100) mon, [Gruppi Min (GG Flusso/Decorrenza-->Out)], 
                          Count([Gruppi Min (GG Flusso/Decorrenza-->Out)]) cnt
               FROM          dbpratiche
               WHERE      compagnia = 'GENERTEL'
               GROUP BY [Gruppi Min (GG Flusso/Decorrenza-->Out)], CONVERT(CHAR(4), [Data OUT (No Val#Vuoto)], 100)
               ) T

                   PIVOT
                    (
                    sum(cnt)
                     FOR [Gruppi Min (GG Flusso/Decorrenza-->Out)] IN ([<=15], [<=18], [<=20], [>20])) p
                     )
    SELECT     mon, isnull([<=15], 0) [<=15], isnull([<=18], 0) [<=18], isnull([<=20], 0) [<=20], isnull([>20], 0) [>20], isnull([<=15] + [<=18] + [<=20] + [>20], 0) Total   

    FROM         ctablee

这会导致：

>

通过以下查询添加一个列：

I wanted to add one more column in it which comes through following query:

select 
AVG([Min (GG Flusso/Decorrenza-->Out) Cal#] ) avrage
from dbo.dbPratiche
where Compagnia='GENERTEL'
and Stato='OUT ATTIVATA'
group by  convert(char(4),[Data OUT (No Val#Vuoto)],100)

：

>

我只想在第一个结果中使用此列（avrage）affter Total。

I just wanted to have this column (avrage) affter Total in first result.

如何将这两个查询

注意：两个查询生成的行相等。

Note: rows generated by both of the queries are equal.

推荐答案

我通过在内部连接表来做：

I done it by joining tables internally as:

WITH ctablee(mon, [<=15], [<=18], [<=20], [>20])
 AS 
(SELECT     *
 FROM         (SELECT     CONVERT(CHAR(4), [Data OUT (No Val#Vuoto)], 100) mon, [Gruppi Min (GG Flusso/Decorrenza-->Out)], 
                          Count([Gruppi Min (GG Flusso/Decorrenza-->Out)]) cnt
               FROM          dbpratiche
               WHERE      compagnia = 'GENERTEL'
               GROUP BY [Gruppi Min (GG Flusso/Decorrenza-->Out)], CONVERT(CHAR(4), [Data OUT (No Val#Vuoto)], 100)
               ) T

                   PIVOT
                    (
                    sum(cnt)
                     FOR [Gruppi Min (GG Flusso/Decorrenza-->Out)] IN ([<=15], [<=18], [<=20], [>20])) p
                     )


    SELECT    TB.mon, isnull(TB.[<=15], 0) [<=15], isnull(TB.[<=18], 0) [<=18], isnull(TB.[<=20], 0) [<=20], isnull(TB.[>20], 0) [>20], isnull(TB.[<=15] + TB.[<=18] + TB.[<=20] + TB.[>20], 0) Total   
    ,

   (
    select 
AVG(d.[Min (GG Flusso/Decorrenza-->Out) Cal#] ) avrage
from dbo.dbPratiche d,ctablee v
where 
convert(char(4),d.[Data OUT (No Val#Vuoto)],100)=TB.mon  and
d.Compagnia='GENERTEL'
and d.Stato='OUT ATTIVATA'
group by  convert(char(4),d.[Data OUT (No Val#Vuoto)],100)) as 'TK'
FROM         ctablee TB

这篇关于在查询中外部添加一个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！