27,580
社区成员
发帖
与我相关
我的任务
分享
create table spxx(id varchar(10), spmc varchar(10))
insert into spxx values('1000001', '统一冰红茶')
insert into spxx values('1000002', '康师傅绿茶')
create table xssp(id int, spid varchar(10), sl int, dj decimal(18,1) , zje decimal(18,1))
insert into xssp values(1, '1000001', 1, 2.5, 2.5 )
create table jhsp(id int, spid varchar(10), sl int, dj int, zje int)
insert into jhsp values(1, '1000001', 1, 2, 2)
insert into jhsp values(2, '1000001', 10, 2, 2)
insert into jhsp values(3, '1000001', 1, 2, 2)
go
select c.spmc,b.sl,a.sl
from (select *,px = (select count(1) from jhsp where id <= b.id) from jhsp b) a
left join (select *,px = (select count(1) from xssp where id <= b.id) from xssp b) b on b.id = a.px
left join spxx c on a.spid = c.id
drop table spxx,xssp,jhsp
/*
spmc sl sl
---------- ----------- -----------
统一冰红茶 1 1
统一冰红茶 NULL 10
统一冰红茶 NULL 1
*/
create table spxx(id varchar(10), spmc varchar(10))
insert into spxx values('1000001', '统一冰红茶')
insert into spxx values('1000002', '康师傅绿茶')
create table xssp(id int, spid varchar(10), sl int, dj decimal(18,1) , zje decimal(18,1))
insert into xssp values(1, '1000001', 1, 2.5, 2.5 )
create table jhsp(id int, spid varchar(10), sl int, dj int, zje int)
insert into jhsp values(1, '1000001', 1, 2, 2)
insert into jhsp values(2, '1000001', 1, 2, 2)
insert into jhsp values(3, '1000001', 1, 2, 2)
go
select isnull(c.spmc,d.spmc) spmc , c.sl xssl, d.sl jhsl from
(
select a.* , b.sl , b.px from spxx a
left join
(select t.* , px = (select count(1) from xssp where spid = t.spid and id < t.id) + 1 from xssp t) b
on a.id = b.spid
) c
full join
(
select m.spmc,n.sl , n.px from spxx m,
(select t.* , px = (select count(1) from jhsp where spid = t.spid and id < t.id) + 1 from jhsp t) n
where m.id = n.spid
) d
on c.spmc = d.spmc and c.px = d.px
/*
spmc xssl jhsl
---------- ----------- -----------
统一冰红茶 1 1
统一冰红茶 NULL 1
统一冰红茶 NULL 1
康师傅绿茶 NULL NULL
(所影响的行数为 4 行)
*/
drop table spxx , xssp , jhsp
SELECT
spxx.spmc AS 商品名称,
(select sum(xssp.sl) from xssp where xssp.spid = spxx.id ) AS 销售数量,
jhsp.sl AS 采购数量
FROM spxx
JOIN jhsp
ON spxx.id = jhsp.spid
SELECT spxx.spmc AS 商品名称, sum(xssp.sl) AS 销售数量, sum(jhsp.sl) AS 采购数量
FROM spxx LEFT OUTER JOIN
jhsp ON spxx.id = jhsp.spid LEFT OUTER JOIN
xssp ON spxx.id = xssp.spid
group by spxx.spmc
create table spxx(spid int, spmc varchar(20))
insert spxx select 1000001,'统一冰红茶'
union all select
1000002,'康师傅绿茶 '
create table xssp(id int , spid int ,sl int)
insert xssp select 1, 1000001, 1
create table jhsp(id int , spid int, sl int)
insert jhsp select 1, 1000001, 1
union all select 2, 1000001 , 10
union all select 3 , 1000001 , 15
---------------------------------------
select a.spmc,xs,jh from
spxx a
left outer join
(select isnull(b.spid,c.spid) spid,b.sl xs,c.sl jh from
(select spid,(select count(*) from xssp where xs.spid = spid and xs.id >=id ) id,sl from xssp xs) b
full outer join
(select spid,(select count(*) from jhsp where jh.spid = spid and jh.id >=id ) id,sl from jhsp jh) c on b.id = c.id
) xsjh on a.spid = xsjh.spid
/*
spmc xs jh
-------------------- ----------- -----------
统一冰红茶 1 1
统一冰红茶 NULL 10
统一冰红茶 NULL 15
康师傅绿茶 NULL NULL
*/
drop table spxx,xssp,jhsp
表spxx 商品信息
id spmc
1000001 统一冰红茶
1000002 康师傅绿茶
表xssp 销售商品
id spid sl dj zje
1 1000001 1 2.5 2.5
表jhsp 采购商品
id spid sl dj zje
1 1000001 1 2 2
2 1000001 1 2 2
3 1000001 1 2 2
---------------------------------------
select a.spmc,b.sl,c.sl from
spxx a
left join (select spid,(select count(*) from xssp where xs.spid = spid and xs.id >=id ) id from xssp xs) b on a.spid = b.spid
left join (select spid,(select count(*) from jhsp where jh.spid = spid and jh.id >=id ) id from jhsp jh) c on a.spid = c.spid and b.id = c.id