如何从SQL Server查询数据? [英] How to Query Data From SQL Server?

问题描述

我在从数据库查询数据以在VB.NET中进行报告时遇到问题.我使用业务对象来做报告.这是我的示例数据:

I have a problem in query data from database to make report in VB.NET. I use the Business Object to do the report. And here is my example data:

___________________________________________________________________________
|    |               |          |             |           |               |
| Id |   Item        |   Unit   |  Unit Price |  Quantity |     Amount    |
|____|_______________|__________|_____________|___________|_______________|
|  1 |   Gasoline    |     L    |    $ 2.00   |     10    |   $ 20.00     |
|  1 |   Gasoline    |     L    |    $ 2.50   |     20    |   $ 50.00     |
|  2 |   Water       |  Bottle  |    $ 5.00   |     10    |   $ 50.00     |
|  3 |   Meat        |     Kg   |    $ 14.90  |     15    |   $ 223.50    |
|  1 |   Gasoline    |     L    |    $ 8.00   |     50    |   $ 400.00    |
|  4 |   Milk        |    Can   |    $ 7.45   |     30    |   $ 223.50    |
|  1 |   Gasoline    |     L    |    $ 6.99   |     10    |   $ 69.90     |
|____|_______________|__________|_____________|___________|_______________|

在报告中，我想查看"Id"，"Item"，"Unit"，"Unit Price"(是的，如果它们具有不同的值，我将显示"Undefined")，""(同一项目的总和)和金额"(同一项目的总和).但是我尝试了几次，结果是错误的.如果它们的单价"根本不是相同的价格，那么如何计算所有相同"的金额".这是我的预期结果:

In report, I want to see the "Id" , "Item" , "Unit" , "Unit Price" (And yes, this one I will show "Undefined" instead if they have the different value), "Quantity" (Sum of the same item) and "Amount" (Sum of the same item). But I have tried a few times, the result is wrong. How to calculate the "Amount" all the same item, if their "Unit Price" are not the same price at all. Here is my expected result:

___________________________________________________________________________
|    |               |          |             |           |               |
| Id |   Item        |   Unit   |  Unit Price |  Quantity |     Amount    |
|____|_______________|__________|_____________|___________|_______________|
|  1 |   Gasoline    |     L    |  Undefined  |     90    |   $ 539.90    |
|  2 |   Water       |  Bottle  |    $ 5.00   |     10    |   $ 50.00     |
|  3 |   Meat        |     Kg   |    $ 14.90  |     15    |   $ 223.50    |
|  4 |   Milk        |    Can   |    $ 7.45   |     30    |   $ 223.50    |
|____|_______________|__________|_____________|___________|_______________|

请帮助我....

推荐答案

如果我对您的理解正确，这应该可以满足您的要求:

If I understood you correctly, this should do what you want:

SELECT  A.Id, 
        A.Item, 
        A.Unit, 
        CASE WHEN B.Id IS NOT NULL THEN 'Undefined' ELSE [Unit Price] END [Unit Price],
        A.Quantity,
        A.Amount
FROM (  SELECT  Id, Item, Unit,
                CAST(MIN([Unit Price]) AS VARCHAR(20)) [Unit Price], 
                SUM(Quantity) Quantity, SUM(Amount) Amount
        FROM YourTable
        GROUP BY Id, Item, Unit) A
LEFT JOIN ( SELECT Id
            FROM YourTable
            GROUP BY Id
            HAVING COUNT(DISTINCT [Unit Price]) > 1) B
    ON A.Id = B.Id

添加了 sql小提琴供您尝试. (由于我的小提琴是基于他已经拥有的小提琴，但是带有我的代码，因此请@bonCodigo提供信用.)

Added an sql fiddle for you to try. (Credit to @bonCodigo, since I based my fiddle on the one he already had, but with my code).

这是结果:

ID  ITEM        UNIT        PRICE       QUANTITY    AMOUNT
1   Gasoline    L           Undefined   90          539.9
2   Water       Bottle      5.00        20          99.9
3   Meat        Kg          14.90       15          223.5
4   Milk        Can         7.45        30          223.5

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