需要插入的表:
INSERT INTO sysdba.account
('ordertotal05')
Values()数据来源:
select sum(sa.ordertotal) as o from account a
inner join salesorder sa on a.accountid=sa.accountid
where orderdate>='2005-1-1' and orderdate<='2005-12-31'
group by a.accountID
having sum(sa.ordertotal)<=15024需要插入数据的表和数据来源表都是account表,关键字是accountID
请问如何写sql???多谢帮忙!!
INSERT INTO sysdba.account
('ordertotal05')
Values()数据来源:
select sum(sa.ordertotal) as o from account a
inner join salesorder sa on a.accountid=sa.accountid
where orderdate>='2005-1-1' and orderdate<='2005-12-31'
group by a.accountID
having sum(sa.ordertotal)<=15024需要插入数据的表和数据来源表都是account表,关键字是accountID
请问如何写sql???多谢帮忙!!
select sum(sa.ordertotal) as o from account a
inner join salesorder sa on a.accountid=sa.accountid
where orderdate>='2005-1-1' and orderdate<='2005-12-31'
group by a.accountID
having sum(sa.ordertotal)<=15024
INSERT INTO sysdba.account(accountid, a.ordertotal)
select sum(sa.ordertotal) as o,max(accountid)+1 from account a
inner join salesorder sa on a.accountid=sa.accountid
where orderdate>='2005-1-1' and orderdate<='2005-12-31'
group by a.accountID
having sum(sa.ordertotal)<=15024
能不能举些例子.感觉好像不对.
INSERT INTO sysdba.account(accountID,ordertotal05)
select a.accountID,sum(sa.ordertotal) as o from account a
inner join salesorder sa on a.accountid=sa.accountid
where orderdate>='2005-1-1' and orderdate<='2005-12-31'
group by a.accountID
having sum(sa.ordertotal)<=15024
我要将05年订单额小于15024的所有客户的05年订单总额记录到account客户表的ordertotal05字段中。
set ordertotal05 = t.o
from account,(select a.accountID,sum(sa.ordertotal) as o from account a
inner join salesorder sa on a.accountid=sa.accountid
where orderdate>='2005-1-1' and orderdate<='2005-12-31'
group by a.accountID
having sum(sa.ordertotal)<=15024)t
where account.accountID=t.accountID
from sysdba.account as a
inner Join (Select accountid,sum(ordertotal) as Ototal
from salesorder group by accountid
having sum(ordertotal)<=15024 ) as b
on a.accountid=b.accountid
where a.orderdate between '2005-1-1' and '2005-12-31 23:59:59.899'
select a.accountID,sum(sa.ordertotal) as o from account a
inner join salesorder sa on a.accountid=sa.accountid
where orderdate>='2005-1-1' and orderdate<='2005-12-31'
group by a.accountID
having sum(sa.ordertotal)<=150242.更新表(此种情况为:当sysdba.account与acount同一表时)
update account
set ordertotal05 = t.o
from account,(select a.accountID,sum(sa.ordertotal) as o from account a
inner join salesorder sa on a.accountid=sa.accountid
where orderdate>='2005-1-1' and orderdate<='2005-12-31'
group by a.accountID
having sum(sa.ordertotal)<=15024)t
where account.accountID=t.accountID
select sum(sa.ordertotal) as o from account a
inner join salesorder sa on a.accountid=sa.accountid
where orderdate>='2005-1-1' and orderdate<='2005-12-31'
group by a.accountID
having sum(sa.ordertotal)<=15024