语句最后的执行结果如下: (Total), -1, 469771.3400 A , 10952, 491.2000 A , 10835, 851.0000 A , 11011, 960.0000 A , -1, 2302.2000 B , 10926, 514.4000 B , -1, 514.4000 C , 10856, 660.0000 C , -1, 660.0000 D , 10864, 282.0000 D , 10920, 390.0000 D , 11016, 491.5000 D , 10953, 4675.0000 D , -1, 5838.5000 ……
--oracle中: ...... theyear number(8):=1998; begin select decode(o.customerid,0,o.customerid,'total') MyCustomerID, decode(od.orderid,0,od.orderid,-1) MyOrderID, sum(od.quantity*od.unitprice) Price from Orders o, "Order Details" od where 1=1 and od.orderid=o.orderid and to_char(o.orderdate,'yyyy')=theyear group by decode(o.customerid,0,o.customerid,'total'), decode(od.orderid,0,od.orderid,-1) order by o.customerid,别名.price end ; --以上未经测试,
更正: --oracle中: ...... theyear number(8):=1998; begin select decode(o.customerid,0,o.customerid,'total') MyCustomerID, decode(od.orderid,0,od.orderid,-1) MyOrderID, sum(od.quantity*od.unitprice) Price from Orders o, "Order Details" od where 1=1 and od.orderid=o.orderid and to_char(o.orderdate,'yyyy')=to_char(theyear) group by decode(o.customerid,0,o.customerid,'total'), decode(od.orderid,0,od.orderid,-1) order by 1,3 end ;
(Total), -1, 469771.3400
A , 10952, 491.2000
A , 10835, 851.0000
A , 11011, 960.0000
A , -1, 2302.2000
B , 10926, 514.4000
B , -1, 514.4000
C , 10856, 660.0000
C , -1, 660.0000
D , 10864, 282.0000
D , 10920, 390.0000
D , 11016, 491.5000
D , 10953, 4675.0000
D , -1, 5838.5000
……
theyear number(8):=1998;
begin
select
decode(o.customerid,0,o.customerid,'total') MyCustomerID,
decode(od.orderid,0,od.orderid,-1) MyOrderID,
sum(od.quantity*od.unitprice) Price
from
Orders o, "Order Details" od
where
1=1
and od.orderid=o.orderid
and to_char(o.orderdate,'yyyy')=theyear
group by
decode(o.customerid,0,o.customerid,'total'),
decode(od.orderid,0,od.orderid,-1)
order by
o.customerid,别名.price
end ;
--以上未经测试,
--oracle中: ......
theyear number(8):=1998;
begin
select
decode(o.customerid,0,o.customerid,'total') MyCustomerID,
decode(od.orderid,0,od.orderid,-1) MyOrderID,
sum(od.quantity*od.unitprice) Price
from
Orders o, "Order Details" od
where
1=1
and od.orderid=o.orderid
and to_char(o.orderdate,'yyyy')=to_char(theyear)
group by
decode(o.customerid,0,o.customerid,'total'),
decode(od.orderid,0,od.orderid,-1)
order by
1,3
end ;
老大,怎么没有数据呢/
我把表结构给你:
create table Orders
{
orderid number,
customerid varchar2(11);
employeeid number,
orderdate date,
requiredda date,
shippeddat date,
shipvia number,
freight number,
shipname varchar2(38);
shipaddres varchar2(51);
shipcity varchar2(17);
shipregion varchar2(15);
shippostal number;
shipcountr varchar2(13)
}
create table Order Details
(
orderid number,
productid number,
unitprice number,
quantity number,
discount date
)
帮忙再看看
老大,怎么没有数据呢/
我把表结构给你:
create table Orders
{
orderid number,
customerid varchar2(11);
employeeid number,
orderdate date,
requiredda date,
shippeddat date,
shipvia number,
freight number,
shipname varchar2(38);
shipaddres varchar2(51);
shipcity varchar2(17);
shipregion varchar2(15);
shippostal number;
shipcountr varchar2(13)
}
create table Order Details
(
orderid number,
productid number,
unitprice number,
quantity number,
discount date
)
帮忙再看看
*
from
Orders o, "Order Details" od
where
1=1
and od.orderid=o.orderid
and to_char(o.orderdate,'yyyy')='1998'--你这样测试一下,如果没有数据,那两表关联可能就确实没有数据.
不加的话数据都有/我是想让1998年的数据都显示出来