TRY:
select customer.custid,
customer.shipcity,
(select sum(totalamt) from sale where sale.custid=customer.custid) as sumtotalamt
-- custsaletotal.sumtotalamt,
(select avg(discount) from customer b where b.shipcity=customer.shipcity)
-- citydisc.avgdisc,
custsaletotal.sumtotalamt*citydisc.avgdisc as avgdiscvalue
from customer
select customer.custid,
customer.shipcity,
(select sum(totalamt) from sale where sale.custid=customer.custid) as sumtotalamt
-- custsaletotal.sumtotalamt,
(select avg(discount) from customer b where b.shipcity=customer.shipcity)
-- citydisc.avgdisc,
custsaletotal.sumtotalamt*citydisc.avgdisc as avgdiscvalue
from customer
你给的代码在第7 行出现错误。
to pengdali:
你给我的代码我还没有时间测试,不知道是否正确。我一定会尽快结帖
你的代码我已经运行了和我上面的代码运行的结果差不多但是就是出现了null,能否在结果中去掉null
select customer.custid,
customer.shipcity,
(select sum(totalamt) from sale where sale.custid=customer.custid) as sumtotalamt,
(select avg(discount) from customer b where b.shipcity=customer.shipcity) as avgdisc,
custsaletotal.sumtotalamt*citydisc.avgdisc as avgdiscvalue
from customer
from (select custid,shipcity,isnull((select sum(totalamt) from sale where custid=a.custid),0) sumtotalamt,isnull((select avg(discount) from customer where shipcity=a.shipcity),0) avgdisc from customer a)tem
你给我代码始终是出错在custsaletotal.sumtotalamt*citydisc.avgdisc as avgdiscvalue
我看了一下我想应该是你没有定义他们的前缀即custsaletotal和citydisc。
to pengdali:
你给我的代码是将0取代了null值是吧?我想说的是在结果集中只出现完全匹配的值也就是不要出现null而只是表中已经存在的值。我假设上面那段代码结果集只有25行,但是你给我的代码运行后的结果是35行其中多的就是null(或者是用isnull(exp,0))
from customer a where totalamt is not null and discount is not null and custid is not null and shipcity is not null)tem
你最后给我的那段代码执行后:
执行错误:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'totalamt'.以下是我创建的表,仅供修改帖子中的代码参考
create table customer
(custid int not null,
name char(30) not null,
shipcity char(30) null,
discount dec (5,3) null,
constraint custpk primary key (custid))
go
create table sale
(orderid int not null,
custid int not null,
totalamt money not null,
saledate datetime not null,
shipdate datetime null,
constraint salepk primary key (orderid),
constraint salefk foreign key (custid)
references customer (custid))
from customer a where discount is not null and custid is not null and shipcity is not null)tem where sumtotalamt is not null and avgdisc is not null
我觉得大家的思路都差不多吗!
玩select ,join,还是他们带星的厉害啊!
小黑GG,大力GG,还有蚂蚁GG在这里,我就不敢献丑了!