子查询不会返回值 [英] Sub Queries wont return Value

问题描述

Hello all



我很难尝试获取查询以获取我想要的数据。 select中的其他字段显示正常，除了那些应该来自Sub查询的字段。



非常感谢提前！



以下是代码：

Hello all

I'm having a hard time trying to get a query to bring the data i want. The rest of the fields in the select show just fine except for those that should come from the Sub queries.

Many thanks in advance!

Here is the code:

with cte as  
 (select 
  rt.TelID as 'Telefono',  
  td.Descr as 'Tipo', cd.Descr As 'Categoria', 
  e.codigo+' - '+e.Nombre as 'Empleado', 
  t.RentBas as 'Renta', 
 (select distinct  sum(MPagar) from TelefonoFactu rt1   where ANO = 2012   and MES = 6 - 3   
  AND rt1.telid IN (Select TelID from Telefonos where rt1.TelID = TelID) GROUP BY rt1.TelId, rt1.MPagar) as 'M3', 	
 (select distinct sum(MPagar) from TelefonoFactu rt2   where ANO =  2012   and MES = 6 - 2  AND rt2.TelID = rt.TelID
  AND rt2.telid IN (Select TelID from Telefonos where rt2.TelID = TelID) GROUP BY TelId) as 'M2',  
   (select distinct sum(MPagar) from TelefonoFactu rt3 where ANO =  2012   and MES = 6 - 1  AND rt3.TelID = rt.TelID
  AND rt3.telid IN (Select TelID from Telefonos where rt3.TelID = TelID) GROUP BY TelId) as 'M1'
 ,sum(MPagar) as 'MActual'
  FROM TelefonoFactu rt
  INNER JOIN Telefonos t on (t.TelID = rt.telID and t.[status] = 1)
  INNER JOIN TipoDevice td on (td.TipoID = t.TipoID)
  INNER JOIN CategoriaDevice cd on (cd.CategID = t.CategID) 
  LEFT OUTER JOIN empleados e on (e.codigo = t.CodAsig)
where ANO =  YEAR(GETDATE()) and Mes = 6 

AND (t.TipoID like  'TD001')
AND (t.CategID LIKE 'CC001')

  Group by  rt.TelID,  
  td.Descr , cd.Descr , 
  e.codigo+' - '+e.Nombre, 
  t.RentBas)
  
  select Telefono,  
  Tipo, Categoria, 
  Empleado, 
  Renta, 
   CASE M3 WHEN null  THEN 0 END AS M3,
  --M3,
  CASE M2 WHEN null  THEN 0 END AS M2, 	
  --M2, 
  CASE M1 WHEN null  THEN 0 END AS M1, 
 -- M1,
  MActual,
 (MActual - M1) / MActual As 'V1M',
 (M1 + M2 + M3) As 'T3M',
 (M1 + M2 + M3) / 3 As 'P3M',
 (MActual - ((M1 + M2 + M3) / 3)) / ((M1 + M2 + M3) / 3) AS 'V3M'
FROM cte

------------->这是表结构

-------------> This is the Table structure

USE [Portal]
GO

/****** Object:  Table [dbo].[TelefonoFactu]    Script Date: 07/16/2013 16:50:35 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[TelefonoFactu](
	[TelID] [nvarchar](10) NOT NULL,
	[Ano] [int] NOT NULL,
	[Mes] [int] NOT NULL,
	[Fecha] [smalldatetime] NOT NULL,
	[MItbis] [decimal](19, 2) NULL,
	[MFactura] [decimal](19, 2) NULL,
	[MPagar] [decimal](19, 2) NULL,
	[MDbCr] [decimal](19, 2) NULL,
	[Nota] [nvarchar](200) NULL,
	[FechaCrea] [smalldatetime] NULL,
	[FechaMod] [smalldatetime] NULL,
	[UserCrea] [nvarchar](20) NULL,
	[UserMod] [nvarchar](20) NULL,
	[NCF] [varchar](19) NULL,
	[MISC] [decimal](19, 2) NULL,
	[MCargoAtraso] [decimal](19, 2) NULL,
	[Modificado] [varchar](1) NOT NULL,
 CONSTRAINT [PK_TelefonoFactu] PRIMARY KEY CLUSTERED 
(
	[TelID] ASC,
	[Ano] ASC,
	[Mes] ASC,
	[Fecha] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'(0)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TelefonoFactu', @level2type=N'COLUMN',@level2name=N'MItbis'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'(0)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TelefonoFactu', @level2type=N'COLUMN',@level2name=N'MFactura'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'(0)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TelefonoFactu', @level2type=N'COLUMN',@level2name=N'MPagar'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'(0)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TelefonoFactu', @level2type=N'COLUMN',@level2name=N'MDbCr'
GO

ALTER TABLE [dbo].[TelefonoFactu] ADD  CONSTRAINT [DF_TelefonoFactu_FechaCrea]  DEFAULT (getdate()) FOR [FechaCrea]
GO

ALTER TABLE [dbo].[TelefonoFactu] ADD  DEFAULT ('M') FOR [Modificado]
GO

推荐答案

我建​​议你：

1）减少子查询数量，

2）优化查询。



以上都是为了提高性能;）


例如下面的查询应该从实际月份开始返回 SUM（MPagar） 3个月：

I would suggest you to:
1) reduce number of subqueries,
2) optimize query.

All above is to improve performance ;)

For example below query should return SUM(MPagar) for 3 months starting from actual month:

SELECT MES, SUM(MPagar) AS SumOfPagar
FROM TelefonoFactu 
GROUP BY MES
WHERE ANO = YEAR(GETDATE()) and MES BETWEEN MONTH(GETDATE())-3 AND MONTH(GETDATE())

示例结果：

Example result:

MES  SumOfPagar
5    100
6    150
7    125

正如我所看到的那样，你遇到了查询，它应该返回MPagar的总和以及所需的记录和数月。在你的情况下，我建议你使用 PIVOT [ ^ 查询。

As i see, you were stuck with query which should return sums of MPagar for desired set of records and months. In your case, i would suggest you to use PIVOT[^] query.

DECLARE @cols VARCHAR(30)
DECLARE @counter INT

SET @counter = MONTH(GETDATE())-3

WHILE @counter < MONTH(GETDATE()) BEGIN
    SET @cols = @cols + '[' + CONVERT(VARCHAR(2), @counter) + '],'
    SET @counter = @counter + 1
END

SET @cols = LEFT(@cols, LEN(@cols)-1)
--[5],[6],[7]

SELECT Telefono, Tipo, Categoria, Empleado, Renta, @cols 
FROM (
    SELECT rt.TelID as 'Telefono',  td.Descr as 'Tipo', cd.Descr As 'Categoria', e.codigo+' - '+e.Nombre as 'Empleado', t.RentBas as 'Renta', MPagar as 'MActual'
    FROM TelefonoFactu rt INNER JOIN Telefonos t on (t.TelID = rt.telID and t.[status] = 1)
        INNER JOIN TipoDevice td on (td.TipoID = t.TipoID) INNER JOIN CategoriaDevice cd on (cd.CategID = t.CategID) 
        LEFT OUTER JOIN empleados e on (e.codigo = t.CodAsig)
    WHERE ANO = YEAR(GETDATE()) and Mes = BETWEEN MONTH(GETDATE())-3 AND MONTH(GETDATE()) AND (t.TipoID like  'TD001') AND (t.CategID LIKE 'CC001')
    ) AS DT
PIVOT(SUM(MActual) FOR Telefono IN(@cols))

这篇关于子查询不会返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！