如何从SQL Server中查询数据？

Question

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

___________________________________________________________________________
|    |               |          |             |           |               |
| 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”），“Quantity”（相同项目的总和）和“Amount”（相同项目的总和）。但我尝试了几次，结果都是错误的。如果它们的“Unit Price”不是完全相同的价格，如何计算所有相同项目的“Amount”。以下是我的期望结果：

___________________________________________________________________________
|    |               |          |             |           |               |
| 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    |
|____|_______________|__________|_____________|___________|_______________|

请帮助我……

- Eric

---

你能展示一下你正在使用但是不起作用的代码吗？看起来Amount只是Unit Price * Quantity。这可以在数据库视图、SQL语句、业务对象、UI视图等中完成。 - David

你能否也展示一下您的期望输出？ - bonCodigo

@kimleng 如果您有两个来自具有相同“unit_price”的项目的条目，该怎么办？我相信您希望在这种情况下显示正确的“unit_price”;) - bonCodigo

Answer 1

如果我理解你的意思正确，那么这应该符合您的要求：

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 Fiddle，让你可以尝试一下。（感谢@bonCodigo，因为我是基于他已经有的Fiddle，但加入了我的代码）。

这是结果：

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

- Lamak

---

这个支持当一个项目有两个相同的unit_price条目时吗？因为我只能提供答案，如果有多个相同或不同的unit_price 条目，就会显示“未定义” :) - bonCodigo

@bonCodigo 是的，它确实考虑到了这一点。这就是为什么在一个使用COUNT(DISTINCT的派生表上有一个LEFT JOIN，以及在SELECT中使用CASE的原因。 - Lamak

顺便说一句，你可能需要在“group by”子句中包含UNIT ;) 我正要将SQLFIDDLE添加到你的答案中，并编辑“group by” :D - bonCodigo

@bonCodigo，我基于你的SQLFiddle添加了一个新的，但是使用了我的代码，如果你也想尝试一下结果的话。而且我已经在我的答案中添加了“Unit”列，谢谢。 - Lamak

我想让你的回答更加“美丽”，所以我冒昧地更新了它，并附上了结果，哈哈:D，干杯。 - bonCodigo

@bonCodigo 谢谢 :-) （虽然我用自己的风格编辑了“完美结果”） - Lamak

Answer 2

请尝试这段代码：

select Id, item, unit, sum(quantity) totoal_Qt ,
sum(amount) total_Px  from td
group by id, item, unit
;

结果：

ID  ITEM        UNIT    TOTOAL_QT   TOTAL_PX
1   Gasoline    L       90          539.9
2   Water       Bottle  10          50
3   Meat        Kg      15          223.5
4   Milk        Can     30          223.5

SQLFIDDLE

---

使用CASE进行编辑

这是我能得到的最接近答案。这是一个很好的问题。因此，这个已编辑的答案的缺陷是它会为一个具有多个unit_prices的项目显示“未定义”，但是它不会显示单个条目的unit_price，即使该条目的多个条目具有相同的unit_price。答案是完全符合ANSI语法的。

*SQLFIDDLE

更改了示例数据以测试各种情况。

ID  ITEM    UNIT    UNIT_PRICE  QUANTITY    AMOUNT
1   Gasoline    L   2   10  20
1   Gasoline    L   2.5     20  50
2   Water   Bottle  5   10  50
3   Meat    Kg  14.9    15  223.5
1   Gasoline    L   8   50  400
4   Milk    Can     7.45    30  223.5
1   Gasoline    L   6.99    10  69.9
2   Water   Bottle  5   10  49.9

查询：

select distinct x.id, x.item, x.unit,
x.total_Qt, x.total_Amt,
case when x.unitPrice = 0
then 'Undefined'
else cast(y.unit_price as varchar(9))
end as UP
from(
select Id, item, unit, sum(quantity) total_Qt ,
sum(amount) total_Amt, 
case when count(unit_price)>1
then 0
else 1 
end unitPrice
from td
group by id, item, unit) as x
left join 
(select distinct id, item, unit, unit_price
 from td) as y
on x.id = y.id
and x.item = y.item
and x.unit = y.unit
;

结果：

ID  ITEM        UNIT    TOTAL_QT    TOTAL_AMT   UP
1   Gasoline    L       90          539.9       Undefined
2   Water       Bottle  20          99.9        Undefined
3   Meat        Kg      15          223.5       14.90
4   Milk        Can     30          223.5       7.45

- bonCodigo

---

@kimleng 这是一个好问题。我已经更新了答案，使其更接近你的需求。但它还有一个缺陷。请阅读更新并发表评论。 :) - bonCodigo