棘手的SQL查询问题 [英] tricky SQL query problem

问题描述

嗨！

我有一个棘手的SQL问题（使用MS SQL Server 2008）：

这是显示软件订单和配置的表格：

Hi!
I have a tricky SQL problem (with MS SQL Server 2008):
Thats the table that shows software orders and provisionings:

BookingID |ServiceID|OrderType|PaymentAmount|InstallationBooking
BOO0025958|SRV00979 |10       | 55.00       |NULL
BOO0025959|SRV00749 |10       |  0.00       |NULL
BOO0025960|SRV00971 |10       | 25.00       |NULL
BOO0025961|SRV00812 |10       | 10.00       |NULL
BOO0025962|SRV00820 |10       | 60.00       |NULL
BOO0025963|SRV00812 |20       |  0.00       |BOO0025960
BOO0025965|SRV00788 |10       |165.00       |NULL
BOO0025966|SRV00789 |10       | 45.00       |NULL
...

OrderType 10是一个安装，OrderType 20是一个卸载（总是免费）和InstallationBooking是一个引用原始安装预订的卸载。这意味着在我的示例中，BOO0025960现在已卸载，不应包含在任何计算中。



那么查询是否考虑了ServiceID挂起的ServiceAmount总和卸载的软件???



我没有权利写入表格！



谢谢

彼得

OrderType 10 is an installation, OrderType 20 is an deinstallation (always cost free) and InstallationBooking is a deinstallation that refers to the original installation booking. That means in my example BOO0025960 is now deinstalled and should not be included in any calculation.

So whats the query to get a sum of PaymentAmount grouped by ServiceID taking into account of deinstalled software ???

And I don't have rights to write into the table!

Thanks
Peter

推荐答案

我没有看到问题中的任何问题。

还有一件事，你说按ServiceID分组的PaymentAmount，它无法帮助你获得正确答案，因为你的所有ServiceID似乎都是唯一的。

例如，预订ID BOO0025960 服务ID SRV00971 而卸载的InstallationBooking（卸载，根据您）具有服务ID SRV00812 ，这是不同的。



我相信你应该保留一个额外的专栏来跟踪记录ID /没有左右。



您可以获得总付款额所有订单都没有。但不是单个订单的总数。



如果我遗漏了什么，请告诉我。如果您对订单有任何参考，我很乐意解决这个问题，这样您就可以唯一地确定哪个订单已经预订。

I don't see any TRICKS in the problem.
One more thing, you said "PaymentAmount grouped by ServiceID", it would not help you getting correct answer as all of your ServiceID seems to be unique.
For Example, Booking ID BOO0025960 has Service ID SRV00971 whereas the InstallationBooking of Uninstalled (deinstallation, as per you) has Service ID SRV00812, which is different.

I believe you should have kept an additional column to keep track of record id/no or so.

You can get the total payment amount for all the orders made but not the total of individual order.

In case I am missing anything, please let me know. I would love to solve the issue if you have any reference of orders made, such a way that you can identify uniquely which booking has been made for which order.

> ...

>还有一件事，你说按ServiceID分组的PaymentAmount，它将是

>无法帮助您获得正确的答案，因为您的所有ServiceID似乎都是

>独一无二。



你是对的，但这只适用于小例子，原始表有超过30,000条记录。



>例如，预订ID BOO0025960的服务ID为SRV00971，而

> InstallationBooking of Uninstalled（卸载，根据你）有

>服务ID SRV00812，这是不同的。



哦是的，抱歉，我在我的例子中不小心忽略了这一点。



>我相信你应该保留一个额外的专栏来跟踪

>记录id / no左右。



每条记录都有一个唯一的ID。

> ...
> One more thing, you said "PaymentAmount grouped by ServiceID", it would
> not help you getting correct answer as all of your ServiceID seems to be
> unique.

You're right but that's only true for the small example, the original table has more then 30,000 records.

> For Example, Booking ID BOO0025960 has Service ID SRV00971 whereas the
> InstallationBooking of Uninstalled (deinstallation, as per you) has
> Service ID SRV00812, which is different.

Oh yes, sorry, I accidentally ignored that in my example.

> I believe you should have kept an additional column to keep track of
> record id/no or so.

There's a unique ID for every record.

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