代码之家 › 专栏 › 技术社区 › Raj

t-sql查询未显示预期结果

tsql sql-server-2005

Raj · 技术社区 · 14 年前

Sales Table Data http://lh5.ggpht.com/_KT7tmVVBHFM/TCryAax1JlI/AAAAAAAAAFk/zahMq4RoOuw/s144/Sales.png

我希望它能根据日期显示分组销售情况。Sales表包含不同组的数据,但我的查询只显示两行。

SQL查询:

select 
    i.gName, 
    sum(Quantity) as '180ml', 
    isnull((select sum(Quantity)
        from saleslog 
        where BillDate='12-10-2010' 
        and pSize=375 and pGroup=i.gCode),0) as '375ml', 
    isnull((select sum(Quantity)
        from saleslog 
        where BillDate='12-10-2010' 
        and pSize=500 and pGroup=i.gCode),0) as '500ml', 
    isnull((select sum(Quantity)
        from saleslog 
        where BillDate='12-10-2010' 
        and pSize=750 and pGroup=i.gCode),0) as '750ml', 
    isnull((select sum(Quantity)
        from saleslog 
        where BillDate='12-10-2010' 
        and pSize=1000 and pGroup=i.gCode),0) as '1000ml', 
    isnull((select sum(Quantity)
        from saleslog 
        where BillDate='12-10-2010' 
        and pSize=2000 and pGroup=i.gCode),0) as '2000ml' 
from saleslog as s
    inner join ItemGroup as i on s.pGroup=i.gCode 
where BillDate='12-10-2010' 
    and i.gCode=pGroup 
    and pSize=180 
group by i.gCode,i.gName

上述查询的输出

WHISKY 5 2 0 0 0 0
RUM     82 0 0 45 0 0

产品组表:

1 BRANDY         1
2 WHISKY         2
3 RUM         3
4 GIN         4
5 VODKA         5
6 BEER         8
7 WINE         6
8 LIQUOR         7
9 SCOTCH WHY 9
10 LUBRICANT 15
11 UNTAXABLE 16
12 O/S LIQUOR 10
13 RTD         11
14 275 ML         12

我的问题怎么了?

5 回复 | 直到 14 年前

Blorgbeard 14 年前

select i.gName,
isnull((select sum(Quantity)from saleslog where BillDate='12-10-2010' and pSize=180 and pGroup=i.gCode),0) as '180ml', 
isnull((select sum(Quantity)from saleslog where BillDate='12-10-2010' and pSize=375 and pGroup=i.gCode),0) as '375ml', 
isnull((select sum(Quantity)from saleslog where BillDate='12-10-2010' and pSize=500 and pGroup=i.gCode),0) as '500ml',
isnull((select sum(Quantity)from saleslog where BillDate='12-10-2010' and pSize=750 and pGroup=i.gCode),0) as '750ml',
isnull((select sum(Quantity)from saleslog where BillDate='12-10-2010' and pSize=1000 and pGroup=i.gCode),0) as '1000ml',
isnull((select sum(Quantity)from saleslog where BillDate='12-10-2010' and pSize=2000 and pGroup=i.gCode),0) as '2000ml' 
from saleslog as s 
inner join ItemGroup as i on s.pGroup=i.gCode 
where BillDate='12-10-2010' 
group by i.gCode, i.gName

marc_s HarisH Sharma 14 年前

BillDate ??

如果是的话 DATETIME

BillDate = '12-10-2010'

只选择2010年10月12日午夜(0:00:00)购买的商品。

还包含时间部分-因此,如果您希望在2010年10月12日购买所有商品,则需要使用:

WHERE BillDate BETWEEN '12-10-2010 00:00:00' AND  '12-10-2010 23:59:59'

或者:

WHERE DAY(BillDate) = 12 AND MONTH(BillDate) = 10 AND YEAR(BillDate) = 2010

AdaTheDev 14 年前

尝试将FROM子句改为:

from ItemGroup as i
    LEFT OUTER JOIN saleslog as s ON i.gCode = s.pGroup AND s.BillDate = '12-10-2010' AND s.pSize=180
group by i.gCode,i.gName

Matt Mitchell 14 年前

例如:

sum(Quantity) as '180ml'

where pSize=180

我想说,这是因为其他组没有180毫升的大小出售。

devio 14 年前

您只查询至少有1 180ml销售额的退货产品组。将内部联接更改为子选择,就像处理其他产品尺寸一样,您将获得结果集中的所有产品组。