帮助Sql Server查询 [英] Help with Sql Server Query

问题描述

嘿伙计我只是想知道是否有人可以帮我处理以下sql查询

Hey guys im just wondering if someone can help me with the following sql query

SELECT tt.TypeName, c.Name, db.BandName, p.Product, s.Service,
ra.Date, ra.Hour, ra.Calls, ra.Cost
FROM dbo.MainTable ra WITH(NOLOCK)
JOIN dbo.ChileTable1 tt WITH(NOLOCK) ON tt.TrafficTypeId = ra.TrafficTypeId
JOIN dbo.ChileTable2 c WITH(NOLOCK) ON c.CarrierId = ra.CarrierId
JOIN dbo.ChileTable3 db WITH(NOLOCK) ON db.DestinationBandId = ra.DestinationBandId
JOIN dbo.ChileTable4 p WITH(NOLOCK) ON p.ProductId = ra.ProductId
JOIN dbo.ChileTable5 s WITH(NOLOCK) ON s.ProductId = p.ProductId
Where (Date Between '01-Mar-2014' and '31-Mar-2014')

因此，基本上名称字段总是只有两个值。我需要在查询中添加一个额外的列，以显示名称1的总和（成本） - 名称2的总和（成本），并且还将所有其他字段组合为

by tt.TypeName，c。姓名，db.BandName，p.Product，s.Service，

ra.Date，ra.Hour



任何帮助都非常多赞赏...



更新



主表这些值已经由BandName，Product汇总，服务，日期和时间。



所以说名字将永远是汤姆和本，所以对于这些名称我希望有TypeName，BandName，Product ，服务，日期，小时，汤姆的电话，汤姆的费用，本的电话，本的费用都在一排。



希望清除

So basically the Name filed will always only two values. I need to add an extra column to the query to show the Sum(Cost) for Name 1 - Sum(Cost) for Name 2 and also group all the other fields
by tt.TypeName, c.Name, db.BandName, p.Product, s.Service,
ra.Date, ra.Hour

Any help is very much appreciated...

UPDATE

The Main table these values is already aggregated up by BandName, Product, Service, Date and hour.

So say the Name will always be Tom and Ben, so for each of these names I would like to have TypeName, BandName, Product, Service, Date, Hour, Tom's Calls, Tom's Cost, Ben's Calls, Ben's Cost all on one row.

Hope thats clear

推荐答案

奇怪的是，您的查询现在似乎正是您想要的。这就是为什么我提到你应该包括你现在得到的结果样本并解释这有什么问题。



无论如何，你试过这个吗？

Strange enough your query now seems to be exactly what you want. That's why I mentioned you should include a sample of results you are getting now and explain what's wrong with that.

Anyway, did you try this?

SELECT tt.TypeName, c.Name, db.BandName, p.Product, s.Service, ra.Date, ra.Hour, sum(ra.Calls) as totalcalls, sum(ra.Cost) as totalcost
FROM dbo.MainTable ra WITH(NOLOCK)
JOIN dbo.ChileTable1 tt WITH(NOLOCK) ON tt.TrafficTypeId = ra.TrafficTypeId
JOIN dbo.ChileTable2 c WITH(NOLOCK) ON c.CarrierId = ra.CarrierId
JOIN dbo.ChileTable3 db WITH(NOLOCK) ON db.DestinationBandId = ra.DestinationBandId
JOIN dbo.ChileTable4 p WITH(NOLOCK) ON p.ProductId = ra.ProductId
JOIN dbo.ChileTable5 s WITH(NOLOCK) ON s.ProductId = p.ProductId
Where (Date Between '01-Mar-2014' and '31-Mar-2014')
group by tt.TypeName, c.Name, db.BandName, p.Product, s.Service, ra.Date, ra.Hour

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